What's the best way to limit results in a joined relation using withGraphFetched/withGraphJoined?

[nikkwong]

For example,

Shop.query().select('*').withGraphJoined('products.variants')

I notice that putting a limit on the end will limit the variants to two, and limits the products to 1, which is confusing:

Shop.query().select('*').withGraphJoined('products.variants').limit(2)
[
    {
       ...shopData,
      products: [{
         // 1 result 
         variants: [{}] // 2 results total
     }]
   }
]      

I want to be able to use withGraphJoined (or maybe withGraphFetched in some circumstances) to be able to limit each level of relations. I know that the documentation says that fetching relations will hinder the use of offset/join; but I want to know what people are doing in practice to implement this kind of functionality then. Thanks.

[JeongJuhyeon]

Try making an SQL query which gets at most 10 shops and then joins each of them on at most 5 products. You'll see that it becomes a really complex query which differs per DB dialect. It doesn't seem viable to support this, I haven't seen any ORM that does. I'm not sure what your use case is but it sounds pretty rare.

I notice that putting a limit on the end will limit the variants to two, and limits the products to 1, which is confusing:

Basic SQL knowledge can tell you why this happens, you're joining multiple tables and returning the first two rows. If Shop 1 has Product 1 with multiple variants, it's going to return those rows.

but I want to know what people are doing in practice to implement this kind of functionality then. Thanks.

Build it themselves with the query builder.

[nikkwong]

Try making an SQL query which gets at most 10 shops and then joins each of them on at most 5 products. You'll see that it becomes a really complex query which differs per DB dialect. It doesn't seem viable to support this, I haven't seen any ORM that does. I'm not sure what your use case is but it sounds pretty rare.

Well I don't know if it's that rare. It seems like a common use case that you'd want to limit the number of results on a relation pulled in.

Build it themselves with the query builder.

Yeah, this issue is because I don't know how to do that. Thank you.

[JeongJuhyeon]

If you can show us the query you want in SQL, we can help you translate it to the querybuilder. If you can't, it's probably better to ask on Stackoverflow as that question isn't really related to Objection.

[nikkwong]

I suppose I'm hopeless, since I am clueless as to what the SQL would be

[stel-dare]

@nikkwong I'm facing the same issue as well. Did you find a solution?

[CarsonStauffer]

I've solved this before by using a second query, applying the limit to it, then joining that in a separate query:

const subQuery = Shop.query()
                   .distinct('id')
                   .limit(2)
                   .as('subQuery')

Shop.query()
  .innerJoin(subQuery, 'id', 'subQuery.id')
  .withGraphJoined('products.variants')