CODEWITHSUNDEEP
mongodbsorting
Dougui
Dougui

Reputation: 7232

Sort by number of document associated

I have two collection, identities and interventions. And identity has many interventions. I have to sort by three types : at first there is identities with active intervention, then identities with inactive interventions and at last identities without interventions. Each types have to be sorted by alphabetical order.

Here is the data I hase :

> db.identities.find().pretty()

{
        "_id" : ObjectId("56c2ba0377656280a1000008"),
        "screenname" : "User without intervention 1"
}
{
        "_id" : ObjectId("56c2ba0677656280a100000c"),
        "screenname" : "User with inactive intervention 2"
}
{       
        "_id" : ObjectId("56c2ba49776562aca000076b"),
        "screenname" : "User with active intervention 1"
}
{       
        "_id" : ObjectId("56c432fa776562356200011a"),
        "screenname" : "User with active intervention 2"
}
{       
        "_id" : ObjectId("56c2ba49776562aca000076b"),
        "screenname" : "User without intervention 2"
}
{       
        "_id" : ObjectId("56c42cc7776562209500006a"),
        "screenname" : "User with inactive intervention 1"
}

> db.interventions.find().pretty()

{
        "_id" : ObjectId("56c4448a7765622817000045"),
        "identity_id" : ObjectId("56c2ba0377656280a1000008"),
        "active" : true
}
{
        "_id" : ObjectId("56c44a1b77656282f0000451"),
        "identity_id" : ObjectId("56c42f54776562b78a00032f"),
        "active" : false
}
{
        "_id" : ObjectId("56c450c8776562336a000776"),
        "identity_id" : ObjectId("56c432fa776562356200011a"),
        "active" : true
}
{
        "_id" : ObjectId("56c450c8776562336a000776"),
        "identity_id" : ObjectId("56c42cc7776562209500006a"),
        "active" : false
}

I expect to have the this results :

{
        "_id" : ObjectId("56c2ba49776562aca000076b"),
        "screenname" : "User with active intervention 1"
}
{
        "_id" : ObjectId("56c432fa776562356200011a"),
        "screenname" : "User with active intervention 2"
}
{       
        "_id" : ObjectId("56c2ba49776562aca000076b"),
        "screenname" : "User with inactive intervention 1"
}
{       
        "_id" : ObjectId("56c2ba0677656280a100000c"),
        "screenname" : "User with inactive intervention 2"
}
{       
        "_id" : ObjectId("56c2ba0377656280a1000008"),
        "screenname" : "User without intervention 1"
}
{       
        "_id" : ObjectId("56c2ba49776562aca000076b"),
        "screenname" : "User without intervention 2"
}

I'm not very competent with MongoDb. At the moment, I have just :

db['domain_test#identities'].aggregate({ $project: { screenname: { $toLower: "$screenname" } } }, {$sort: { screenname: 1 }})

It just sort by screenname but does not check the other table. I suppose I have to add other values to $project but I have no idea how.

Do you have a solution?

Upvotes: 0

Views: 45

Answers (1)

s7vr
s7vr

Reputation: 75914

You can try below aggregation in 3.4.

Use $lookup to get the matched interventions array ( one to one ) with $arrayElemAt in $addFields to output first element.

Use $indexOfArray to locate the position of searched boolean value in list of values, which is just 0 or 1 with -1 for missing field followed by $addFields to keep the output index in the extra field in the document and descending $sort on sort field to sort the document.

$project with exclusion to drop the sort field and intervention to get expected output.

db.identities.aggregate([
  {"$lookup":{
    "from":"interventions",
    "localField":"_id",
    "foreignField":"identity_id",
    "as":"interventions"
  }},
  {"$addFields":{
    "sortorder":{
      "$let":{
        "vars":{"intervention":{"$arrayElemAt":["$interventions",0]}},
        "in":{"$indexOfArray":[[false,true],"$$intervention.active"]}
      }
    }
  }},
  {"$sort":{"sortorder":-1}},
  {"$project":{"sortorder":0,"interventions":0}}
])

Upvotes: 1

Related Questions