New Posts New Posts RSS Feed: Complex OQL query with multiple AND.s and OR's
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Complex OQL query with multiple AND.s and OR's
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Topic: Complex OQL query with multiple AND.s and OR's
    Posted: 12-Jul-2007 at 4:15pm
 

I've just started to work on this project again today, it has been on chalenge with SplendidCRM (it's not a joke) and somehow it wins (I cannot believe myself) cause it's closer to the needs (what's amazing is that there's really quite nothing to see for the moment, just fiew lists... users prefers cessna to 747).

So I'm adding fiew filters to my list and encounter a problem with the entityquery object (well I've encountered this problem before with ideablade)...

I've got listboxs that give me int arrays and I try to build a query by adding clauses on a foreach...

the main goal is having this kind of query :

[demande](typeid=3 or typeid=4 or typeId=5) and (stateId=2 or stateId=12) and...

but the entityquery doesn't seem to be able to do this!? (the result is like that :[demande](((((typeId=3 or((typeid=4 or typeId=5....) Is this a known issue ? (I will use a passthruquery but if you can help me with this, it would be fine)

For the moment I've spent approximativly 2 days on the project (I've changed the database structure n user interface in minutes - it's really cool). I think if you can help me with the previous question it will be fine - no time for the moment for a visit (but thanks for the invitation) - but if you have information about the commercial version (price) and the webservices tools (thinks to read)?

I may use DevForce on two other projects (web again) - a product website (registring user, licencing, product update,...) and a community web site (a wiki strange thing). If there are other ressource about web, I thank you again for this !
Back to Top
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353 		Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 4:18pm

Putting together a complex query having multiple AND's and OR's and NOT's is something that is not always easy to get exactly right.  The quick answer is that our query mechanism uses a stack machine and works like a calculator and doesn't understand parentheses.  Here are some examples that might be helpful

RPN
Algebraic
A B and
A and B
A B or C and
(A or B) and C
A B C or and D and
(A and (B or C)) and D
A B C or D and and
A and ((B or C)) and D)
A B and C D and or
(A and B) or (C and D)
A not B or
(not A) or B
A B or not
Not (A or B)
A B
A and B
A B not
A and (not B)

I don't think think that there is any bug here.  I think you have just not built your query correctlly.  If this slide doesn't help, please send me your query (in Algebraic format) and the code for your RdbQuery.
Back to Top
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 4:19pm
Thanks for your answer,
It looks like the OQL is not as easy to use as the request I try to do :-)
I will try to make my dynamic query with this, but it looks like it will be much more difficult than building it the SQL way [what I've already done](some kind of paradox no ?).
 
Thanks again
Back to Top
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353 		Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 4:20pm
I would argue the opposite, but then I am much more familiar with OQL than SQL.  It depends upon your experience and your background as to what is the easier way for you. If you vastly prefer using SQL over OQL for complex queries, and you don't anticipate making disconnected queries, you should consider using PassThruQuery 
Back to Top
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 4:22pm
I have another question.
Can you tell me how you would do theses (I try an answer after the =>):
(A or B or C) => AB or C or
(A or B or C or D) => AB or C or D or ?!?
(A or B or C) and (D or E or F)  and (G or H or I)=> AB or C or... oups?! :-)... your knowledge is welcome :-) !!!
Back to Top
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353 		Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 4:23pm
I think that you got it!
 
You can also do query merging.
 
A query merge is the equivalent of 'ANDing' the two queries together. Both queries must be for the same EntityType.  In your last example,
 
EntityQuery1 = A or B or C = AB or C or
EntityQuery2 = D or E or F = DE or F or
EntityQuery3 = G or H or I = GH or I or
EntityQuery1.Merge(EntityQuery2)
EntityQuery1.Merge(EntityQuery3)

=================================== 

(A or B or C) => AB or C or  
 
Correct
 
(A or B or C or D) => AB or C or D or ?!? 
 
Correct
 
(A or B or C) and (D or E or F)  and (G or H or I)=> AB or C or... oups?! :-)... your knowledge is welcome :-) !!! 
 
This is a tough one.  I hope I got it right.
 
AB or C and DE or F and GH or I or and
 
removing ands goes to
 
AB or CDE or FGH or I or
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down