Loopback: hasMany through returns duplicate records

[PaddyMann]

Bug or feature request

• [x] Bug
• [ ] Feature request

Description of feature (or steps to reproduce if bug)

Model one config (organization.json):

"relations": {
  ...
  "users": {"type": "hasMany", "model": "OrganizationUser", "foreignKey": "organizationId", "through": "UserOrganizationRole"}
},

Model two config (organization-user.json):

"relations": {
  "organizations": {"type": "hasMany", "model": "Organization", "foreignKey": "userId", "through": "UserOrganizationRole"},
  ...
},

Connecting model (user-organization-role.json):

  "relations": {
    "organization": {"type": "belongsTo", "model": "Organization", "foreignKey": "organizationId"},
    "organizationUser": {"type": "belongsTo", "model": "OrganizationUser", "foreignKey": "userId"}
  },

Data:

Organization 1
User 1 is in organization 1 with role administrator
User 2 is in organization 1 with role projectAdministrator
User 2 is in organization 1 with role accountant

Call: GET /organizations/1/users

Link to sample repo to reproduce issue (if bug)

Expected result

Expected result:

[
  {
    "id": 1,
    "name": "User 1"
  },
  {
    "id": 2,
    "name": "User 2"
  }
]

Actual result (if bug)

[
  {
    "id": 1,
    "name": "User 1"
  },
  {
    "id": 2,
    "name": "User 2"
  },
  {
    "id": 2,
    "name": "User 2"
  }
]

The second user is duplicated due to there being 2 records connecting the organization to the user.

The SQL output is simply:

loopback:connector:mysql SQL: SELECT `id`,`userId`,`organizationId`,`roleName`,`deleted`,`createdBy`,`updatedBy` FROM `UserOrganizationRole` WHERE (`organizationId`=?) AND (`deleted`=?) ORDER BY `id`, params: [1,0] +7ms

I believe that this should - by default - only return unique records. Some googling suggests that this isn't always true with other frameworks: Rails requires that you set a unique flag on the relationship definition.

I can't see anything in the Loopback documentation on how to support this. Have I missed something?

If not, any suggestions for a quick fix? It's important that pagination works as expected.

Additional information (Node.js version, LoopBack version, etc)

[jannyHou]

@PaddyMann I am trying to reproduce your dup records but appreciate some details:
Is this bug related to "ROLE"? Since you mentioned instances' roles in:

User 1 is in organization 1 with role administrator
User 2 is in organization 1 with role projectAdministrator
User 2 is in organization 1 with role accountant

Can I simply reproduce it with a hasManyThrough relation without touching any role definition?

[PaddyMann]

@jannyHou This relates to our own schema:

• User is connected to Organization through UserOrganizationRole.
• UserOrganizationRole has columns userId, organizationId, roleName

It doesn't relate to roles within Loopback. You could replace it with an example of Patients, Doctors and Appointments if that makes life less confusing :)

(The important bit here is that we're not just using this to map a patient to multiple doctors, but also to the same doctor multiple times.)

[jannyHou]

@PaddyMann Confirmed it's a bug.

[jannyHou]

A thought: I am trying to read the implementation of function modelFrom.prototype['__findById__' + relationName], which maps that endpoint: https://github.com/strongloop/loopback/blob/master/lib/model.js#L529

Actually in javascript code, function organization.users() will fetch users with uniq ids:
https://github.com/strongloop/loopback-datasource-juggler/blob/master/lib/scope.js#L118
// function collectTargetIds removes dup id.

Weird that it doesn't work in modelFrom.prototype['__findById__' + relationName]

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

[amismailz]

Same issue here. Is there an estimate to solve this?

[charuruflex]

Same issue here too...

[jgfrancisco]

same with me

[PaddyMann]

@jgfrancisco I highly recommend _not_ using Loopback for your ORM. Loopback 3 has numerous serious bugs that were never resolved by the team.

If you're using an SQL database, I can recommend sequelize as an alternative.

[ColineDoe]

Is this issue going to be solved one day?