Loopback: Filter by properties of related models (use SQL JOIN in queries)

[bluestaralone]

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]

See https://github.com/strongloop/loopback-datasource-juggler/issues/730 for a more detailed description of the requested feature.

[princecharmx]

+1

[AlexYegupov]

+1

[codyolsen]

+1 Hope this gets accepted soon

[reneolivo]

+1 please! \0/

[ericalves]

+1 please!

[shuckepco]

This is definitely a "must have"!

[pierissimo]

+1

[bostondv]

+1

[rusackas]

+1

[Simone-cogno]

+1

[jeff3yan]

+1

[bajtos]

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.

[mull]

For those of us arriving to this issue late, where is the appropriate place to follow progress on this feature? It looks to me like it is indeed planned.

[sneakersgames]

+1 Can't believe this isn't included in the framework!

[siyb]

Hi, I was just wondering if there is an ETA on this issue and if there is any suggested workaround other than using bidirectional relationships?

[Amir-61]

I heard from @kjdelisle this feature is not possible unfortunately based on the currect architecture. @kjdelisle or @strongloop/sq-lb-apex could you please mention a brief note of the reasons why this feature is not possible and whether this will be available for LoopBack.Next for the people who are giving thumps up and waiting for this feature and then close it? Thanks!

CC: @bajtos

[kjdelisle]

The problem is that this would require us to create a complete SQL ORM that handles multiple different SQL databases and their nuances, which has already been done in the form of things like Sequelize. Re-inventing this for our own DSL would be prohibitively complex given our current resources.

[email protected] will be easier to integrate into ORMs, frameworks, or libraries of all kinds.

Unfortunately, as a workaround for now, you'll have to build the queries yourself using the existing ParameterizedSQL functionality (to prevent SQL injection): https://apidocs.strongloop.com/loopback-connector/v/2.0.1/#using-loopback-connector

[vidhema]

I am starting a project in loopback .. this is major drawback if we can not make where conditions for related models . This is not a feature missing .. this is issue

[vanderdill]

indeed, it`s very hard to write a non trivial application with good perfomance and clear code without this functionality. I think this should be seriously reconsidered if you plan to turn loopback in a robust framework for big applications.

[mdali602]

I also have same issue. I have a very bad experience with loopback. This is a major issue should be fixed asap. :(

[kurtommy]

@bajtos I'm looking at other relalted issues but there is not a solution for Mysql, this is very fundamental behaviour, we can filter the results in the UI (left join) we need a proper inner join.

Is there a valid workaround for this?

[bajtos]

@kurtommy I am afraid the only workaround is to manually build a native SQL query and execute it from a custom model method, see

• https://loopback.io/doc/en/lb3/Executing-native-SQL.html
• https://loopback.io/doc/en/lb3/Remote-methods.html

[kurtommy]

@bajtos i'm wondering how the community can use this framework without the basic join
i have a generic library and several related entities like songs streams playlist
i need to implement a UI filter where the user can type the name of the song so i need an inner join between the library and the song
ex:

const filters = {
      where: { type: 'Song' },
      include: {
        relation: 'song',
        scope: {
          where: { name: 'Mutt' } 
        }
      }
    };

but this works as left join. I can filter the library items without a song attached but this break the pagination and is very bad for the unwanted payload and performance

you are pointing to this one:
strongloop/loopback-datasource-juggler#730
how to use that?

[barocsi]

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]

+1

[cantoute]

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

[kurtommy]

@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]

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]

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

Thanks!

[bkstorm]

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]

Hi,

Seams that the best way is to cast you vote here
https://github.com/strongloop/loopback-next/issues/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 [e-mail] 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 https://github.com/strongloop/loopback/issues/683#issuecomment-434979680, or mute the thread https://github.com/notifications/unsubscribe-auth/AD42peEIKFFTLhlIivQdRbUAbxJt3jpoks5uqrtcgaJpZM4Cx_lQ.

[pookdeveloper]

This is not resolved yet? Thanks

[pookdeveloper]

if this is not resolved:

https://github.com/strongloop/loopback/issues/683

Why in the docu are this ?

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

Thanks

[bajtos]

@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]

Is Loopback 4 supporting this now?

[vncdias]

Is there any update on this?

[Rayjax]

This is completely insane.

[pookdeveloper]

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

[afilp]

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

[pookdeveloper]

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

I use combined

[afilp]

@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]

I will try today to do a tutorial

[melochale]

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

[pookdeveloper]

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

[pookdeveloper]

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

[alexdee2007]

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]

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?