Filter on level 2 properties #517

daankets · 2014-08-29T12:41:11Z

I am trying to use the ?filter rest method, in order to query on nested properties. However, this doesn't seem to work:

/someResource?filter[where][field.subField][like]=abc

neither does this (which I think is wrong anyway):

/someResource?filter[where][field][subField][like]=abc

Any hints?

The text was updated successfully, but these errors were encountered:

fabien · 2014-08-29T13:09:26Z

Which connector are you using?

daankets · 2014-08-29T13:23:13Z

Memory in dev, mongo in test and beyond. It's the memory connector that has the issues. We use this one for our dev setup as it resets upon restart...

It's the 'dot' notation that is working with mongo.

fabien · 2014-08-29T13:29:59Z

This is currently a limitation of the memory connector.

@raymondfeng perhaps we can use something like https://github.com/logicalparadox/filtr or https://github.com/crcn/sift.js to enable this

daankets · 2014-08-29T13:31:12Z

Well, the nice thing about the memory connector is that it resets, so you test every run with a clean sheet. And of course it's rather speedy ;-)

fabien · 2014-08-29T13:32:37Z

Of course - however, you can easily reset the test database before you start, like this:

app.datasources['db'].automigrate(next);

daankets · 2014-08-29T13:33:33Z

Will this drop the entire schema?

fabien · 2014-08-29T13:40:22Z

Yes, but in the case of MongoDB collections will be empties and indexes re-created. There's no real schema, like with SQL databases.

daankets · 2014-08-29T13:41:31Z

Sure. Just calling it a 'schema' as a 'habit'

fabien · 2014-08-29T13:49:59Z

Of course! While collections are being recreated, they are not really removed from the database first. So any obsolete collections will stay around, until you delete them, or the database itself. AFAIK loopback will create the db for you, in case it doesn't exist.

raymondfeng · 2014-08-29T16:07:29Z

@fabien These libraries are interesting:

I also looked at:

https://github.com/louischatriot/nedb

Keep in mind, RDBs won't support nested queries.

fabien · 2014-08-30T10:24:31Z

@raymondfeng sift.js looks good to me, but it doesn't support skip/limit. nedb does, but it seems quite a bit more involved. It can probably be implemented as a new connector altogether.

Users should be made aware of the limits of RDBM's, which is perhaps easiest when having this as a separate connector - so users have to be explicit about it, instead of relying on the memory connector (for tests and so forth) to support any type (both SQL as well as NoSQL style) of query. It's probably easy to copy the mongodb connector's query normalization code and implement this.

Here are some more libraries of interest:

https://github.com/kawanet/obop
http://kawanet.github.io/kagodb/
https://github.com/sergeyksv/tingodb (but why use this, over mongodb?)
~~https://github.com/bevry/query-engine~~ (nevermind, too heavy)

bajtos · 2014-10-23T06:39:53Z

Loosely related: #683 How can i set condition where on include model

yagobski · 2014-11-24T21:34:05Z

How this can work for mysql connector? I have same issue

stringbeans · 2014-12-19T19:13:26Z

Looks like Loopback currently does not support nested include filtering. This seems like a huge limitation. I don't believe the db connector should make any difference because ideally the Loopback framework would work the same regardless of what db connector you're using

weslley39 · 2015-01-20T16:35:52Z

i'm having the same problem trying to do a nested include. Any solution ?

GameScripting · 2015-01-22T22:18:38Z

So are there any plans to support nested properties? Is there any way to get the "native" mongodb-connection so we can query the database without the loopback abstraction?

dominikriedel · 2015-01-27T22:07:11Z

Hey! I've got the Same Problem. I want to query a List of objects based on the property of an related model as well as the property of the wanted model e.g:

Event.find( { where: { and: [ { endDate : { lt : givenDate}}, { location.name : "Abc" }]} }, cb );

So, am I right, that this is not supported atm?

bajtos · 2015-01-28T08:21:02Z

I want to query a List of objects based on the property of an related model as well as the property of the wanted model.
So, am I right, that this is not supported atm?

You are right, this it not supported yet.

dominikriedel · 2015-01-28T08:24:20Z

so the easiest way would be to just query via SQL? Without any further knowlage: is there any caching mechanism I could use?

pungoyal · 2015-02-27T13:06:38Z

Can we upvote on this feature?

bajtos · 2015-02-27T13:45:02Z

Sure, leave a comment containing one of :+1: or +1 (as regular text, not like I did).

pungoyal · 2015-02-27T14:02:39Z

👍

kurtommy · 2017-12-30T08:27:08Z

in the doc i can read: Loopback supports filtering nested properties in three NoSQL connectors: Mongodb, Cloudant, Memory.

so a query like this it's impossible on mysql
User.find({where: {'address.tags.tag': 'business'}}

i can't see any valid workarount to achieve multiple join with where condition... that will require multiple queries leading performance issues (and resource waste)

any suggestion?

F3L1X79 · 2018-02-02T13:23:58Z

I am lost at the moment. This ticket is closed, but does filtering on nested properties work with mongodb?

nVitius · 2018-02-02T18:50:56Z

@F3L1X79 It works as long as the nested properties are embedded on the same document.

acrodrig · 2018-02-02T19:13:04Z

This does not work for MySQL. It should be possible to translate something like:

User.find({where: {'address.tags.tag': 'business'}}

Into JSON_EXTRACT conditions instead of simple SQL equality.

Can someone point me in the direction of the code where the translation from JSON to SQL is done? Thanks.

nVitius · 2018-02-02T19:20:40Z

@acrodrig I think most of the code you're looking for will be here: https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js.

zbarbuto · 2018-02-03T02:19:38Z

Also for anyone interested - there is support for this to some degree in postrgres using the json data type as per this PR

loopbackio/loopback-connector-postgresql#304

elviocb · 2018-03-03T12:43:08Z

Why the filter with property id does not work? If I change it to any other property name it works (ie: customer.name). I'm using mongodb connector.

// Does not work
        var filter = {
            where: {
                'customer.id': customerId
            },
            order: 'order_number DESC'
        }
// Works
        var filter = {
            where: {
                'customer.name': customerName
            },
            order: 'order_number DESC'
        }

Order.find(filter, function (err, orders) {..})

princecharmx · 2018-03-03T13:02:41Z

Hey @elviocb , this must be problem with customer.id stored as String and not ObjectId. Can you share how your Order model looks. It should be related to https://loopback.io/doc/ja/lb3/MongoDB-connector.html#strictobjectidcoercion-flag

elviocb · 2018-03-03T13:47:48Z

hey @princecharmx .. thanks for your reply!

That's my order model definition:

{
  "name": "order",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "status": {
      "type": "string",
      "required": true,
      "default": "open"
    },
    "customer": {
      "type": "object"
    },
    "customer_id": {
      "type": "string"
    },
    "discounts": {
      "type": [
        "object"
      ]
    },
    "discount_codes": {
      "type": [
        "string"
      ]
    },
    "comissions": {
      "type": [
        "object"
      ]
    },
    "email": {
      "type": "string",
      "required": true
    },
    "cart": {
      "type": "object"
    },
    "financial_status": {
      "type": "string",
      "required": true,
      "default": "pending"
    },
    "tags": {
      "type": "string"
    },
    "number": {
      "type": "number",
      "required": true,
      "default": 1000
    },
    "order_number": {
      "type": "number",
      "required": true
    },
    "name": {
      "type": "string",
      "required": true
    },
    "subtotal_price": {
      "type": "string",
      "required": true
    },
    "total_discounts": {
      "type": "string",
      "required": true
    },
    "total_price": {
      "type": "string",
      "required": true
    },
    "total_tax": {
      "type": "string",
      "required": true
    },
    "total_comissions": {
      "type": "string",
      "required": true
    },
    "tax_lines": {
      "type": [
        "object"
      ]
    },
    "cancel_reason": {
      "type": "any"
    },
    "canceled_at": {
      "type": "date"
    }
  },
  "validations": [],
  "relations": {
    "shop": {
      "type": "belongsTo",
      "model": "shop",
      "foreignKey": "shop_id"
    },
    "customers": {
      "type": "hasOne",
      "model": "customer",
      "foreignKey": "customer_id"
    }
  },
  "acls": [],
  "methods": {}
}

And this is how an order model is stored:

{
    "_id" : ObjectId("5a9a9b3b4fd628739efb4972"),
    "status" : "open",
    "customer" : {
        "name" : "Laila ",
        "lastname" : "Gonçalves",
        "email" : "laila@gmail.com",
        "gender" : "female",
        "age" : "23",
        "cpf" : "24898932100",
        "id" : "5a9352c29fe55f9a981661a8",
        "partner_id" : "5a80d66992856a1c701ab2c2"
    },
    "customer_id" : "5a9352c29fe55f9a981661a8",
    "discounts" : [],
    "discount_codes" : [],
    "email" : "laila@gmail.com",
    "cart" : {
        "items" : [ 
            {
                "id" : 994925047,
                "product_id" : "5a77690bd3715f5127e10cff",
                "title" : "Feijoada monster",
                "price" : "14.90",
                "quantity" : 1,
                "image" : "https://dummyimage.com/400x400/000000/ffffff.jpg"
            }, 
            {
                "id" : 518365228,
                "product_id" : "5a77694ad3715f5127e10d00",
                "title" : "Escondidinho Power",
                "price" : "14.90",
                "quantity" : 1,
                "image" : "https://dummyimage.com/400x400/cc14cc/ffffff.jpg"
            }
        ],
        "subtotal_price" : "29.80",
        "total_price" : "29.80",
        "formatted_price" : "R$29.80",
        "item_count" : 2,
        "total_discount" : 0,
        "discounts" : [],
        "tax_lines" : [],
        "total_taxes" : 0,
        "allow_payment_method" : true,
        "allow_discount_override" : false,
        "payment_method" : {
            "id" : "003",
            "display_name" : "Dinheiro",
            "name" : "money",
            "short" : "d"
        },
        "customer" : {
            "name" : "Laila ",
            "lastname" : "Gonçalves",
            "email" : "laila@gmail.com",
            "gender" : "female",
            "age" : "23",
            "cpf" : "24898932100",
            "id" : "5a9352c29fe55f9a981661a8",
            "partner_id" : "5a80d66992856a1c701ab2c2"
        }
    },
    "financial_status" : "pending",
    "tags" : "",
    "number" : 1000,
    "order_number" : 1002,
    "name" : "#1002",
    "subtotal_price" : "29.80",
    "total_discounts" : "0.00",
    "total_price" : "29.80",
    "total_tax" : "0.00",
    "total_comissions" : "5.60",
    "tax_lines" : [],
    "cancel_reason" : null,
    "canceled_at" : null,
    "shop_id" : ObjectId("5a8ddeba4f11315cb6076cc3"),
    "comission_ids" : [ 
        "5a9a9b3b4fd628739efb4973"
    ],
    "id" : ObjectId("5a9a9b3b4fd628739efb4972")
}

princecharmx · 2018-03-03T14:38:34Z

You have incorrect model. You are having property customer and also have a relation customers with hasOne.
You should use either.

Also, as pointed note, your customer.id is stored as string and hence you are having problem.
Add strictObjectIDCoercion to true in above model.

  "options": {
    "validateUpsert": true,
    "strictObjectIDCoercion": true
  }

Fyi, your usage is incorrect at many places. First you confirm if my suggestion works. I will then guide you next steps.

elviocb · 2018-03-04T00:42:29Z

Hey @princecharmx .. It works. Thanks!.. Could you guide me which other parts of the model are incorrect please?

kurtommy · 2018-05-16T15:02:49Z

I can't see valid solution for MYSQL inner join yet, has anyone found at least a workaround?

jolivervidal · 2018-05-17T11:05:15Z

I've made a fork of loopback-connector-mysql to support joins. It's not an ideal solution but it works, I'm using it in production in my applications. It only supports read operations.

This is a filter working over the User built-in model, to find all users with an ADMIN name role (the relation between User and Role is needed):

{"where": {"roles": {"name": "ADMIN" }}}

You can check it out https://github.com/jolivervidal/loopback-connector-mysql

kurtommy · 2018-05-17T21:32:40Z

@jolivervidal just tested and that work perfectly !!! you save me a lot of time, why this is not included in the standard library? i'll check what you have done in your fork!

bellgetlinks · 2018-06-17T22:58:57Z

Right now we have a workaround solution for this case ? :( for mongoDB

jackrvaughan · 2018-08-24T12:12:31Z

@bellgetlinks For mongodb I've ended up opening a connection to mongodb in a boot script and running direct queries and filters on related models using .aggregate and $lookup through remote methods. Essentially sidestepping loopback.

I found this stack overflow post helpful for including related documents in queries.

This is the boot script I used to connect to mongodb and expose it as mongoDB for use in the models:


const MongoClient = require('mongodb').MongoClient;
// Connection URL
const url = '< mongodb connection URL>';
// Database Name
const dbName = '< db name >';

module.exports = function connectToMongoDB(server) {
    // Use connect method to connect to the server
    MongoClient.connect(url).then(client => {
        console.log("Connected successfully to server");
        const db = client.db(dbName);
        server.mongoDB = db
    })
};

ranggarifqi · 2018-10-01T09:21:01Z

I've made a fork of loopback-connector-mysql to support joins. It's not an ideal solution but it works, I'm using it in production in my applications. It only supports read operations.

This is a filter working over the User built-in model, to find all users with an ADMIN name role (the relation between User and Role is needed):

{"where": {"roles": {"name": "ADMIN" }}}

You can check it out https://github.com/jolivervidal/loopback-connector-mysql

@jolivervidal
Thanks dude, it works
I hope it won't create new errors on another things though
how about creating a PR on the official module about this feature you just made ? 😃

michelgokan · 2019-03-03T16:52:32Z

What's the status of this issue? Seems a very important feature is missing. @jolivervidal your fork seems working very well, why not merging with the master branch?

zbarbuto · 2019-03-03T22:43:40Z

@michelgokan as you'll see from the thread:

There is some support in postgres connector
Mongo also has some basic support (only for dot nested properties)
Further support isn't going to be added to other RDB connectors as loopback 3 is in LTS and no longer accepting new feature PRs
Loopback 3 ends LTS at the end of 2019 so it's even less likely to be added.

Options are:

Write the custom SQL yourself as a custom endpoint
Create an alternate connector for something like Knex or Bookshelf that has support (big job)
Do the filtering in memory
Use json for the nested data to use dot queries in something like mongo or postgres
Look at migrating to loopback next which may add support in the future

michelgokan · 2019-03-04T20:00:06Z

Thanks, @zbarbuto but none of your mentioned alternatives works, at least for me at the moment: 1. I can’t write custom SQL scripts/custom endpoints as it ruins the whole architecture of my application (but maybe the most usable solution at the moment!!!!!) 2. For the same reason, it's not possible to use Bookshelf or Knex 3. Then we should basically ignore the whole idea behind using relational DBs: to be able to perform complex queries!! 4. Same as 3 5. This feature is not working even in Loopback 4, so can't see a point for migration. I didn't find a problem in @jolivervidal fork at the moment. I hope nothing bad happens 😬

…

On Sun, 3 Mar 2019 at 23:43, zbarbuto ***@***.***> wrote: @michelgokan <https://github.com/michelgokan> as you'll see from the thread: - There is some support in postgres connector <#517 (comment)> - Mongo also has some basic support (only for dot nested properties) - Further support isn't going to be added to other RDB connectors as loopback 3 is in LTS and no longer accepting new feature PRs - Loopback 3 ends LTS at the end of 2019 so it's even less likely to be added. Options are: - Write the custom SQL yourself as a custom endpoint - Create an alternate connector for something like Knex <https://knexjs.org/> or Bookshelf <https://bookshelfjs.org/> that has support (big job) - Do the filtering in memory - Use json for the nested data to use dot queries in something like mongo or postgres - Look at migrating to loopback next which may add support in the future — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#517 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AARH6W9n5vY5vqmX9fyotI6573Hl__nfks5vTFArgaJpZM4Ccngj> .

jeff3yan · 2019-03-04T20:10:33Z

Does anyone know if the loopback datasource is compatible with the knex connector? It'd be great to use the same connections for both, to migrate to knex if possible. Then wrap around something like objection.js for filtering on nested relations.

zbarbuto · 2019-03-04T22:34:48Z

Does anyone know if the loopback datasource is compatible with the knex connector?

@jeff3yan You'd have to write your own loopback-knex-connector. I've tried a few times but it's a big job and never really got anywhere with it. Having said that it would be an amazingly useful took if someone did manage to crack it. Particularly since join style queries (include) are pretty inefficient in loopback.

@michelgokan if that fork is working for you with MySQL then sounds like a good solution. I agree it's an unfortunate situation for users of other RDBs (like Postgres) but it's just the way loopback works - it does not assume your models are in any particular datasource so it does not leverage the full power of the underlying datasource (you may be querying two models in postgres or one might be in postgres and one might be in Mongo - it makes no assumptions)

vanderdill · 2020-03-03T15:05:10Z

@tellex solution works very well for PostgreSQL. Thank you so much!
I've tested many scenarios, with where conditions and order/limit situations and it is exactly what I needed.

Code and instructions:
https://github.com/tellex/findComplex

alexdee2007 · 2020-03-21T19:19:01Z

#683 (comment)

vanderdill · 2020-05-07T12:05:34Z

For those who are using PostgreSQL I'm using @tellex solution, and it's working greate on a large project.
I've done some bug fixes and I'm keeping this fork updated with it:
https://github.com/vanderdill/findComplex

bajtos added #review and removed #review labels Sep 6, 2014

bajtos mentioned this issue Dec 4, 2014

Include with filters REST URL #864

Closed

bajtos mentioned this issue Jan 20, 2015

Filter date on nested document strongloop/loopback-sdk-angular#123

Closed

bajtos mentioned this issue Feb 23, 2015

Ability to query nested documents/objects/arrays #953

Closed

bajtos added the enhancement label Feb 23, 2015

bajtos added this to the #Epic: Metrics milestone Feb 23, 2015

bajtos added the #tob label Feb 23, 2015

Sturgelose mentioned this issue Feb 7, 2018

[Feature] Filter on level 2 propierties loopbackio/loopback-connector-mysql#348

Closed

Sturgelose mentioned this issue Mar 29, 2018

[Feature] Filter in level 2 properties loopbackio/loopback-next#1210

Closed

jeff3yan mentioned this issue Jul 19, 2018

How to filter a model by a submodel using where? #3897

Closed

luisfavila mentioned this issue Jan 20, 2019

Coerce nested properties loopbackio/loopback-datasource-juggler#1684

Closed

2 tasks

bajtos mentioned this issue Aug 24, 2020

Unable to include nested object properties in fields and in where condition in lb4 loopbackio/loopback-next#5244

Open

Filter on level 2 properties #517

Filter on level 2 properties #517

Comments

daankets commented Aug 29, 2014 • edited by kjdelisle Loading

fabien commented Aug 29, 2014

daankets commented Aug 29, 2014

fabien commented Aug 29, 2014

daankets commented Aug 29, 2014

fabien commented Aug 29, 2014

daankets commented Aug 29, 2014

fabien commented Aug 29, 2014

daankets commented Aug 29, 2014

fabien commented Aug 29, 2014

raymondfeng commented Aug 29, 2014

fabien commented Aug 30, 2014

bajtos commented Oct 23, 2014

yagobski commented Nov 24, 2014

stringbeans commented Dec 19, 2014

weslley39 commented Jan 20, 2015

GameScripting commented Jan 22, 2015

dominikriedel commented Jan 27, 2015

bajtos commented Jan 28, 2015

dominikriedel commented Jan 28, 2015

pungoyal commented Feb 27, 2015

bajtos commented Feb 27, 2015

pungoyal commented Feb 27, 2015

kurtommy commented Dec 30, 2017

F3L1X79 commented Feb 2, 2018

nVitius commented Feb 2, 2018

acrodrig commented Feb 2, 2018

nVitius commented Feb 2, 2018

zbarbuto commented Feb 3, 2018

elviocb commented Mar 3, 2018 • edited Loading

princecharmx commented Mar 3, 2018 • edited Loading

elviocb commented Mar 3, 2018

princecharmx commented Mar 3, 2018 • edited Loading

elviocb commented Mar 4, 2018

kurtommy commented May 16, 2018

jolivervidal commented May 17, 2018

kurtommy commented May 17, 2018

bellgetlinks commented Jun 17, 2018

jackrvaughan commented Aug 24, 2018 • edited Loading

ranggarifqi commented Oct 1, 2018 • edited Loading

michelgokan commented Mar 3, 2019

zbarbuto commented Mar 3, 2019

michelgokan commented Mar 4, 2019 via email • edited Loading

jeff3yan commented Mar 4, 2019

zbarbuto commented Mar 4, 2019 • edited Loading

vanderdill commented Mar 3, 2020

alexdee2007 commented Mar 21, 2020

vanderdill commented May 7, 2020

daankets commented Aug 29, 2014 •

edited by kjdelisle

Loading

elviocb commented Mar 3, 2018 •

edited

Loading

princecharmx commented Mar 3, 2018 •

edited

Loading

princecharmx commented Mar 3, 2018 •

edited

Loading

jackrvaughan commented Aug 24, 2018 •

edited

Loading

ranggarifqi commented Oct 1, 2018 •

edited

Loading

michelgokan commented Mar 4, 2019 via email •

edited

Loading

zbarbuto commented Mar 4, 2019 •

edited

Loading