Understanding logic in SearchEngine
I'm trying to understand how the SearchEngine work.
What is the difference between the 2 searchengines below? I get 2 different results. In case
In the second case I get a row, where y_eb_eval_koder.x_ft_heading == '82', which it shouldn't be allowed to.
Case 1:
String null;
SearchEngine se;
se.addField('y_eb_eval_koder.id');
se.addField('y_eb_eval_koder.x_ft_heading');
se.addCriteria('y_eb_eval_koder.x_ft_heading', 'Equals', '80', 'And', 1);
se.addCriteria('y_eb_eval_koder.x_eb_post', 'Is', null, 'Or', 2);
se.addCriteria('y_eb_eval_koder.x_eb_post', 'Equals', '0', 'And', 2);
se.buildSql() gives:
select a0.id, a0.x_ft_heading from y_eb_eval_koder a0 where a0.x_ft_heading = ? and (a0.x_eb_post is NULL or a0.x_eb_post = ?)
//reducing parantheses
select a0.id, a0.x_ft_heading from y_eb_eval_koder a0 where a0.x_ft_heading = ? and (a0.x_eb_post is NULL or a0.x_eb_post = ?)
Case 2:
String null;
SearchEngine se;
se.addField('y_eb_eval_koder.id');
se.addField('y_eb_eval_koder.x_ft_heading');
se.addCriteria('y_eb_eval_koder.x_eb_post', 'Is', null, 'Or', 1);
se.addCriteria('y_eb_eval_koder.x_eb_post', 'Equals', '0', 'And', 1);
se.addCriteria('y_eb_eval_koder.x_ft_heading', 'Equals', '80', 'And', 2);Using se.buildSql() and reducing parantheses again, I get this:
select a0.id,a0.x_ft_heading from y_eb_eval_koder a0 where ((a0.x_eb_post is NULL or a0.x_eb_post = ?) and ((a0.x_ft_heading = ?)))
//reducing parantheses
select a0.id,a0.x_ft_heading from y_eb_eval_koder a0 where (a0.x_eb_post is NULL or a0.x_eb_post = ?) and a0.x_ft_heading = ?)
Trusting the printed SQL I end up with the following:
Case 1: select a0.id, a0.x_ft_heading from y_eb_eval_koder a0 where a0.x_ft_heading = ? and (a0.x_eb_post is NULL or a0.x_eb_post = ?)
Case 2: select a0.id, a0.x_ft_heading from y_eb_eval_koder a0 where (a0.x_eb_post is NULL or a0.x_eb_post = ?) and a0.x_ft_heading = ?
Isn¨t those the exact same expressions, just with the 2 where-statements switched?
Alle Svar (7)
Yes, the queries are the same. And yes, none of them should yield x_ft_heading = 82, so case 2 is wrong / bug
Usually, I try to first gather all AND expression and put the ORs behind them. This way you most likely get what you want.
1. Always test queries against expected result, except for simple cases having AND only and no parentheses - don't trust the result of buildSql
2. Keep your criteria as simple as possible
Do note that SearchEngine does not actually execute the SQL query that you are seeing here, it goes through NetServer instead. (unless you enabled bypass netserver)
I always try to get the criteria as simple as possible. But I'm also trying to understand how the SE works / eg. where its flaws might be :).
The buildSql was just to prove my point, that the logic should be the same - I do not usually look at this, but rely more on the composition of the SE.
So the conclusion should be, that they best way to use the SearchEngine is to do a little of black magic, sacrifice a 3-eyed frog, and hope for your best? I have worked with this for 7 years, and still havent found anyone who can truly tell me how these works :D.
Hi,
The only way to validate your query is by running/testing it.. As @David said what you get from buildSql() is not what is actually executed, as it then sends the data to the NetServer who does its own magic.
What I CAN say is that you could try to use IN instead of OR => bug , as it in other cases have yielded better results.
There is also another thread explaining that you could/should insert a dummy as indentation 0, but I'm not sure how accurate that is in this specific case.
Besides that there is really nothing to do about this, as we have no plans to change/update this method to more accurate reflect what is being executed.
It's great for simple queries, but take it with a grain of salt when doing more complext things.
//Eivind
Hi,
I understand its hard to trust it when it yields wrong results. To be hones this method (buildSql()) should PROBABLY be deprecated, but its still useable (even though you shouldn't trust it without testing the actually exection).
I'm not sure i understand what you mean by 'no other way to filter data in a fast way', you could use the Dynamic archive provider for something similar (depending on your usercase)? Depends on the context and what you are trying to do, though.
Hope this helps!
//Eivind
Hi Elvind.
Can you explain to me how the AND and OR is supposed to work?
Normally I don't even look at buildSQL. But BuildSQL outputs the AND and OR in a way, that aligns with my theoretical understanding of how they should work :).
Last time I checked any archive provider was far from easy and fast to write, when you do it trough crmscript. Maybe something has changed, and I should take a look at it again. And if it is the only reliable way, I guess I have to :).
'IN' is only possible to use when you are dealing with integers, since strings nor date is accepted this way.
I've done some further testing, and I have eliminated the posibility to have anythiing with the null criteria from my OP example. It definitily has something to do with the way OR is handled. We can hope that the bug you mention has the same sources of error.
See the example below (which gives me wrong output). Notice the crit-letters.
cred.addCriteria('y_eb_eval_koder.x_password', 'BeginsWith', 'mx', 'Or', 1); //Crit A
cred.addCriteria('y_eb_eval_koder.x_password', 'BeginsWith', 'zw', 'And', 1); //Crit B
cred.addCriteria('y_eb_eval_koder.x_ft_heading', 'Equals', '80', 'And', 2); //Crit CUsing Crit lettes I've put to the right, what I want is:
(A OR B) AND CIt looks like the output is the following logic. I get a lot of 'mx' and 'zw' passwords, but only 'zw' has heading 80.
A OR (B AND C)Because when I change the lines to be:
cred.addCriteria('y_eb_eval_koder.x_password', 'BeginsWith', 'zw', 'Or', 1); //Crit B
cred.addCriteria('y_eb_eval_koder.x_password', 'BeginsWith', 'mx', 'And', 1); //Crit A
cred.addCriteria('y_eb_eval_koder.x_ft_heading', 'Equals', '80', 'And', 2); //Crit C..I get a lot of 'mx' and 'zw' passwords, but here only 'mx' has heading 80.
B OR (A AND C)