Why does this nested relationship in LoopBack return duplicate results?

Question

When I request to include a nested model - for example, GET /api/Widgets/1?filter={include: {"foos": "bars"}} - I get a duplicate foos in my results. I thought it was due to LEFT JOIN or something like that since I use MySQL, but when I run LoopBack in debug mode loopback:connector:mysql , I see that the request for the initial widget works once, but the request for foo it is executed twice, and the query for the line is run twice. Why is this behavior happening, and what can I change (my models, my code, or my expectations)?

Models:

 { "name": "Widget", ... "relations": { "foos": { "type": "hasMany", "model": "Foo", "foreignKey": "widgetId" } } } { "name": "Foo", ... "relations": { "bars": { "type": "hasMany", "model": "Bar", "foreignKey": "fooId" }, "widget": { "type": "belongsTo", "model": "Widget", "foreignKey": "" } } } { "name": "Bar" ... "relations": { "foo": { "type": "belongsTo", "model": "Foo", "foreignKey": "" } } } 

Results:

 { id: 1 foos: [ { id: 2, bars: [ { id: 3 } ] }, { id: 2, bars: [ { id: 3 } ] } ] } 

Pending:

 { id: 1 foos: [ { id: 2, bars: [ { id: 3 } ] } ] } 

This is paraphrased SQL, which, as I see it, is executed for this query:

 SELECT `...` FROM `Widget` WHERE `id`=1 ORDER BY `id` LIMIT 1 SELECT `...` FROM `Foo` WHERE `widget_id` IN (1) ORDER BY `id` SELECT `...` FROM `Foo` WHERE `widget_id` IN (1) ORDER BY `id` SELECT `...` FROM `Bar` WHERE `foo_id` IN (2) ORDER BY `id` SELECT `...` FROM `Bar` WHERE `foo_id` IN (2) ORDER BY `id` 

I am using Loopback 3.x.

Update: While the request GET /api/Widgets/1?filter={include: {"foos": "bars"}} demonstrates this behavior, executing Widgets.findById(id, {include: {"foos": "bars"}}) server side works fine. So, for now, I will create a remote method that does this, and possibly an error message file with LoopBack.

Answer 1

I used this mixin, which limits the query limit to the maximum at a certain value. When include present in the request, mixin also sets a scope limit for include as follows:

"include": {"foo":"bar","scope":{"limit":1}}

The mixin seems to have assumed that all inclusions that are objects will be written as {"relation":"foo", "scope":{"include:"bars"}} , so {"relation":"foo", "scope":{"include:"bars"}} are added twice.

Whatever the cost, I wrote this simple mixin to limit the maximum number of results if not specified and stopped using the one above:

generic / model / model.json:

 "mixins": { "ResultsetLimit": { "limit": 100 } } 

General / Impurities /ResultSet-limit.js:

 const _ = require('lodash'); module.exports = (Model, options) => { /** * Modify incoming query to apply appropriate limit filters. */ Model.beforeRemote('**', (ctx, unused, next) => { // Get the limit from the request, defaulting to the max limit. const request_limit = _.toNumber(_.get(ctx, 'args.filter.limit', options.limit)); // Set the limit. _.set(ctx, 'args.filter.limit', request_limit); next(); }); }; 

Answer 2

Have you tried to delete the following lines? Since by default, if foreignKey NOT set, it sets it as <relationName>Id . But since you set it to empty, loopback does not look for any column for the link. therefore, it gets all the records for your related model.

 { "name": "Widget", ... "relations": { "foos": { "type": "hasMany", "model": "Foo", "foreignKey": "widgetId" } } } { "name": "Foo", ... "relations": { "bars": { "type": "hasMany", "model": "Bar", "foreignKey": "fooId" }, "widget": { "type": "belongsTo", "model": "Widget", "foreignKey": "" // remove this } } } { "name": "Bar" ... "relations": { "foo": { "type": "belongsTo", "model": "Foo", "foreignKey": "" //remove this } } } 

UPDATE:

This is what I call the relationship of the 2nd (or 3rd) level:

 /api/Widgets/1?filter={include: [{"relation":"foo", "scope":{"include:"bars"}}]}