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
?
As of Waterline@0.11.8, 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);
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With