Wrong Multiple filters

[kethan]

https://runkit.com/kethan/wherefilter

var DataSource = require('loopback-datasource-juggler').DataSource;
var ModelBuilder = require('loopback-datasource-juggler').ModelBuilder;
var ds = new DataSource('memory');
datas = [];

var Todos = ds.createModel('Todo', {
    title: { type: String, length: 255 }
});

for (var index = 0; index <= 10; index++) {
    datas.push({ title: 'hi ' + index });
}
Todos.create(datas).then((results) => {
    console.log('created', results)
    return Promise.resolve(results)
})

Todos.find( {
 limit: 10,
  order: [ 'title ASC' ],
  where: { id: { gt: 1, lt: 5 } } 
}).then(results => console.log(results));

But I am getting wrong results

[ssh24]

Hi, @kethan your where filter is incorrect. It should be: where: {and: [{id: {gt:1}}, {id:{lt:5}}]}

Here is the full code:

var DataSource = require('loopback-datasource-juggler').DataSource;
var ModelBuilder = require('loopback-datasource-juggler').ModelBuilder;
var ds = new DataSource('memory');
datas = [];

var Todos = ds.createModel('Todo', {
    title: { type: String, length: 255 }
});

for (var index = 0; index <= 10; index++) {
    datas.push({ title: 'hi ' + index });
}
Todos.create(datas).then((results) => {
    console.log('created', results)
    return Promise.resolve(results)
});

Todos.find( {
 limit: 10,
  order: [ 'title ASC' ],
  where: {and: [{id: {gt:1}}, {id:{lt:5}}]} 
}).then(results => console.log('results:', results));

Output:

sakibs-mac:juggler-issue-1327 ssh$ node server/boot/script.js
created [ { title: 'hi 0', id: 1 },
  { title: 'hi 1', id: 2 },
  { title: 'hi 2', id: 3 },
  { title: 'hi 3', id: 4 },
  { title: 'hi 4', id: 5 },
  { title: 'hi 5', id: 6 },
  { title: 'hi 6', id: 7 },
  { title: 'hi 7', id: 8 },
  { title: 'hi 8', id: 9 },
  { title: 'hi 9', id: 10 },
  { title: 'hi 10', id: 11 } ]
results: [ { title: 'hi 1', id: 2 },
  { title: 'hi 2', id: 3 },
  { title: 'hi 3', id: 4 } ]

[kethan]

Hi @ssh24 but in this https://github.com/strongloop/loopback-filters/ they use like this

applyFilter({
  where: {
    // the price > 10 && price < 100
    price: {gt: 10, lt: 100},

    // match Mens Shoes and Womens Shoes and any other type of Shoe
    category: {like: '.* Shoes'},

    // the status is either in-stock or available
    status: {inq: ['in-stock', 'available']}
  }
})

[ssh24]

@kethan That is not correct. You are more than welcome to submit a pull request to fix that.

[kethan]

@ssh24 I don't know how to fix that.

[ssh24]

@kethan I have updated the readme. Hopefully this will help you :)

Closing this issue as a result.

[kethan]

So for this also I need to use and?? Refer here : http://loopback.io/doc/en/lb3/Where-filter.html

where: {
    location: {
      near: userLocation,
      maxDistance: 2
    }
  }

[ssh24]

@kethan Not for that one. How ever if you wanted to do something like this which is technically the same as above (i.e the back-end interprets it this way):

where: {
   and: [
    location: {
      near: userLocation
    }, 
    location : {
       maxDistance: 2
    }
   ]
  }

[kethan]

@ssh24 It's very confusing than how come not for previous one back-end didn't interpret like that?? can't and be implicit?

Or is it only for user-defined fields?

[ssh24]

Actually, please ignore my last comment. What I said there is wrong and that filter would not work.

Reading more on the docs, near filter takes two additional properties out of which maxDistance is one of them.

For your example, gt and and were more like "operators" rather than filter itself.

You can however do this with near filters:

where: {
    location: {
      near: { and : [ ... more queries ...] },
      maxDistance: 2
    }
  }

[kethan]

@ssh24 Oh Ok. Can you check this https://docs.mongodb.com/manual/reference/operator/query/and/

This query can be also be constructed with an implicit AND operation by combining the operator expressions for the price field. For example, this query can be written as:

db.inventory.find( { price: { $ne: 1.99, $exists: true } } )

Why can't it be implicit and?

https://github.com/strongloop/loopback-connector-mongodb/pull/322

[ssh24]

I do not think we support implicit and statements and that is why filters like { price: { ne: 1.99, exists: true } } will not work as expected.

@jannyHou Could you correct me if I am wrong?

[kethan]

@ssh24 How about this? Should I use and?

ONE_MONTH = 30 * 24 * 60 * 60 * 1000;  // Month in milliseconds
transaction.find({
      where: {
        userId: user.id,
        time: {gt: Date.now() - ONE_MONTH}
      }
    }

[ssh24]

@kethan No that should work as expected. The where filter is converted into a sql statement of this form: select userId, time from <TABLE_NAME> where userId=<id> && time >= <someDate>.

The and property within the where filter can be used to explicitly say if you want to combine more than one expression. For example, this would work:

ONE_MONTH = 30 * 24 * 60 * 60 * 1000;  // Month in milliseconds
transaction.find({
      where: {
        userId: user.id,
        and: [ { time: {gt: Date.now() - ONE_MONTH}}, { time: {lt: Date.now() - (ONE_MONTH * 6)}}]
      }
    }

[jannyHou]

@ssh24 by

I do not think we support implicit and statements and that is why filters like { price: { ne: 1.99, exists: true } } will not work as expected.

I think you are right.

Loopback filter doesn't have exactly the same syntax with mongodb's filter, please read https://loopback.io/doc/en/lb2/Where-filter.html#and-and-or-operators about the syntax of operator 'and'.

[kethan]

@ssh24 @jannyHou So Why isn't and used for this? category and status ?

applyFilter({
  where: {
    // the price > 10 && price < 100
    and: [
      {
        price: {
          gt: 10
        }
      },
      {
        price: {
          lt: 100
        }
      },
    ],

    // match Mens Shoes and Womens Shoes and any other type of Shoe
    category: {like: '.* Shoes'},

    // the status is either in-stock or available
    status: {inq: ['in-stock', 'available']}
  }
})

[ssh24]

@kethan

1. the query with category is checking to filter out all the category that contains similar regex pattern.
2. the query with status is filtering by returning back all the object that has a status or in-stock or available.

Now you can obviously do random stuff with the filtering. You can do nested and/or queries on them. For example:

// match shoes and coats
where: {
and: [{category: {like: '.* Shoes'}}, {category: {like: '.* Coats'}}],
}

[kethan]

@ssh24 Ok Thank you :)

[ssh24]

@kethan You are welcome :)

Closing this issue. Please feel free to reopen it if you have any further questions.