Building an OR statement with a Loop

Reply

no1ross Posted: 01-14-2008 1:47 PM

rated by 0 users

Hi,

What i'm trying to do is build a dynamic OR statement with items from a listbox, but i think i may be doing something wrong, here is what i have

                Dim vMemb As New VAHNAMemberDirectoryCollection
                vMemb.es.Connection.Name = GetNOAHSetting("DBName")
                vMemb.Query.Where(vMemb.Query.Date2 >= Date.Now())

                If Me.tb_FName.Text.Trim <> "" Then
                    vMemb.Query.Where(vMemb.Query.FirstName.Like(Me.tb_FName.Text.Trim & "%"))
                End If
                vMemb.Query.Where(vMemb.Query.Modalities <> ",")
                vMemb.Query.es.DefaultConjunction = esConjunction.Or
                Dim arr As New List(Of esWhereItem)
                For Each itm As ListItem In lb_Mods.Items
                    If itm.Selected = True And itm.Value <> "" Then
                        vMemb.Query.Where(vMemb.Query.Modalities.Like("%" & itm.Value & ",%"))
                    End If
                Next
                vMemb.Query.OrderBy(vMemb.Query.LastName, esOrderByDirection.Ascending)
                vMemb.Query.OrderBy(vMemb.Query.FirstName, esOrderByDirection.Ascending)
                Dim str As String = vMemb.Query.es.LastQuery
                vMemb.Query.Load()

This is the LastQuery ran.

SELECT * FROM [v_AHNA_MemberDirectory] WHERE [Date2] >= @Date21 AND [Modalities] <> @Modalities2 OR [Modalities] LIKE @Modalities3 OR [Modalities] LIKE @Modalities4 OR [Modalities] LIKE @Modalities5 ORDER BY [LastName] ASC,[FirstName] ASC

BUT I need it to read like the following.

SELECT * FROM [v_AHNA_MemberDirectory] WHERE [Date2] >= @Date21 AND [Modalities] <> @Modalities2 AND ([Modalities] LIKE @Modalities3 OR [Modalities] LIKE @Modalities4 OR [Modalities] LIKE @Modalities5) ORDER BY [LastName] ASC,[FirstName] ASC

As you can see what i'm trying to do is just section off the last pieces of it, i need to do an AND for the first three, then AND the OR's together. any help would be greatly appreciated.

Andrew

Reply

Mike.Griffin replied on 01-17-2008 4:34 PM

rated by 0 users

Unfortunately this is a short coming of our current API, that is, building a Where clause with mixed groups and conjunctions within a for loop by adding one item at a time. It's very easy with our API to build complex where clauses with paren and native AND and OR operators but not within a for loop.

Dim coll As New EmployeeCollection
coll.LoadAll()
Dim q As EmployeeQuery = coll.Query

q.Where((q.FirstName = "Mike" OR q.LastName = "Griffin" OR q.Supervisor = "Joe") AND q.Age = 21)

q.Load()

Using a for loop you are probably better off using the .AND and .OR functions in combination with the Default Conjuction, but it's very hard to get straight. I hate to say it but in this case you might want to call a custom proc

I was talking to David tonight about this post and we are going to create a new API for this entirely in ES2008 (a new API for building Where clauses in a for loop that is).

Reply

no1ross replied on 01-18-2008 8:22 AM

rated by 0 users

Thank you for looking into this,  I look foward to see what the new API will look like.

For now i will create a custom proc for this.

Thanks,
Andrew

Reply

LBowers replied on 05-07-2008 7:25 AM

rated by 0 users

Has this made it into 2008?

I'm currently experiencing (what I think is) a similar problem

I need to render the following SQL from within ES:

SELECT *
FROM [vwAssessmentResult]
WHERE [CompanyId] = 1 AND ([CompanyUnitId] = 2 OR [CompanyUnitId] = 1 OR [CompanyUnitId] = 3)

The query is built up dynamically:

            this.Query.Where(this.Query.CompanyId == companyId);

            if (companyUnitIds != null)
            {
                // THESE NEED TO BE "OR"D WITH THE ABOVE "AND"
                foreach (string unitId in companyUnitIds)
                {
                    this.Query.Where(this.Query.CompanyUnitId == unitId);
                }
            }

Am I missing something, or is this a shortcoming of ES? I need to get this up and running a.s.a.p (client is in tomorrow morning at 9:30am).

Reply

pritcham replied on 05-07-2008 7:55 AM

rated by 0 users

Hi Leigh

Would you be able to use an "IN(...)" clause for the second part of your query your case - I know it's a different query but I think it should still skin the same cat if you know what I mean! If so then you could build up an array of CompanyUnitId's inside your loop, and pass that array to the IN(....) clause - make sense?

Cheers and hope that helps

Martin

Reply

LBowers replied on 05-08-2008 2:24 PM

rated by 0 users

Thanks Martin - That worked a treat. I got so hung up with trying to get the original query working in ES that I never considered the alternative SQL approach.

Cheers mate.

Reply

Mike.Griffin replied on 05-08-2008 6:55 PM

rated by 0 users

Way to go Martin, we appreciate your help as always, for the record I wanted to post this though I didn't test it (that is, setting the default conjuction to OR instead of AND

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

this.Query.Where(this.Query.CompanyId == companyId);

if (companyUnitIds != null)
{
    // THESE NEED TO BE "OR"D WITH THE ABOVE "AND"
    foreach (string unitId in companyUnitIds)
    {
        this.Query.Where(this.Query.CompanyUnitId == unitId);
    }
}

I like your IN solution better still I think. 

Reply

pritcham replied on 05-09-2008 12:27 AM

rated by 0 users

No problem Leigh - I can be a little too focussed on solving things in a specific way too sometimes, find a fresh pair of eyes helps see things from a different angle every now and then, just glad it solved your issue.

Cheers

Martin

Reply

Isotope replied on 06-27-2008 12:26 PM

rated by 0 users

Did this improvement make it into ES2008?  I'm still using an early release of ES2007 (upgrading take a bit of effort in my situation), and encountered the same problem.

I'm creating a search feature for my application that allows users to search the database using my custom UI.  It isn't an open-ended UI: it just provides a series of inputs (title, version, created by, etc) and will build a dynamic query for all inputs the user selected, using either an and-based or or-based search.  This works fine, and might generate a query like this:

Select * from users where firstname like '%da%' and lastname like '%k%' and createdbyuserid = 5 -- (could also be or's instead of ands)

The problem is that I'm now trying to combine searches, and running into issues with combined ands and ors.  After combining multiple searches, I might get something like the following:

Select * from users
where
 (firstname like '%da%' and lastname like '%k%' and createdbyuserid = 5)
 or
 (firstname like '%ai%' and lastname like '%k%')
 or
 (deletedon is not null or isactive = false)

So the and/ors are the same across one level ("and, and, or" for the individual searches, respectively, and "or" for the combined search), but any time I try to combine the esWhereItems from each item into one large query, everything either gets turned to ands or ors.  I assume this is because the .And() and .Or() methods return List<esWhereItem>, which stores no information about whether they're and'd or or'd together.  Obviously, this situation can't be solved by using an In term, as before.

Keep me posted (thanks).

Reply

David.Parsons replied on 06-27-2008 4:32 PM

rated by 0 users

The DynamicQuery parenthesis fix did make it into the ES 2008 release. I just posted this FAQ with examples:

 http://community.entityspaces.net/forums/thread/10046.aspx

Reply

Isotope replied on 06-30-2008 8:51 AM

rated by 0 users

Oh nice!  Thanks, David.  I guess it's time to upgrade to 2008.

Mike, to clarify, here's what I was doing: my application allows users to run simple searches based on a variety of criteria that is specific to the entity they're searching for.  These simple searches do not allow repeat-use of individual terms (ex: Title = "Bob" + Title != "Larry") or any logic between terms (Title != Description + ModifyDate > CreateDate).  Advanced logic is supported via combined searches: you can take 2 or more existing searches and combine them to find the union or intersection of those searches.  I try to have each sub-search assemble its search criteria, then combine them all within the parent [combined] search.  I hoped this would generated a composite query with parentheses and a combination of ands/ors, but as I said earlier, it just generates a flat query with no parens and all ands or all ors. 

class MySearch
{
   // Sub-search properties (for combined searches)
   List SubSearches; // List of MySearch
   bool IsUnion;
   
   // This-search properties (for stand-alone searches)
   List SearchCriteria; // List of esWhereItem
   bool IsOptimistic;

   List PrepareSearch() // List of esWhereItem
   {
      MyCollection matches = new MyCollection();

      if(this.SubSearches.Count > 0)
      {
         if(this.IsUnion)
            matches.Query.es.DefaultConjunction = esConjunction.Or;
         else
            matches.Query.es.DefaultConjunction = esConjunction.And;

         foreach(MySearch subSearch in this.SubSearches)
            matches.Query.Where(subSearch.PrepareSearch());

         return null;
      }
      else
      {
         // Build search criteria based on user selections from the UI.
         this.PrepareSearchCriteria();

         if(this.IsOptimistic)
            return matches.Query.Or(this.SearchCriteria);
         else
            return matches.Query.And(this.SearchCriteria);
      }
   }
}