Modeling complex GlideRecord queries

[tpalino]

I ran into a problem the other day when trying to model a GlideRecord query that was a little more complex than the norm. It seems like there's a huge blind spot in the way GR lets us do queries, but I'm still hopeful that someone can come up with an answer.

In this example, I have a table with the fields u_testboolean, u_testnumber, and u_teststring. The table has been loaded with 20 rows of sample data with the numbers 1 through 10, with a row with the boolean as true and false for each number. All the string values have been set to "wibble" except for the lines with number=4 (those are "bazbang") and number=9 (those are "foobar"). The query I want to run is:

(u_testboolean == true) && ((u_testnumber <= 3) || ((u_testnumber >= 😎 && (u_teststring != 'wibble')))

What I expect from that query with this data is that it will return 4 lines:
true 1 wibble
true 2 wibble
true 3 wibble
true 8 foobar

I can find no way using addQuery and addOrCondition to get this query to work, primarily because I can find no way to build a subquery to an or condition. In addition, in testing this I have found the following problems:

1) While you can call addOrCondition on a GlideRecord object, it appears to be a NOOP. Nothing is added to the SQL query when you do this. This does not match with the documentation, which shows GlideRecord as having the addOrCondition method.
2) Calling addOrCondition on a QueryCondition object works as expected when called with 2 or 3 arguments, but trying to call it with 1 argument throws an error that the method is not defined. This does not match with the documentation, which shows that addOrCondition supports being passed an encoded query string
3) While you can call addQuery on a QueryCondition object, it appears to be a NOOP as well. As there is no documentation I can find on the QueryCondition object, I can't tell if this is intended or an oversight.

If #2 worked, then I could concievably pass addOrCondition a query string 'u_testnumber>=8^u_teststring!=wibble' to build the subquery. Alternately, if #3 worked, I could possibly build nested QueryConditions to build it.

Of course, if GlideRecord would just expose the ability to set the WHERE clause of the SQL directly (like many other ORM models do), none of this would be a problem. Any complex query could be built without having to worry about the addQuery/addOrCondition methods.

-Todd

[Mikolz]

This is assuming you know what query you want...have you tried using an encoded query?

Heres a small example...

var queryString = 'active=true^category=Hardware^ORcategory=Network^state=10^ORstate=1^assignment_group=6717493b786b5c00910063ad31c0bf0f';

var GG = new GlideRecord("change_request");
GG.addEncodedQuery(queryString);
GG.query();

while(GG.next())
{
// some code....
}

When I have a lot of criteria that needs to be checked when I am going to query for records, I normally use an encoded query. Whats nice about them is, you dont need to write them out. Just go to the list of records and set the filter to whatever consitions you want and run it. From there right click the breadcrumb filter and select copy query. You can just paste that and assign a variable to it...

I think this is addessing what you are asking for?

[tpalino]

In this case I'm just using an example to illustrate the type of query that I can't create. An encoded query string won't work here, as far as I can tell, because there's still no way to group conditions together.

So I could have an encoded query:
u_testboolean=true^u_testnumber<=3^ORu_testnumber>=8^u_teststring!=wibble

But that's actually:
(u_testboolean == true) && ((u_testnumber <= 3) || (u_testnumber >= 8)) && (u_teststring != 'wibble')

Similarly, you could try and shuffle the statements around several ways (and I have) to try and express the original query in a way that would still get what you want, but I just can't manage to find a shuffle that works.

[eric_szonyi]

Todd, two questions:

1. Where are you doing this from ? A Business Rule ? A workflow activity ?
2. Have you tried using addEncodedQuery ? - http://wiki.servicenow.com/index.php?title=GlideRecord#addEncodedQuery

Let me know.
Eric

[tpalino]

Right now I'm just using the background script to test it out. Eventually, the logic would end up in a script include that would get called from either business rules or other script includes.

See my other response for why I can't get an encoded query string to work. It might work if I could pass the encoded query to addOrCondition. But without the ability to group statements in the query string I can't see a way to describe that query.