How to sort by weighted values
I have this problem that I want to sort the query result based on field values from another collection,
Problem: I want to get 123 users first, and then get their messages, and then sort the message with the power of friends power,
I have it:
POST COLLECTON:
{
user_id: 8976,
post_text: 'example working',
}
{
user_id: 673,
post_text: 'something',
}
USER COLLECTON:
{
user_id: 123,
friends: {
{user_id: 673,strength:4}
{user_id: 8976,strength:1}
}
}
source to share
Based on the information you got from the user, you really want to exit into an aggregation structure that looks like this:
db.posts.aggregate([
{ "$match": { "user_id": { "$in": [ 673, 8976 ] } } },
{ "$project": {
"user_id": 1,
"post_text": 1,
"weight": {
"$cond": [
{ "$eq": [ "$user_id", 8976 ] },
1,
{ "$cond": [
{ "$eq": [ "$user_id", 673 ] },
4,
0
]}
]
}
}},
{ "$sort": { "weight": -1 } }
])
So why aggregation when it doesn't aggregate? As you can see, the aggregation framework is not just concatenated. Here it is used to "project" the new field into the document and populate it with "weight" for sorting. This allows you to return results sorted by the value you want them to be sorted.
Of course, you need to get from your input data into this form in a "generated" way that you do for any data. It takes a few steps, but here I am going to talk about a JavaScript way that should be easily converted to most languages
It also assumes that your actual "user" looks more like this, which would actually be:
{
"user_id": 123,
"friends": [
{ "user_id": 673, "strength": 4 },
{ "user_id": 8976, "strength": 1 }
]
}
From an object like this, you then construct an aggregation pipeline:
// user is the structure shown above
var stack = [];
args = [];
user.friends.forEach(function(friend) {
args.push( friend.user_id );
var rec = {
"$cond": [
{ "$eq": [ "user_id", friend.user_id ] },
friend.strength
]
};
if ( stack.length == 0 ) {
rec["$cond"].push(0);
} else {
var last = stack.pop();
rec["$cond"].push( last );
}
stack.push( rec );
});
var pipeline = [
{ "$match": { "user_id": { "$in": args } } },
{ "$project": {
"user_id": 1,
"post_text": 1,
"weight": stack[0]
}},
{ "$sort": { "weight": -1 } }
];
db.posts.aggregate(pipeline);
And that's all there is. You now have the code to view your friends list, and build another query to get all messages from friends, weighted by strength for each.
Of course, you could do the same with the query for all messages, just delete or change , but keeping the "weight" projection, you can "float" all messages of "friends" to the top. $match
source to share