How to select, groupBy, and join in Waterline or MongoDB

Question

I have three models: user, noun, and usernoun (user_noun in PHP/Eloquent). There is a many to many relationship between user and noun. The "pivot" table has an extra attribute score. I can use this query with Eloquent to sum the scores of each noun a user has to get the user's total score:

$users = User::leftJoin('noun_user', 'user.id', 'noun_user.user_id')
    ->groupBy('user.id')
    ->select('user.*', DB::raw('sum(noun_user.score) as score'))
    ->orderBy('score', 'desc')
    ->get();

But I can't figure out how to get this to work in Waterline. This works, but not when I uncomment the .populate('user') line. I need user to be populated.

UserNoun
    .find({})
    //.populate('user')
    .groupBy('user')
    .sum('score')
    .sort({ score: 'desc' })
    .exec((err, usernouns) => {
    return res.json(usernouns)
})

here is a .native() query that works:

UserNoun.native(function(err, collection) {
    collection.aggregate([
        {
            $lookup: {
                from: 'user',
                localField: 'user',
                foreignField: '_id',
                as: 'user'
            }
        },
        {
            $group: { _id: '$user', total: { $sum: '$score' } }
        },
        {
            $sort : { total: -1 }
        }
    ]).toArray(function (err, results) {
        return res.json(results)
    })
})

Can this native query be rewritten in Waterline with groupBy and populate and sum?

Scanlan

Answer 1

As of [email protected], the only way to do that is to use native queries.

By the way, you can export your method inside the User model:

// User.js
module.exports = {

    // ...

    findTotalScores: function (callback) {
        UserNoun.native(function(err, collection) {
            if (err) {
                return callback(err);
            }

            collection.aggregate([
                {
                    $lookup: {
                        from: 'user',
                        localField: 'user',
                        foreignField: '_id',
                        as: 'user'
                    }
                },
                {
                    $group: { _id: '$user', total: { $sum: '$score' } }
                },
                {
                    $sort : { total: -1 }
                }
            ]).toArray(callback);
        });
    }

};

And you can use it in your controller by calling:

User.findTotalScores(function (err, results) {
    return res.json(results);
});

Passifloraceous

Answer 2

You can not do joins with waterline as of now, you will have to use raw queries for that. populate will just populate the associated fields, it does not return you the result the way sql join returns it.

Waterline at this point only supports using groupBy in combination with sum(), count() etc.

For groupby and sort you can use following :

Model.find()  
.groupBy('term') 
.sum('count')  
.limit(20)
.sort({count: 'desc'}) 
.exec(function (err, data){
//Your code here..
});

Fluorosis

Can you please share reference links for groupBy and sum methods? – Sumrall

Not able to find the exact document, but i tried the query, groupby in sails works with a calculative function. Found one link - #20255410 – Fluorosis