Where with multiple OR clause

Reply

ayoussef Posted: 01-07-2008 1:57 PM

rated by 0 users

Hi All,

How would I go about writing the following

select * from sometable

where field1==value1 AND ( (field2==value21 AND  field3==value31) OR (field2==value22 AND  field3==value32) OR (.....)  )

I want to do the OR part in  a loop

Query.Where(Query.field1==value1)

for(int i=0; i<n;i==)

{

     Query.Where(Query.Or(Query.field2==i,Query.field3==i+1)   // Something like that

} 

I am using the latest version of ES 

Thanks for your help

Reply

David.Parsons replied on 01-07-2008 10:13 PM

rated by 0 users

If there were only one field that needed to be ORed repeatedly, I would recommend building a list in the loop, then passing the list to a Query.field.In(list) clause. But, to get that exact SQL, with the proper nesting of ANDs and ORs, I believe you are going to have to construct it in a custom load. Your pseudo-code would produce something like the following, which has the ANDs and ORs reversed in the loop.

where field1=value1
  AND (field2=value21 OR  field3=value31)
  AND (field2=value22 OR  field3=value32)
  AND (.....)

This is a pure DynamicQuery that would produce the results you are looking for:

EmployeesCollection collection = new EmployeesCollection();

collection.Query.Select(
    collection.Query.EmployeeID,
    collection.Query.ReportsTo,
    collection.Query.Country);

collection.Query.es.DefaultConjunction =
    esConjunction.Or;

for (int i = 0; i < 10; i++)
{
    collection.Query.Where(
        collection.Query.Country == "USA" &
        collection.Query.ReportsTo == i &
        collection.Query.EmployeeID == i + 1);
}

collection.Query.Load();

But, the SQL generated would be less efficient since the field1 ("Country") clause would need to be in each loop.

where (field1=value1 AND field2=value21 AND  field3=value31)
  OR (field1=value1 AND field2=value22 AND  field3=value32)
  OR (.....)

With EntitySpaces DynamicQuery, you have excellent control of parenthesis and mixing ANDs and ORs within a Where statement. But, you cannot stick a "for" loop within a Where statement. And, there is no way to pull the field1 clause out into a separate Where statement, and then wrap all the "OR" Where statements within a single pair of parenthesis.

Reply

ayoussef replied on 01-08-2008 8:08 AM

rated by 0 users

Thanks for the reply,

that works but as you mentioned that might be less efficient, at leat I have a way of doing this

For my situation it was more efficient to get the set using filed2 in(..,..,..) and then throw away the field3 in my loop, as field3 condition did not return large set

Thanks again