BreezeJS "WHERE value IN array"

Question

Can specify in the where clause that I want the data that has the column value equal to some values from an array?

For example:

EntityQuery.from('Customers')
.where('DepartmentID','in','[3,5,6]');

Or how else should I do it efficiently since the table has a lot of entries an it wouldn't be efficient to retrieve all of them? Is it efficient if I do it one by one?

Koreykorff

Answer 1

Just add multiple predicates -

var myArray = [3, 4, 5];
var predicate = new Breeze.Predicate;

var query = EntityQuery.from('Customers');

if (myArray) {
    var criteriaPredicate = null;
    $.each(myArray, function (index, item) {
        criteriaPredicate = (index === 0)
            ? Predicate.create('DepartmentId', '==', item)
            : criteriaPredicate.or('DepartmentId', '==', item);
        if (Predicate.isPredicate(criteriaPredicate)) {
            predicate = predicate.or(criteriaPredicate);
        }
    });
}

query = query.where(predicate);

That may not run 100% correctly but should show you what to do - create predicates dynamically and add them to a total predicate and then to the query.

Cermet

I made it work, but apparently I can't use it because it builds HTTP requests that are too long... Thanks for the tip though! – Koreykorff

Answer 2

Using Breeze's new JSON query feature introduced in 1.5.1, you can create a “WHERE value IN array” clause like this:

var jsonQuery = {
    from: 'Customers',
    where: {
        'DepartmentID': { in: [3,5,6] }
    }
}
var query = new EntityQuery(jsonQuery);

Bootlace

Not much use for me after almost 1.5 years, but hope it helps whoever is interested. Thanks. – Koreykorff

Is there a way to do not in? – Urquhart

You can do this by wrapping the where clause with a not. So in the example above, you'd just wrap 'DepartmentID': { in: [3,5,6] } to be not: {'DepartmentID': { in: [3,5,6] }} – Bootlace

Answer 3

A bit late to the party but I needed the same thing as was able to do it like this:

public getFeaturedRestaurants(restaurantIds: number[]) { this.dataBreezeService.initialiseQuery('getFeaturedRestaurants', [restaurantIds]);

let query = new EntityQuery()
    .from('restaurants')
    .where("restaurantId", "in", restaurantIds)
    .toType('Restaurant')
    .expand('foodType, suburb.city')

return this.dataBreezeService.executeQueryCacheOrServerForList(query, false);

}

Assonance