Why is this nested relation in LoopBack returning duplicate results?

Question

When I query to include a nested model – e.g. GET /api/Widgets/1?filter={include: {"foos": "bars"}} – I get duplicate foos in my results. I thought this was due to a LEFT JOIN or something like that, as I'm using MySQL, but when I run LoopBack in the loopback:connector:mysql debug mode, I can see that the query for the initial widget runs once, but that the query for foo runs twice, and the query for bar runs twice. Why is this behavior occurring, and what can I alter (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
        }
      ]
    }
  ]
}

Expecting:

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

This is paraphrased SQL that I see being run for this request:

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'm using Loopback 3.x.

Update: While a request of GET /api/Widgets/1?filter={include: {"foos": "bars"}} exhibits this behavior, a server-side execution of Widgets.findById(id, {include: {"foos": "bars"}}) works perfectly. So, at the moment I'll create a remote method that does this and perhaps file a bug report with LoopBack.

Answer 1

I was using this mixin that limits the limit of a query to max out at a defined value. When include is present in a query, the mixin also sets a limit on the scope of the include like so:

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

Seems the mixin was assuming all includes that are objects would be written in the form of {"relation":"foo", "scope":{"include:"bars"}}, so includes were getting added twice.

For what it's worth, I wrote this simple mixin to limit the maximum number of results unless specified and stopped using the one linked above:

common/models/model.json:

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

common/mixins/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();

    });

};