Inner join not available in loopback 4

Question

Associations are properly set & code is working but I am struggling with join type I want.

Below code is written to get only those addresses which have users having ids [260, 123].

const addresses = await this.addressRepository.find({
  include: [{
    relation: 'users',
    scope: {
      where: { id: { inq: [260, 123] } }
    }
  }]
})

But what I am getting is, all addresses with where filter applied only on users which works as left join.

Please help me to get query as per my need.

score 3 · Accepted Answer · answered Jul 18 '20 at 06:32

Currently, LB4 doesn't support SQL inner join (https://github.com/strongloop/loopback-next/issues/4299).

Examples with instances and a simple reference to what are the relations would help us understand what you are trying to accomplish, what's the current output and what's the expected output.

I guess that the relation is **Address -> hasMany -> User

Example Instances:

addresses = [{ id: 0 }, { id: 1 }, { id: 2 }, { id: 3 }];
users = [
  { id: 0, addressId: 0 },
  { id: 1, /* no address */ },
  { id: 2, addressId: 1 }, // same as id 260
  { id: 260, adressId: 1 },
  { id: 123, addressId:2 }
];

Expected Result:

[
  { id: 1, users: [{ id: 260 }] }
  { id: 2, users: [{ id: 123 }] },
]

Actual Result:

[
  { id: 0, users: [] } // filtered out user with id = 0
  { id: 1, users: [{ id: 260 }] } // filtered out user with id = 2
  { id: 2, users: [{ id: 123 }] },
  { id: 3, users: [] }
]

So your query doesn't filter Addresses based on relation users and their userId (or inner join), but instead queries parent model (Address) based on current 'where' filter (matches all) and justs filters out the users from the include relation.

Workaround 1

One workaround could be to also add a User -> BelongsTo -> Address relation and use the reverse query:

const users = await this.userRepository.find({
  where: {id: {inq: [260, 123]}},
  include: [{
    relation: 'address',
  }],
});
// now you only have to reduce the final users with their addresses into unique addresses.

Note: Don't forget to update the UserRelations as for belongsTo the relation is not automatically added:

export interface UserRelations {
  // describe navigational properties here
  address?: AddressWithRelations;
}

Workaround 2

And of course, another workaround is to keep the list of userIds inside the addresses and query based on that property. Obviously, the downside with this is that when you update the user you must also update the corresponding address.

I already preferred first workaround but nice explaination. I am from `rails` background and activerecord is much stronger than this. So I am not happy with lb4 so much. — ray, Jul 18 '20 at 17:15

score 1 · Answer 2 · answered Jul 22 '20 at 22:09

Hi if you are using only one postgres datasource for the concerned queries we are working on this fork : https://github.com/Wikodit/loopback-connector-postgresql

it allows inner join query like :

{
  "where": {
    "user.contact.email": "mail@domain.tld",
    "profile.name": "support"
  },
  "include": [
    {
      "relation": "user",
      "scope": {
        "include" : [
           {
             "relation": "contact"
           }
        ]
      }
    },
    {
      "relation": "profile"
    }
  ],
  "order": "user.contact.email ASC"
}

Inner join not available in loopback 4

2 Answers2