Loopback: 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

[superkhau]

This issue may be related to https://github.com/strongloop/loopback-datasource-juggler/pull/787. I'm going to take over that PR and add in some test cases for your limit issue to see if we can kill two birds with one stone.

[superkhau]

The fix it at https://github.com/strongloop/loopback-datasource-juggler/pull/786/files, needs more test coverage before merging though.

[superkhau]

I've merged in the fixes. Please let me know if it works for you guys! ;)

[ebarault]

@superkhau : i can't find the merge in the master branch, is it on a specific branch?
please see my comment an PR here i think the patch for https://github.com/strongloop/loopback-datasource-juggler/pull/787 is not enough

[superkhau]

@ebarault I merged in https://github.com/strongloop/loopback-datasource-juggler/issues/786. See if this is good enough for you, if not, we'll have to continue the fix for your issue separately. I was hoping the fix would solve both, but it's really specific for the limit issues within scoping. ;)

[ebarault]

@superkhau well i guess it's unfortunately of no help for issue https://github.com/strongloop/loopback-datasource-juggler/pull/787

[superkhau]

@ebarault Let's continue the conversation over there since it's unrelated to this one.

[barocsi]

Bump, which version would fix this issue? Still present for me on "loopback-datasource-juggler": "^2.39.0",

 User.posts({
                 id: 'me',
                 filter: {
                   where: {and: and},
                   include: ['tags',{relation: 'grades', scope: {limit:6, order: "index ASC"}}],
                   skip: skip,
                   limit: limit,
                   order: "created DESC"
                 }
               },...

it limits not the grades per user posts, but the total number of grades to be returned to 6 which is not the assumed expected behavior.

[jmereaux]

Hello,
Is there any update on this please?
Updated to [email protected] but the issue is still there

[mneil]

This is still an issue as far as I can tell. I set an internal upper limit to queries to stop people from requesting hundreds of thousands of documents at a time and found out this makes the data returned incomplete.

It feels like a bug since it's undocumented. However, I'm not sure how you would handle limits on relations given how the queries are currently performed. An include like this

{relation: 'grades', scope: {limit:6, order: "index ASC"}}

Is turned into this with the juggler

{relation: 'grades', scope: {limit:6, order: "index ASC", id: {inq: [1,2,3,4,5,6,7,8,9,10]}}}

So I'm effectively asking for grades belonging to 10 users but my limit says only grab the first 6 of them. Even if all 10 users have grades I only get 6. What I expect, and what seems like everyone else in this thread expects, is to get at most 6 grades per user for a real limit of 60. To achieve that the juggler would need to make 10 separate queries to the database. That solution is really inefficient and would likely cause major performance issues. I'm not sure what the correct way to handle this is.

[superkhau]

@jannyHou Can you TOB this when you have time, it's been an issue for awhile and I don't have bandwidth to look into it -- related to relations FA anyways so it's up your alley. ;)

[jannyHou]

@superkhau Sure, thank you for mention me. let me reopen it first 😆

[jaydp17]

@superkhau @jannyHou any update ?

[jannyHou]

@jaydp17 I feel this is same issue as https://github.com/strongloop/loopback/issues/3030#issuecomment-275249931

In loopback-datasource-juggler we have a test case to cover this scenario and it passes, can you take a look of my comment above and provide more details about what's the difference between that test case and your use case? Thanks.

[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.