CODEWITHSUNDEEP
mysqlnode.jsobjection.js
Avedis Kiyici
Avedis Kiyici

Reputation: 67

Objection.js how to filter a model based on relationship

So I've been trying to find a way to do the following sql in objection.js


SELECT
    u.user_id, u.profile_photo_name, u.first_name,
    u.last_name, COUNT(*) AS count
FROM users AS u
    INNER JOIN post_users AS pu ON (u.user_id = pu.user_id)
WHERE
    u.organization_id = 686
GROUP BY user_id
ORDER BY count DESC
LIMIT 1

This is what I've got so far... but can't use $relatedQuery this way

return await User.$relatedQuery('recognitions')
.where('organization_id', organizationID)
.select(
    'user_id',
    'profile_photo_name',
    'first_name',
    'last_name',
    raw('COUNT(*) as count')
)
.groupBy('user_id')
.orderBy('count', 'desc')
.limit(1)

This is the recognitions relationship:

            recognitions: {
                relation: Model.ManyToManyRelation,
                modelClass: Post,
                join: {
                    from: 'users.user_id',
                    through: {
                        from: 'post_users.user_id',
                        to: 'post_users.post_id',
                    },
                    to: 'posts.post_id'
                }
            },

Upvotes: 2

Views: 3050

Answers (1)

Avedis Kiyici
Avedis Kiyici

Reputation: 67

Had to use joinRelation instead like so also used first() instead of limit(1) to get a single object instead of an array of length 1

return await User.query()
.joinRelation('recognitions')
.where('organization_id', organizationID)
.select(
    'user_id',
    'profile_photo_name',
    'first_name',
    'last_name',
    raw('COUNT(*) as count')
)
.groupBy('user_id')
.orderBy('count', 'desc')
.first()

Upvotes: 1

Related Questions