7

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.

Charlie Schliesser
  • 7,851
  • 4
  • 46
  • 76
  • 1
    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](https://loopback.io/doc/en/lb3/Routing.html); a double route or some nested routes may causing this problem. – Christos Lytras Aug 17 '17 at 15:16
  • do you solve it? – MatCas Nov 10 '17 at 20:49
  • 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. – Charlie Schliesser Nov 17 '17 at 01:30

2 Answers2

2

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

    });

};
Charlie Schliesser
  • 7,851
  • 4
  • 46
  • 76
1

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"}}]}
Mark Ryan Orosa
  • 847
  • 1
  • 6
  • 21