CAML Query Dynamics with SPServices

Question

I'm developing a web application using SPservices for interaction with Sharepoint lists.

My question is on how to create a dynamic Caml Query using the And and Or conditions.

The condition below caters to me, works perfectly

var consultaInicio  = "<Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy><Where>";
var consultaFim     = "</Where></Query>";  
var consultaFiltros = "";  
var consultaAnd     = "";

consultaFiltros += "<Geq><FieldRef Name='ID' /><Value Type='Number'>" + min_id + "</Value></Geq>"; 

consultaAnd     += "<And>";
consultaFiltros += "<Leq><FieldRef Name='ID' /><Value Type='Number'>" + max_id + "</Value></Leq></And>"; 

consultaAnd     += "<And>";
consultaFiltros += "<Eq><FieldRef Name='NIVEL_REAL' /><Value Type='Text'>5</Value></Eq></And>";

consultaAnd     += "<And>";
consultaFiltros += "<Eq><FieldRef Name='PILAR' /><Value Type='Text'>" + pilar + "</Value></Eq></And>"; 

consultaAnd     += "<And>";
consultaFiltros += "<Eq><FieldRef Name='COMPETENCIA_x003a_ID' /><Value Type='Text'>" + arr_competencia_temp[0] + "</Value></Eq>"; 

var public_consulta = consultaInicio + consultaAnd + consultaFiltros + consultaFim;

The problem is in adding to this Caml Query a condition of Or for the FieldRef "COMPETENCE_X003a_ID".

In the search form filter, the user can select more than one "COMPETITION".

can you help me?

Is there another way of doing the consultation?

Answer 1

UPDATE!

I didn't realize CAML had a "Where In" syntax, but this might be an easier solution than nesting Or's if your option list is long. The syntax is:

<Where>
    <Or>
      <In>
        <FieldRef Name='COMPETENCIA_x003a_ID' />
        <Values>
          <Value Type='Text'>arr_competencia_temp[0]</Value>
          <Value Type='Text'>arr_competencia_temp[1]</Value>
          <Value Type='Text'>arr_competencia_temp[2]</Value>
          <Value Type='Text'>arr_competencia_temp[3]</Value>
        </Values>
      </In>
   </Or>
</Where>

My Old Answer

CAML uses xml format, which means you need to "wrap" your elements in opening and closing tags. For an "And" condition, it would look like this:

<And>...</And>

For an "Or" condition it would look like this:

<Or>...</Or>

You can nest them if needed like this:

<Or><And>...</And><And>...</And></Or>

The "..." in each example is your <Eq><FieldRef.../><Value.../></Eq> or Neq or whatever comparison you're trying to make.

To make yours work it would be something like:

"<And>[Other And Conditions Here]" +
"<Or>" +
"<Eq><FieldRef Name='COMPETENCIA_x003a_ID' /><Value Type='Text'>" + arr_competencia_temp[0] + "</Value></Eq>" +
"<Eq><FieldRef Name='COMPETENCIA_x003a_ID' /><Value Type='Text'>" + arr_competencia_temp[1] + "</Value></Eq>" +
"</Or></And>"

... 2...3...4...etc. you would need to append each option dynamically if they exist, use a for loop over the arr_competencia_temp array.

For nesting, I forgot to mention you can only nest 2 conditions. So just add another nested set to the nest if you have more than 2, like this:

<Or><1><Or><2><3></Or></Or>