Objection.js: Limit on eager loading

[RuiPMoreira]

Hi,

I am trying to get the first 10 childs of each parent object.

parents = await Parent.query(knex)
        .select(
          raw(
            "*, CONCAT(user_first_name, ' ', user_last_name) as name, user_profile_pic as photo"
          )
        )
        .eager("[childs(createdAtFilter)]", {
          createdAtFilter: builder => {
            if (startDate && endDate) {
              builder
                .where("created_at", ">=", startDate)
                .andWhere("created_at", "<=", endDate)
                .orderBy(orderColumn, orderDirection)
                .limit(10);
            } else {
              builder
                .orderBy(orderColumn, orderDirection)
                .limit(10);
            }
          }
        })
        .where(
          raw(
            "CONCAT(user_first_name, ' ', user_last_name) LIKE '" + name + "%'"
          )
        );

This doesn't return the childs for at least one of the parents, but if i remove the "limit", that will work fine. The same happens with pagination.

Any help, the problem should be mine?

Ty

[jeff3yan]

Eager relations are loaded using another query based on the parent result. The parent query will return N results, which will generate the child query with a WHERE "id" IN (...) filter.

Limiting that second query will not have the result you expect, since it limits the total amount rather than an amount _per parent_.

You may be able to achieve something similar to what you want by using a RANK() window function on the child model, but this will depend on what SQL dialect you are using.

[koskimas]

@jeff3yan is right. You can probably get that to work by using naiveEager instead of eager, but that creates a separate query for each Parent. So if you fetch 1000 parents, you get 1001 queries! There is no easy solution for this, other than naiveEager.

[jeff3yan]

@RuiPMoreira If you're using postgres or another dialect with a rank function, you could try something like:

.eager("[childs(createdAtFilter)]", {
  createdAtFilter: builder => {
    const { raw } = builder._modelClass.knex();
    const ranked = builder._modelClass.query()
      .select('*')
      .select(raw(
        'RANK() OVER (PARTITION BY "parent_id" ORDER BY ??) AS "rank"', [orderColumn]
      ))
      .as('ranked');

    if (startDate && endDate) {
      builder
        .where("created_at", ">=", startDate)
        .andWhere("created_at", "<=", endDate)
        .innerJoin(ranked, function() {
          this.on('child.id', '=', 'ranked.id').andOn('ranked.rank', '<=', 10);
        });
    } else {
      builder
        .innerJoin(ranked, function() {
          this.on('child.id', '=', 'ranked.id').andOn('ranked.rank', '<=', 10);
        });
    }
  }
})

This should have the intended effect, but you'll need to somehow get your order direction in there too.

[allxie]

Relatedly, can't get .first() to work in a similar situation. This resulted in with different data (all of the children show up instead of none). Thanks-- this thread was helpful.

[alvelig]

@allxie same for me .first() doesn't work, while .limit(1) does
either in relationMapping.filter or eager().modifyEager()

[heisian]

I think part of the issue, and something that I've grappled with, is that one _expects_ the parent/child relation model to map to SQL, but it doesn't. We need to remember what's going on here:

Relation: One Parent has Many Children.

When you perform a join, what does the result look like?

SELECT * FROM parents
JOIN children ON children.parent_id = parents.id;

The resulting row set is as such:

| parent.id | parent.name | children.id | children.parent_id | children.name |
|-----------|-------------|-------------|--------------------|---------------|
| 10 | 'bob' | 1 | 10 | 'jane' |
| 10 | 'bob' | 2 | 10 | 'jill' |
| 11 | 'boa' | 3 | 11 | 'jazz' |
| 11 | 'boa' | 4 | 11 | 'june' |
| 11 | 'boa' | 5 | 11 | 'jaye' |
| 12 | 'bif' | 6 | 12 | 'jack' |
| 12 | 'bif' | 7 | 12 | 'jayz' |
| 12 | 'bif' | 8 | 12 | 'jaru' |
| 13 | 'bea' | 9 | 13 | 'jora' |
| 13 | 'bea' | 10 | 13 | 'john' |

Let's attempt to do some LIMITing:

SELECT * FROM parents
JOIN children ON children.parent_id = parents.id;
-- LIMIT 2; <-- This would limit the total number of records,
-- and would not achieve the desired result.

Can anyone in this thread tell me how they would limit ONLY the child records in the JOIN?

It's not as trivial as it seems when you look at it from the SQL perspective. The solution requires sub-queries (complex join) or multiple queries (naiveEager), and as a result, it is NOT a trivial eager-load for Objection, or any other ORM.

@alvelig @allxie With any query that has this sort of complexity or higher, I would recommend writing the query in plain SQL first, getting that to work as desired, and _then_ translating to an Objection query.