CODEWITHSUNDEEP
node.jstypescriptormloopback4
ray
ray

Reputation: 5552

Inner join not available in loopback 4

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.

Upvotes: 1

Views: 2226

Answers (2)

hessman
hessman

Reputation: 11

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": "[email protected]",
    "profile.name": "support"
  },
  "include": [
    {
      "relation": "user",
      "scope": {
        "include" : [
           {
             "relation": "contact"
           }
        ]
      }
    },
    {
      "relation": "profile"
    }
  ],
  "order": "user.contact.email ASC"
}

Upvotes: 1

Nikos Oikonomou
Nikos Oikonomou

Reputation: 196

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.

Upvotes: 3

Related Questions