How to query documents with embeded array and sort them

Question

I have problem to query documents with embedMany.

These are my documents, where User embeds many Groups (that I call userGroups).

class User {

    /**
     * @MongoDB\Id
     */
    protected $id;

     /**
      * @MongoDB\Field(type="string")
      */
     protected $name;

    /**
     * @MongoDB\EmbedMany(targetDocument="Group", strategy="setArray")
     * @var UserGroups[]
     */
    protected $userGroups;
}

class UserGroup {

    /**
     * @MongoDB\NotSaved
     * @MongoDB\ReferenceOne(targetDocument="Group")
     * @var Group
     */
    protected $group;

    /**
     * @MongoDB\Field(type="string")
     * @var string
     */
    protected $type;

    /**
     * @MongoDB\Field(type="date")
     * @var \DateTime
     */
    protected $joinedAt;
}

These are DB documents in User collection:

{ 
    "_id" : ObjectId("56de151b821b16ac02310a25"), 
    "name" : "Some user name", 
    "userGroups" : [
        {
            "group" : DBRef("groups", ObjectId("5705157244ae89863aaeb725"), "some group name"), 
            "type" : "T1", 
            "joinedAt" : ISODate("2016-05-13T19:59:43.000+0000")
        }, 
        {
            "group" : DBRef("groups", ObjectId("571498f7821b16100c5bcc58"), "some other group name"), 
            "type" : "T1", 
            "joinedAt" : ISODate("2016-05-21T20:07:26.000+0000")
        }, 
        {
            "group" : DBRef("groups", ObjectId("57348618c67940a0528b4567"), "some other group name T2"), 
            "type" : "T2", 
            "joinedAt" : ISODate("2016-05-30T18:07:39.000+0000"), 
        }
    ]
}

{ 
    "_id" : ObjectId("287sd3d728as56dnsdu2hsds782nsdsd"), 
    "name" : "Some other user name", 
    "userGroups" : [
        {
            "group" : DBRef("groups", ObjectId("5705157244ae89863aaeb725"), "some group name"), 
            "type" : "T2", 
            "joinedAt" : ISODate("2016-05-13T19:59:43.000+0000")
        }, 
        {
            "group" : DBRef("groups", ObjectId("57348618c67940a0528b4567"), "some other group name T2"), 
            "type" : "T2", 
            "joinedAt" : ISODate("2016-05-30T18:07:39.000+0000"), 
        }
    ]
}

How can I query for users in group with ID '5705157244ae89863aaeb725' and type "T1"? The following query in Users collection delivers both users and it should deliver only the first, which has group with ID '5705157244ae89863aaeb725' and type "T1"

{ userGroups: { $elemMatch: 
               { "group.$id": ObjectId("5705157244ae89863aaeb725"), "type": "T1" } }} 

But cannot sort them by joinedAt field

Answer 1

to sort inner array field you need to use aggregation framework: This example contains steps and you can remove them to see how document changes

var unwind = {
    $unwind : "$userGroups"
}
var match = {
    $match : {
        "userGroups.group.$id" : ObjectId("5705157244ae89863aaeb725"),
        "userGroups.type" : "T1"
    }
}
var sort = {
    $sort : {
        "userGroups.joinedAt" : -1
    }
}
var reGroup = {
    $group : {
        _id : {
            id : "$_id",
            name : "$name"
        },
        userGroups : {
            $push : "$userGroups"
        }
    }
}
var reShapeDocument = {
    $project : {
        _id : "$_id.id",
        name : "$_id.name",
        userGroups : 1
    }
}

//step 1

db.coll.aggregate([match ])

//step 2

db.coll.aggregate([match, unwind    ])

//step 3

db.coll.aggregate([match, unwind, match,    ])

//step 4

db.coll.aggregate([match, unwind, match, sort,  ])

//step 5,6

 db.coll.aggregate([match, unwind, match, sort, reGroup, reShapeDocument])

1 and 3 are selecting only documents matching criteria, 2 - unwinds array elements to separate documents, 4 - performs sort, 5 and 6 are used to restore previous shape