SuperOffice Community

Hi,

I am struggling with getting the query right which should give me the same result as:

My syntax is:

se.addCriteria("ticket.ticket_status", "OperatorIn","9,10","OperatorAnd",0);
se.addCriteria("ticket.category.fullname", "OperatorBeginsWith", "AMS","OperatorOr", 1);
se.addCriteria("ticket.category.fullname", "OperatorBeginsWith", "HDQ","OperatorOr", 1);

But it does not give me the same result. It gives me:

All tickets with (status 9 or 10) and Category begins with AMS

All tickets with Category begins with HDQ

The priority I am using should place the last two criteria between the same brackets, but it does not.

Any clue why this is not working?

By: Niels van Broekhoven
11. okt 2017

PARTNER

RE: Struggling SeachEngine

Hi Niels, looks ok to me.

Here is a similar example;

SearchEngine se;
se.addField("ticket.id");
se.addCriteria("ticket.ticket_status", "in", "1,2");
se.addCriteria("ticket.category.fullname", "beginswith", "Bruker", "or", 1);
se.addCriteria("ticket.category.fullname", "beginswith", "Kun", "or", 1);
printLine(se.buildSql());
printLine(se.countRows().toString());
for (se.execute(); !se.eof(); se.next())
  printLine(se.getField(0));

This generates SQL like this:

select 
  a0.id 
from ticket a0 
left join ej_category a1 on (a1.id = a0.category) 
where a0.ticket_status in (1,2) 
and (a1.fullname like N'Bruker%' or a1.fullname like N'Kun%')

Is there a chance that the Find Requests screen filters out some tickets that you don't have access to, and thats the offset you're seeing?

Av: Frode Lillerud 11. okt 2017

STAFF

RE: Struggling SeachEngine

If you use bypassNetServer, SearchEngine will always show the correct amount. If not, it will use the rights of the user executing the script.

As Frode suggests, there might be two things (as far as I know) that may alter the results when using "Find requests".

1. If you get a count larger than the page view (displaying 1-25 of x), it will only show a guestimate. Clicking the bottom row will show you the correct results.

2. As Frode mentions, are there any restrictions on the categories?

Av: Simen Mostuen Iversen 12. okt 2017

RE: Struggling SeachEngine

Hi,

Well actually, the problem is the other way around.

Find Requests shows me the right results, but the SearchEngine does not. Search Engine gives me way too much results.

Could it be that OperatorBeginsWith and OperatorOr are not a good combination?

In the end solved this by:

//ID's van categorien welke opgenomen moeten worden in de monitor
String catIds="13,10,34,28,6,23,8,29,17,12,56,24,58,22,25,5,35,32,145,26,7,";
se.addCriteria("ticket.category", "OperatorIn",catIds,"OperatorAnd",0);

But still don't know why it didn't work...

Av: Niels van Broekhoven 12. okt 2017

STAFF

RE: Struggling SeachEngine

Well, your criterias seems correct.

Can you show us your entire code, and as well, a printout of se.buildSql() so that we can see what happens?

Av: Simen Mostuen Iversen 12. okt 2017

RE: Struggling SeachEngine

It might be because the criteria OperatorIn sometimes act buggy.

See: https://community.superoffice.com/en/bugs-wishes/Product-Issue/?bid=44807

I have actually experienced scenarios where running the printSQL from the SearchEngine in the SQL database and through CS have given different results.

Not sure but something tells me I had to find other ways than using the critera OperatorIn..

Av: Gustaf Cullin 16. okt 2017

Svar