Querying associated model with scope and limit

[geekguy]

Here is the test project: https://github.com/geekguy/loopback-sandbox (Details in readme)

Lets say we have 3 models, M1, M2 and M3 and instances are m1, m2 and m3.

M1 hasMany M2 and M2 hasMany M3.

Now. I want M2s for a given M1 and I want to include M3 with some condition.

m1.m2s({
  include: [
    {
      relation: 'm3s',
      scope: {
        where: {
          someCondition: something
        }
      }
    }
  ]
})

This returns all m2s and includes m3s with matching condition which is perfect.

Now I wan only one m3 per m2. So I ll add limit: 1

m1.m2s({
  include: [
    {
      relation: 'm3s',
      scope: {
        where: {
          someCondition: something,
          limit: 1
        }
      }
    }
  ]
})

But this call has some issues. It returns 1 m3 for first m2 and 0 for rest of them.
If I change limit: 2, it returns 1 m3 for first two m2 and 0 for rest of them.

Looks like scope condition is going wrong.

Please correct me if I missed anything or doing something wrong.

https://docs.strongloop.com/display/public/LB/Querying+related+models#Queryingrelatedmodels-Usingfiltersparameterswithincludedrelations

[superkhau]

Please post questions at https://groups.google.com/forum/#!forum/loopbackjs. See https://github.com/strongloop/loopback/wiki/Questions for more details.

[geekguy]

@superkhau : I ll do that, but this looks like an issue to me.

[superkhau]

Can you provide a link to a test project on GitHub? See https://github.com/strongloop/loopback/wiki/Reporting-issues#bug-report

[geekguy]

@superkhau : My bad. should have checked that.

Here is the test project: https://github.com/geekguy/loopback-sandbox

[Neil-UWA]

try to explicitly define keyThrough property

[geekguy]

@Neil-UWA : Not sure what you mean. Can you please elaborate?

[Neil-UWA]

@geekguy, sorry, I thought you're using has many through relation. I had this problem when defining has-many through relation without defining key through before.

[geekguy]

@superkhau : Did you get a chance to verify this?

[superkhau]

@geekguy Sorry not yet, been busy. However, I will find someone who can. ;)
@Amir-61 ^

[Amir-61]

Hi @geekguy,

Thanks for reporting this issue. It definitely seems a bug to me. Also, it is worth mentioning the following examples based on your models:

  M1.callWithLimit = function (id, callback) {
    M1.findOne({
      where: {id: id},
      include: [
        {
          relation: 'm2s',
          scope: {
            limit: 2, // This works
            include: {
              relation: 'm3s',
              scope: {
                where: {id: {inq: [2, 3, 4, 5, 6]}} // This works too
              }
            }
          },
        }
      ]
    }, callback);
  };

However:

  M1.callWithLimit = function (id, callback) {
    M1.findOne({
      where: {id: id},
      include: [
        {
          relation: 'm2s',
          scope: {
            limit: 2, // This works
            include: {
              relation: 'm3s',
              scope: {
                limit: 1 // This does not work properly!
              }
            }
          },
        }
      ]
    }, callback);
  };

I'll label it as a bug. Thanks for reporting it!

[jsheely]

Another similar issue to this with scope, limit

If you have a 200 model products and each has 1 related model "image" attached to it. And you put scope: limit: 100 on the images model. Then you query like this:

 Product.find({
    filter: {
        where: { groupId: groupId },
        include: {
            relation: 'images'
        },
    }
})

This will give you 200 products, but only the first 100 products will have the image attached. The next 200 will have an empty array.

[geekguy]

@jsheely : Yes. That's the exact issue.

[ghost]

The include filter seems to be all over the place. I am having some problems with it and embedded models. I am currently seeing if there are any solutions to my problem and if so I will open a new issue or possibly post back here.

[robinskumar73]

I am having the same issue. This seems to be a bug in limit in case of hasMany relationship.I think the ideal case would be to use limit within scope.
Suppose I have two models Wishlist and Item

Item ======>Wishlist (Each item hasMany Wishlist)
Wishlist =======> Item(Each wishlist hasMany items)

Now suppose i want to get Item of a particualr wishlist with limit=7 option I get only three value even i have more than three items present in Wishlist.

wishlistId = 56bdab621f386c1307f4389d

GET request : -

Wishlists/56bdab621f386c1307f4389d/item?filter[skip]=7&filter[limit]=7

[kjdelisle]

@loay Any updates on this task?

[loay]

Issue cannot be reproduced in Loopback 3 and neither in LB2
I used the same sandbox as provided by @geekguy in the first comment .. I could not reproduce in LB2, however, I created a LB3 app, with the same relation and same models and I ran the script and it worked fine and the m3s are not empty and they are all there and it worked with Both limit 1 and 2. I followed the same algorithm provided in the sandbox .. Please have a look and let me know if I have missed something ..

Loopback 2 app:
with Limit 1:

{"name":"Model - 1 - index 1","checkDefault":"Scope Condition String","id":1,"m2s":[{"name":"Model - 2 - index 1","id":1,"m1Id":1,"m3s":[{"name":"Model - 3 - index 1","id":1,"m2Id":1}]},{"name":"Model - 2 - index 2","id":2,"m1Id":1,"m3s":[{"name":"Model - 3 - index 4","id":4,"m2Id":2}]},{"name":"Model - 2 - index 3","id":3,"m1Id":1,"m3s":[{"name":"Model - 3 - index 7","id":7,"m2Id":3}]}]}

with limit 2:

{"name":"Model - 1 - index 2","checkDefault":"Scope Condition String","id":2,"m2s":[{"name":"Model - 2 - index 4","id":4,"m1Id":2,"m3s":[{"name":"Model - 3 - index 4","id":10,"m2Id":4}]},{"name":"Model - 2 - index 5","id":5,"m1Id":2,"m3s":[{"name":"Model - 3 - index 7","id":13,"m2Id":5}]},{"name":"Model - 2 - index 6","id":6,"m1Id":2,"m3s":[{"name":"Model - 3 - index 10","id":16,"m2Id":6}]}]}

Loopback 3 app:
with limit 1:

{"name":"Model - 1 - index 1","checkDefault":"Default String","id":1,"m2s":[{"name":"Model - 2 - index 1","checkDefault":"Default String","id":1,"m1Id":1,"m3s":[{"name":"Model - 3 - index 1","checkDefault":"Default String","id":1,"m2Id":1}]},{"name":"Model - 2 - index 2","checkDefault":"Default String","id":2,"m1Id":1,"m3s":[{"name":"Model - 3 - index 4","checkDefault":"Default String","id":4,"m2Id":2}]},{"name":"Model - 2 - index 3","checkDefault":"Default String","id":3,"m1Id":1,"m3s":[{"name":"Model - 3 - index 7","checkDefault":"Default String","id":7,"m2Id":3}]}]}

with Limit 2:

{"name":"Model - 1 - index 2","checkDefault":"Default String","id":2,"m2s":[{"name":"Model - 2 - index 4","checkDefault":"Default String","id":4,"m1Id":2,"m3s":[{"name":"Model - 3 - index 4","checkDefault":"Default String","id":10,"m2Id":4}]},{"name":"Model - 2 - index 5","checkDefault":"Default String","id":5,"m1Id":2,"m3s":[{"name":"Model - 3 - index 7","checkDefault":"Default String","id":13,"m2Id":5}]},{"name":"Model - 2 - index 6","checkDefault":"Default String","id":6,"m1Id":2,"m3s":[{"name":"Model - 3 - index 10","checkDefault":"Default String","id":16,"m2Id":6}]}]}

[kjdelisle]

Closing this issue. If you have a reproduction that can demonstrate the problem, please page @loay or myself (@kjdelisle) and post the repo link here and we'll reopen this.