CAML query logic

Question

When I extract data from a list and want to filter them, I use

"intraActive eq 1 and StartDate lt datetime'" + new Date().toISOString() + "' and OData__EndDate ge datetime'" + new Date().toISOString() + "'";

Full code:

var promise = web.getList(listUrl).items;

if (filter) {
    promise = promise.filter(filter); //Filter is the code snippet above
}

I now want to use CAML to do the same thing

 "<View><Query></Query></View>";

However, I have no clue about CAML. Any help?

Answer 1

You can try something like below (Assuming type of intraActive field in Yes/No(Boolean)):

<And>
    <Eq><FieldRef Name="intraActive" /><Value Type="Boolean">1</Value></Eq>
    <And>
        <Geq>
            <FieldRef Name="OData__EndDate" />
            <Value Type="DateTime">2015-08-15T00:00:00Z</Value>
        </Geq>
        <Lt>
            <FieldRef Name="StartDate" />
            <Value Type="DateTime">2015-08-15T00:00:00Z</Value>
        </Lt>
    </And> 
</And>

Answer 2

The easiest way to generate CAML is to use a list view. Create a list view with EXACTLY how you want items to be returned. Then go to it with REST

<site collection url>/_api/web/lists/getByTitle('<list name>')/views/getByTitle('<view name>')

Then look for the value of d:ListViewXml This will be the exact CAML query for the view you've customized.

Answer 3

probably something along the lines of (warning: untested!)

<Query>
  <Where>
    <And>
      <Eq>
        <FieldRef Name="intraActive" />
        <Value Type="Text">1</Value>
      </Eq>
      <And>
        <Lt>
          <FieldRef Name="StartDate" IncludeTimeValue="TRUE" />
          <Value Type="DateTime" IncludeTimeValue="TRUE">2012-03-14T00:00:00Z</Value>
        </Lt>
        <Ge>
          <FieldRef Name="EndDate" IncludeTimeValue="TRUE" />
          <Value Type="DateTime" IncludeTimeValue="TRUE">2012-03-14T00:00:00Z</Value>
        </Ge>
      </And>
    </And>
  </Where>
</Query>

Be sure to checkout U2U CAML Query Builder and if you're bulding the CAML using c# I recommend using camlex