Filter by properties of related models (use SQL JOIN in queries) #683

bluestaralone · 2014-10-23T02:55:48Z

Ex: I have models Book and Category and i want to query Book with condition like below:
Books with title contain "Art" and belongs to Category "Programming", please tell me how to do it ?
I run this filter in Books model.
GET /Books

{"include":{"categories"}, "where": {"title":{"like":"^Art"}, "categories.name":"Programming"}}

bajtos · 2016-06-02T11:56:29Z

See loopbackio/loopback-datasource-juggler#730 for a more detailed description of the requested feature.

princecharmx · 2016-08-02T15:33:43Z

+1

AlexYegupov · 2016-08-16T08:16:13Z

+1

codyolsen · 2016-08-16T18:00:21Z

+1 Hope this gets accepted soon

reneolivo · 2016-08-24T01:24:44Z

+1 please! \0/

ericalves · 2016-09-03T01:11:42Z

+1 please!

shuckepco · 2016-09-27T11:29:04Z

This is definitely a "must have"!

pierissimo · 2016-10-13T07:53:00Z

+1

bostondv · 2016-10-20T17:26:56Z

+1

rusackas · 2016-10-25T18:12:27Z

+1

Simone-cogno · 2016-10-27T22:16:18Z

+1

jeff3yan · 2016-10-30T22:28:08Z

+1

bajtos · 2016-11-02T21:59:18Z

Please, don't spam everybody on this thread by posting +1 comments. Click on the "thumb up" icon at the top. It will also allow us to sort issues based on the number of upvotes.

barocsi · 2018-06-18T13:26:51Z

Boy, its broken at the core just as many "open source" framework, which itself is an illusion. These guys are obviously building apps for clients and not for community. We are good to be testers, pull requesters, fixers, but left fairly behind if it comes to an issue that is not in the interest of loopback main clients.

Andresagustin · 2018-06-25T23:19:16Z

+1

cantoute · 2018-06-30T22:33:38Z

Is this going to be implemented on lb4 ?
seems unreal at this level to be stuck with things as basic as that.

kurtommy · 2018-07-01T06:49:26Z

@barocsi well a lot of time the trigger to build huge open source libs is: some company needs and build a framework and then is released publicly eg Angular React ....
I believe that the lack of doing SQL Join is a major / huge problems that also the ibm customers will face.

So as @cantoute is saying this situation is absolutely unreal.

I'd like to mention @bajtos as maintainer to ask him if there are some updates on this topic

bajtos · 2018-07-02T14:10:22Z

I don't have any update to share. Our focus is on delivering LoopBack 4.0 GA, we don't have bandwidth to work on this feature until 4.0 is released.

dionisisZyg · 2018-10-31T16:18:42Z

@bajtos Sorry to bother you, but since the Loopback 4.0 is released is there anything planed for this feature?

Thanks!

bkstorm · 2018-11-01T09:13:49Z

I can't believe that Loopback doesn't support this basic feature. Does Loopback 4.0 support it?
To resolve this problem, I have to use native sql.

cantoute · 2018-11-01T18:08:28Z

Hi, Seams that the best way is to cast you vote here loopbackio/loopback-next#512

Left and Inner Joins on queries. Perhaps just use GraphQL with something like Join Monster <https://github.com/stems/join-monster> to generate smart SQL behind the scenes.

this feature doesn't seem to be demanded... while to me it seams like an essential and basic need for database driven apps...

…

Le 1 nov. 2018 à 06:14, Nguyen Hoang ***@***.***> a écrit : I can't believe that Loopback doesn't support this basic feature. Does Loopback 4.0 support it? To resolve this problem, I have to use native sql. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#683 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AD42peEIKFFTLhlIivQdRbUAbxJt3jpoks5uqrtcgaJpZM4Cx_lQ>.

pookdeveloper · 2018-12-02T18:09:15Z

This is not resolved yet? Thanks

pookdeveloper · 2018-12-03T15:01:56Z

if this is not resolved:

#683

Why in the docu are this ?

https://loopback.io/doc/en/lb3/Include-filter.html

Thanks

bajtos · 2018-12-03T15:11:37Z

@pookdeveloper IIUC, the condition specified in include.scope.where is applied only to related models being fetched. This issue is asking for a different feature: filter the source models based on properties of the related model.

What does not work:

Books with title contain "Art" and belongs to Category "Programming"

What may work using include.scope.where:

Books with title containing "Art", include data of all related categories with the name "Programming".

I hope my example is making the difference clear.

afilp · 2019-04-14T09:42:10Z

Is Loopback 4 supporting this now?

vncdias · 2019-05-03T00:04:10Z

Is there any update on this?

Rayjax · 2019-05-10T10:41:11Z

This is completely insane.

pookdeveloper · 2019-05-10T10:53:49Z

I can do that with typeorm https://typeorm.io/#/

afilp · 2019-05-10T11:28:13Z

@pookdeveloper You mean that you use typeorm as a total replacement of loopback? Or combined?

pookdeveloper · 2019-05-10T11:31:02Z

@pookdeveloper You mean that you use typeorm as a total replacement of loopback? Or combined?

I use combined

afilp · 2019-05-11T07:38:29Z

@pookdeveloper Thank you, is somewhere a tutorial on how we can setup this solution? It seems that this can solve many problems that loopback has not addressed yet.

pookdeveloper · 2019-05-11T08:42:40Z

I will try today to do a tutorial

melochale · 2019-05-11T11:28:36Z

@pookdeveloper could you please give us an example? thanks very much!

pookdeveloper · 2019-05-12T10:13:53Z

@melote @afilp i'm writting a tutorial, i will send you the link

pookdeveloper · 2019-05-12T10:50:37Z

https://medium.com/@davidgarciasantes/typeorm-with-loopback-3-68b195cbe6de?source=friends_link&sk=56276d0cb3a319ef96d2996247b7d3f6

alexdee2007 · 2020-03-21T19:02:45Z

It's not hard to just override the _buildWhere method at boot time.
For example (i use Oracle DB)

const set = require('lodash/set');

module.exports = function (app) {
  const connector = app.dataSources.db.connector;
  const _buildWhere = connector._buildWhere.bind(connector);

  connector._buildWhere = function (model, where) {

    for (var key in where) {
      if (key.includes('.')) {
        Object.assign(where, set({}, key, where[key]));
        delete where[key];
      }
    }

    let whereClause = _buildWhere(model, where);
    const relations = app.models[model].relations;

    for (const key in where) {
      if (relations && relations[key]) {
        const relation = relations[key];
        const inSelect = this.buildSelect(relation.modelTo.modelName, {
          fields: [relation.keyTo],
          where: {
            and: [
              app.models[model]._coerce(where[key], {}, app.models[relation.modelTo.modelName].definition)
            ]
          }
        }, {parameterize: false, order: false});
        whereClause = new ParameterizedSQL({
          sql: whereClause.sql + (whereClause.sql !== '' ? ' AND ' : '') + this.columnEscaped(relation.modelFrom.modelName, relation.keyFrom) + ' IN (' + inSelect.sql + ')',
          params: whereClause.params.concat(inSelect.params),
        });
      }
    }
    return whereClause;
  }
}

And then
GET /Books

{"include":{"categories"}, "where": {"title":{"like":"^Art"}, "categories.name":"Programming"}}

or just

{"where": {"categories.name":"Programming"}}

or even

{"where": {"models1.models2.models3.field":"someValue"}}

{"where":{"models1":{"and":[{"someField1":"someValue1"},{"someField2":"someValue2"}]}}}

mituso89 · 2020-10-01T09:34:35Z

It's not hard to just override the _buildWhere method at boot time.
For example (i use Oracle DB)

const set = require('lodash/set');

module.exports = function (app) {
  const connector = app.dataSources.db.connector;
  const _buildWhere = connector._buildWhere.bind(connector);

  connector._buildWhere = function (model, where) {

    for (var key in where) {
      if (key.includes('.')) {
        Object.assign(where, set({}, key, where[key]));
        delete where[key];
      }
    }

    let whereClause = _buildWhere(model, where);
    const relations = app.models[model].relations;

    for (const key in where) {
      if (relations && relations[key]) {
        const relation = relations[key];
        const inSelect = this.buildSelect(relation.modelTo.modelName, {
          fields: [relation.keyTo],
          where: {
            and: [
              app.models[model]._coerce(where[key], {}, app.models[relation.modelTo.modelName].definition)
            ]
          }
        }, {parameterize: false, order: false});
        whereClause = new ParameterizedSQL({
          sql: whereClause.sql + (whereClause.sql !== '' ? ' AND ' : '') + this.columnEscaped(relation.modelFrom.modelName, relation.keyFrom) + ' IN (' + inSelect.sql + ')',
          params: whereClause.params.concat(inSelect.params),
        });
      }
    }
    return whereClause;
  }
}

And then
GET /Books

{"include":{"categories"}, "where": {"title":{"like":"^Art"}, "categories.name":"Programming"}}

or just

{"where": {"categories.name":"Programming"}}

or even

{"where": {"models1.models2.models3.field":"someValue"}}

{"where":{"models1":{"and":[{"someField1":"someValue1"},{"someField2":"someValue2"}]}}}

How to use it in loopback4?

enisinanaj · 2021-11-16T12:35:06Z

It's not hard to just override the _buildWhere method at boot time.
For example (i use Oracle DB)

const set = require('lodash/set');

module.exports = function (app) {
  const connector = app.dataSources.db.connector;
  const _buildWhere = connector._buildWhere.bind(connector);

  connector._buildWhere = function (model, where) {

    for (var key in where) {
      if (key.includes('.')) {
        Object.assign(where, set({}, key, where[key]));
        delete where[key];
      }
    }

    let whereClause = _buildWhere(model, where);
    const relations = app.models[model].relations;

    for (const key in where) {
      if (relations && relations[key]) {
        const relation = relations[key];
        const inSelect = this.buildSelect(relation.modelTo.modelName, {
          fields: [relation.keyTo],
          where: {
            and: [
              app.models[model]._coerce(where[key], {}, app.models[relation.modelTo.modelName].definition)
            ]
          }
        }, {parameterize: false, order: false});
        whereClause = new ParameterizedSQL({
          sql: whereClause.sql + (whereClause.sql !== '' ? ' AND ' : '') + this.columnEscaped(relation.modelFrom.modelName, relation.keyFrom) + ' IN (' + inSelect.sql + ')',
          params: whereClause.params.concat(inSelect.params),
        });
      }
    }
    return whereClause;
  }
}

And then
GET /Books

{"include":{"categories"}, "where": {"title":{"like":"^Art"}, "categories.name":"Programming"}}

or just

{"where": {"categories.name":"Programming"}}

or even

{"where": {"models1.models2.models3.field":"someValue"}}

{"where":{"models1":{"and":[{"someField1":"someValue1"},{"someField2":"someValue2"}]}}}

How to use it in loopback4?

where does ParameterizedSQL come from?

alexdee2007 · 2021-11-16T13:42:25Z

@enisinanaj

const ParameterizedSQL = require('loopback-connector/lib/parameterized-sql');

cantoute · 2021-11-16T16:37:37Z

Or perhaps the way to go is switch to typeORM ....

But at this time support for it in lb is at useless... nestjs ?

pandayamitray · 2022-01-22T15:18:07Z

8 years and counting. Still, this basic feature is not there. If I would have known earlier about this, I would have not chosen LB4

cantoute · 2022-01-22T16:37:17Z

I switched to nestjs + typeorm and no regrets

bajtos mentioned this issue Oct 23, 2014

Filter on level 2 properties #517

Closed

bajtos mentioned this issue Dec 4, 2014

Include with filters REST URL #864

Closed

superkhau added question feature labels Feb 26, 2015

bajtos mentioned this issue Jun 2, 2016

filter with related model's properties loopbackio/loopback-datasource-juggler#730

Closed

bajtos added major and removed question labels Jun 2, 2016

bajtos added the #tob label Sep 6, 2016

bajtos assigned Amir-61 Sep 6, 2016

bajtos changed the title ~~How can i set condition where on include model ?~~ Filter by properties of related models (use SQL JOIN in queries) Sep 6, 2016

Amir-61 removed the #tob label Sep 6, 2016

This was referenced Sep 16, 2016

Query on Included Model Properties loopbackio/loopback-datasource-juggler#1042

Closed

Support JOIN when using relational databases. loopbackio/loopback-datasource-juggler#1092

Closed

0candy mentioned this issue Oct 6, 2016

mysql filter where on relation #2834

Closed

Amir-61 added juggler-relations labels Oct 13, 2016

Amir-61 removed their assignment Oct 13, 2016

paeolo mentioned this issue Apr 17, 2020

Support for SQL JOIN (INNER JOIN) loopbackio/loopback-next#5132

Open

Filter by properties of related models (use SQL JOIN in queries) #683

Filter by properties of related models (use SQL JOIN in queries) #683

Comments

bluestaralone commented Oct 23, 2014

bajtos commented Jun 2, 2016

princecharmx commented Aug 2, 2016

AlexYegupov commented Aug 16, 2016

codyolsen commented Aug 16, 2016 • edited Loading

reneolivo commented Aug 24, 2016

ericalves commented Sep 3, 2016

shuckepco commented Sep 27, 2016

pierissimo commented Oct 13, 2016

bostondv commented Oct 20, 2016

rusackas commented Oct 25, 2016

Simone-cogno commented Oct 27, 2016

jeff3yan commented Oct 30, 2016

bajtos commented Nov 2, 2016

barocsi commented Jun 18, 2018 • edited Loading

Andresagustin commented Jun 25, 2018

cantoute commented Jun 30, 2018 • edited Loading

kurtommy commented Jul 1, 2018

bajtos commented Jul 2, 2018

dionisisZyg commented Oct 31, 2018

bkstorm commented Nov 1, 2018

cantoute commented Nov 1, 2018 via email

pookdeveloper commented Dec 2, 2018

pookdeveloper commented Dec 3, 2018 • edited Loading

bajtos commented Dec 3, 2018

afilp commented Apr 14, 2019

vncdias commented May 3, 2019

Rayjax commented May 10, 2019

pookdeveloper commented May 10, 2019

afilp commented May 10, 2019 • edited Loading

pookdeveloper commented May 10, 2019

afilp commented May 11, 2019

pookdeveloper commented May 11, 2019

melochale commented May 11, 2019

pookdeveloper commented May 12, 2019

pookdeveloper commented May 12, 2019

alexdee2007 commented Mar 21, 2020 • edited Loading

mituso89 commented Oct 1, 2020

enisinanaj commented Nov 16, 2021

alexdee2007 commented Nov 16, 2021

cantoute commented Nov 16, 2021 • edited Loading

pandayamitray commented Jan 22, 2022

cantoute commented Jan 22, 2022

codyolsen commented Aug 16, 2016 •

edited

Loading

barocsi commented Jun 18, 2018 •

edited

Loading

cantoute commented Jun 30, 2018 •

edited

Loading

pookdeveloper commented Dec 3, 2018 •

edited

Loading

afilp commented May 10, 2019 •

edited

Loading

alexdee2007 commented Mar 21, 2020 •

edited

Loading

cantoute commented Nov 16, 2021 •

edited

Loading