Objection.js: Query OrderBy based on Eager column

[alihesari]

Hi, How to can I orderBy Result based on eager?
In this Query, I want to order by salePriceAdult in sessions eager.
Can anyone Help me?

My Query:

rows = await Model.query()
    .where({
        status: 'active'
    })
    .andWhere(filterCity)
    .eager('[media, sessions, comments]')
    .modifyEager(
        'media', qb => qb.select(raw('CONCAT("http://10.0.2.2:8000", path) as url')
        ))
    .modifyEager(
        'sessions', qb =>
            qb.select('poolPriceAdult', 'salePriceAdult', 'discount')
                .where({
                    status: 'active',
                    sessionType: 'public'
                })
                .orderBy('salePriceAdult', 'ASC')
    )
    .modifyEager(
        'comments', qb =>
            qb.select('tbl_pool_comments.rank', 'tbl_pool_comments.comment',
                raw('CONCAT(tbl_users.firstName," ",tbl_users.lastName) as commenter')
            )
                .whereRaw('tbl_pool_comments.status = "accepted"')
                .innerJoin('tbl_users', 'tbl_pool_comments.userId', 'tbl_users.id')
                .orderBy('tbl_pool_comments.createdAt', 'DESC')
    )
    .select('id', 'name', 'cityId', 'address', 'features', 'description', 'latitude', 'longitude', 'rank')
    .orderBy('sessions.salePriceAdult', 'ASC')

[koskimas]

Isn't sessions a m2m or 1-m relationship? What would ordering based on salePriceAdult even mean? Think about it, there's one parent for N sessions. How can the one be sorted based on the many? Or do all sessions of a Model have the same salePriceAdult? You probably need to sort based on max(sessions.salePriceAdult) or something like that, based on what you want.

Anyway, the solution is to use joins. You can experiment by replacing eager with joinEager, but that would mean you'd have to change other things in your query too. You can also simply join the sessions relation using joinRelation('sessions') to the main query, but then you will get duplicates if there are more than one session per Model. That's basically the same problem I described in the first paragraph.