SearchEngine and criterias

RE: SearchEngine and criterias

Wouldn't it be like this? 

#setLanguageLevel 3;
SearchEngine se;
se.addField("contact.name");
se.addField("contact.number2");
se.addField("contact.emailAddress");
se.addCriteria("contact.name","OperatorEquals", "Hans", "OperatorAnd",0);
se.addCriteria("contact.emailAddress","OperatorEquals","hans@something.no","OperatorOr",0);
se.addCriteria("contact.name","OperatorEquals","Hans" , "OperatorAnd",1);
se.addCriteria("contact.number2","OperatorEquals","12345","OperatorAnd",1);

print(se.buildSql());

You can use buildSql() to see what the output will look like

RE: SearchEngine and criterias

Just did that Hans. Feeling a little stupid now :)

SearchEngine se; 
se.addField("contact.name");

se.addCriteria("contact.name", "OperatorEquals", "Ummair Tahir", "OperatorAnd", 0);
se.addCriteria("contact.emailAddress", "OperatorEquals", "ummairt@domain", "OperatorOr", 0);
se.addCriteria("contact.name", "OperatorEquals", "Ummair Tahir", "OperatorAnd", 1);
se.addCriteria("contact.number2", "OperatorEquals", "1234", "OperatorAnd", 1);


printLine(se.buildSql());

RE: SearchEngine and criterias

Hi Ummair

dealing with SearchEngine criterias can be annoying sometimes as it often requires a lot of trial and errors.

What I usually do when struggling with this is to use buildSql() function on the SearchEngine class to see what the output is.

For example, your SQL:

SELECT  name from contact 
WHERE  (name = 'Ummair' AND email = 'umta@domain.com') 
OR     (name = 'Ummair' AND number2='1234')

could be translated to this in SearchEngine:

SearchEngine se;
se.addField("contact.name");
se.addCriteria("contact.name", "Equals", "Ummair", "And", 0);
se.addCriteria("contact.(Email->contact_id).email_address", "Equals", "umta@domain.com", "Or", 0);
se.addCriteria("contact.name", "Equals", "Ummair", "And", 1);
se.addCriteria("contact.number2", "Equals", "1234", "Or", 1);

Which would output:

select a0.name from contact a0 left join Email a1 on (a1.contact_id = a0.contact_id) 
where (a0.name = ? and a1.email_address = ?)
or ((a0.name = ? and a0.number2 = ?))

Which looks like your query.

I'm no expert on grouping with SearchEngine, but my "go to" solution when wanting several groups is to increase the last integer in the SearchEngine (Priority), ref the documentation in CRMScript:

"Priority: A number. All criterias with the same number will be place inside the same brackets".

I advice you to test it out, do a print(se.buildSql()); to see that SQL query being generated and you should be able to get the results that you want.

Edit: See that Hans was a bit quicker than me to respond.

RE: SearchEngine and criterias

Just a little precaution: The buildSql() method shows what the query would look like if executed by CS (only possible using bypassNetServer). But since this is passed to NetServer, the query will look different when executed there.

RE: SearchEngine and criterias

I would agree with Stians note, I was just about to add it myself. :)

I have been in situations where the output of .buildSql() looks correct but where the query run via the NetServer returns something else which is not comparable. I don't remember my exact case now though.

I don't know if it's possible to come up with the actual converted "truth" using some kind of trace/debug mode? Anybody know?

I think I ran a trace on the SQL Server or something in a VM-installation to get the actual sql query that the NetServer sent.


Stian:

Regarding the bypassNetServer, what is the actual status of that function right now? Is there different behaviour Onsite compared to Online when enabling this? Meaning, does Online have stripped functionality in some way compared to Onsite?

I have gotten indications from colleagues that this "feature" doesn't seem to work to the same extent in Online anymore.

Also, is there any specific differences in the return data that should be expected? I'm thinking of the representation of null-values, differences in how compare criterias can be interpreted, etc.

Personally I tend to not use it anymore as it seem to be more or less a legacy feature that might get revoked at any time soon. :)

/Marcus

RE: SearchEngine and criterias

Sorry for hijacking the post... :)

SO RnD:
Could somebody explain the programmatic workflow for when the SearchEngine calls the Netserver with a query?

What is sent from Service to NetServer? Is the the actual SQL-query from buildSql() or is it some kind of query object (if so, which object)?

Which function in NetServer is actually called from Service?

It would be interesting to understand what could go wrong and why. Like, which kind of parts of a query or which kind of queries will likely get distorted by NetServer so it doesn't return what's expected.

/Marcus

RE: SearchEngine and criterias

Hijacking a post eliminates the possibility to award someone else credit for answering your question. Please repost your question in a new thread and I will clean this up accordingly.