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

[BenjaminTam]

Hi Todd,

I wasn't sure if you found the answer yet but there are some functions you can use to add the correct sub-queries you are looking for.

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

I believe it can be accomplished by the following:

var gr = new GlideRecord('your_table');
gr.addQuery('u_testboolean', true);
var q1 = gr.addQuery('u_testnumber', "<=", 3);
var q2 = q1.addOrCondition('u_testnumber', ">=", 8);
q2.addCondition('u_teststring", "!=", 'wibble');
gr.query();

The key function you are missing is addCondition(), which isn't well documented but can be used to place an AND condition as a subquery (similar to addOrCondition()). While I haven't tested the script above, it should give you a good idea of how it can be structured to provide you with the right results.

[ravi1_tandon]

Not sure if you were able to get the answer for your question as I have also faced similar issue when creating a query with grouping with add condition and the way I end up doing was writing a SQL statement with nested condition and then passing it in gs.sql("SQL query").

I did this is in background script as wanted to see the records and not sure if it will run in script include or business rule.

Let me know if this works.