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.

Froh

If it works fine on server-side execution and you just get duplicates through the API calls, then maybe there is a problem with Loopback routes; a double route or some nested routes may causing this problem. – Pokorny

do you solve it? – Ornament

See my answer, @Casy, it may help point you in the right direction if you're encountering a similar issue. Thanks Christos, that helped me sort it out. – Froh

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();

    });

};

Froh

Answer 2

have you tried removing the following lines? Because by default, if foreignKey is NOT set, it will set it as <relationName>Id. But since you set it to blank, loopback is not looking for any column to reference to. hence it is getting all the records on 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 how I call 2nd (or 3rd) level relations:

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

Return

This does not seem to make a difference. The default foreignKey is calculated as relationName + 'Id' whether the line is empty or omitted. – Froh

updated my answer. Added how I call 3rd level relations. – Return

That's helpful, Mark. I believe the mixin I was using that caused this issue was assuming all multi-level relations were defined like this. – Froh