push_pin
lock
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?
Last reply
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 C Using Crit lettes I've put to the right, what I want is: (A OR B) AND C It 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)