Is it possible to dynamically add .orWhere to GlideQuery?

[Rob Humphries]

Problem

I want to add multiple .orWhere calls to a GlideQuery, one for each entry in an array, but this returns no results and no error. I might have just made a scripting error, so if anyone knows how to correct it that would be great to know. Or if this is a limitation of GlideQuery also useful to know.

Example

The example below does not return an error but also returns no results.

//I have an array of dates, the real array could be very long
var dates= ["1926-07-22","1957-01-04","2012-12-12"];

//I want to get records where a given date field matches one of these dates in one query
var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
var query = new global.GlideQuery(table)
            .where(dateField, dates[0])
//go through each additional date in the array and add a .orWhere call
            for(var k = 1; k<dates.length; k++){
                 query.orWhere(dateField,dates[k])
            }
            query.select(fields)
            .forEach(function(u){
                recs.push(u);
            })

I know I can get the correct results with the following 2 approaches:

1. Doing a separate GlideQuery for each date.

This get slow quickly. 3 seconds for about 800 queries.

var dates= ["1926-07-22","1957-01-04","2012-12-12"];

var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
for (var k in dates) {
    new global.GlideQuery(table)
        .where(dateField, dates[k])
        .select(fields)
        .forEach(function(u){
            recs.push(u);
        })
}

2. Using GlideRecord instead.

This works, but we like some of the benefits of using GlideQuery like the nice error etc.

var dates= ["1926-07-22","1957-01-04","2012-12-12"];

var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
var rec = new GlideRecord(table);
var or = rec.addQuery(dateField,dates[0]);
for(var k = 1;k<dates.length;k++){
    or.addOrCondition(dateField,dates[k]);
}
rec.query();
while(rec.next()){
    var obj = {};
    for(var l in fields){
        obj[fields[l]]=rec.getValue(fields[l]);
    }
    recs.push(obj);
}

[Tudor]

Hi,

What happens if you do it like this?

//I have an array of dates, the real array could be very long
var dates= ["1926-07-22","1957-01-04","2012-12-12"];

//I want to get records where a given date field matches one of these dates in one query
var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
new global.GlideQuery(table)
            .where(dateField, dates[0])
//go through each additional date in the array and add a .orWhere call
            for(var k = 1; k<dates.length; k++){
                 .orWhere(dateField,dates[k])
            }
            .select(fields)
            .forEach(function(u){
                recs.push(u);
            })

Regards,

 Tudor

[Rob Humphries]

Hi Tudor, 

It's a good idea, but unfortunately when I try this without declaring query variable it just throws a Javascript compiler exception. I guess it doesn't know what the .orWhere is a method of.

It's a good point though because actually you get the same "no error and no results" behaviour if you add the query variable on every line like this...

var dates= ["1926-07-22","1957-01-04","2012-12-12"];

var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
for (var k in dates) {
    var query = new global.GlideQuery(table)
        query.where(dateField, dates[k])
        query.select(fields)
        query.forEach(function(u){
            recs.push(u);
        })
}

I guess it must be that the initial GlideQuery doesn't return anything and it's only when you use one of the terminal methods like forEach that you can use the query variable again.

If that is the case then I guess this is just a limitation of GlideQuery and if I want to build up large sets of OR conditions with for loops I'll have to use GlideRecord.

Any other ideas for how to achieve this would be great though!

Thanks

Rob

[Tudor · Accepted solution]

Directly from the blog

https://developer.servicenow.com/blog.do?p=/post/glidequery-p7/

it appears that .where accepts the "IN" operator

Try using this:

var dates= ["1926-07-22","1957-01-04","2012-12-12"];

var recs = [];
var fields = ["field_name_1","field_name_2","field_name_3"];
var dateField = "date_field_name";
var query = new global.GlideQuery(table)
        query.where(dateField,"IN", dates)
        query.select(fields)
        query.forEach(function(u){
            recs.push(u);
        })
}

Tudor

Regards,

 Tudor

[Rob Humphries]

Hi Tudor,

Thanks very much, I thought I had tested that approach and not had any luck, but I must have made a mistake because it is working perfectly.

Thanks for your help !