SearchEngine criteria confusion
Hello everyone,
I'm having some problems creating a SearchEngine query. I've tried changing the order and priority of the criteria blocks but can't seem to find the right combination.
What I'm trying to do:
Fetch all tickets that belong to a list of companies (38 or 2) AND matches a search text (34) either in ID or Title.
What's being returned:
1. All tickets belonging to company 38 and 2
2. Just ticket 34, which belongs to another company altogether.
How can I get ONLY tickets that match the search text AND belong to these companies?
My code:
String searchText = "34";
SearchEngine se;
se.bypassNetServer(true);
se.addField("ticket_customers.ticket_id");
se.addCriteria("ticket_customers.ticket_id", "Equals", searchText, "OR", 0);
se.addCriteria("ticket_customers.ticket_id.(ticket->id).title", "Contains", searchText, "AND", 0);
se.addCriteria("ticket_customers.customer_id.(person->person_id).contact_id", "Equals", "38", "OR", 1);
.... potentially more ....
se.addCriteria("ticket_customers.customer_id.(person->person_id).contact_id", "Equals", "2", "AND", 1);
Alle Svar (3)
Hi,
I think the problem is that the first two are at the same level without parenthesis which becomes:
A or B and (C or D)
which is ambigious. Is it (A or B) and (C or D), or is it A or (B and (C or D))?
You can solve this by setting indentation level to 1 for the two first ones, and then inserting a dummy criteria with level 0 as number 3 (e.g. "ticket_customers.id > 0"). Not very elegant, but it solves the problem.
But, we also support OperatorIn, which means you can pass in the contact_id as a comma-separated string of multiples ID's. E.g. "ticket_customers.customer_id.(person->person_id).contact_id", "In", "38,2,42,etc".
Sverre
Thanks!
I ran a buildSql on the SearchEngine and it returned the following:
where ((a0.ticket_id = N'34' or a2.title like N'%34%') and ((a4.contact_id = N'38' or a4.contact_id = N'2')))
It seems to me that these are very clearly separated as (1 OR 2) AND (3 OR 4) but I might be wrong.
On the other hand, though I don't understand why, the dummy criteria works a treat!
The IN list is also a good tip. Is there a function for turning an array into a comma separated list? I can't seem to get any help with arrays in the script editor :(
Hi,
I believe the buildSQL() is not entirely accurate any more... :-/
Arrays don't have a buildString method (should have), but we have an old StringArray class, Vector, which may be used:
Vector ids;
ids.pushBack("12");
ids.pushBack("23");
print(ids.buildString(","));
Sverre