MongoDB findOne（）查询博客帖子文档中的数组

Question

So, basically, I am doing some blog work, and, as I am newbie in regards to MongoDB, I cannot construct the right query for this. I just wanted to do a simple, separate page for post edition purposes. So, through the cursor, I managed to use a foreach() loop and list all the comments, I also managed to create the post submission form that works, session manager, et cetera. Here's the deal, I cannot construct the right query for a single post edition. In my PHP file, there's an anchor leading to the edit page, and it uses its comment id for it, likewise:

 <a href="edit.php?id=<?php echo $comment["_id"]; ?>"> edit </a>

It directs to the second page by the id of the particular post, a page in which I cannot match the right findOne() query. It always find a NULL value. What I want to do is to find this particular post, and put it into the textarea for user to edit it and then repost it. It is very simple but I have a hard time figuring it out. I read through the MongoDB documentation, but couldn't find anything that satisfies me. The other thing is that MongoDB isn't the most popular, and community is not so vast as with MySQLi.

Here's my post document inside the collection:

{
    "_id" : "css-clearfix-explained",
    "title" : "CSS Clearfix Explained",
    "comments" : [ 
        {
            "_id" : ObjectId("53ff7ad2af105b0c0b3c9869"),
            "comment" : " some comment",
            "author" : "maciejsitko",
            "date" : ISODate("2014-08-28T18:54:10.569Z")
        }, 
        {
            "_id" : ObjectId("53ff7ae8af105b080b3c986a"),
            "comment" : "some comment 2",
            "author" : "maciejsitko",
            "date" : ISODate("2014-08-28T18:54:32.670Z")
        }, 
        {
            "_id" : ObjectId("53ff851baf105b370b3c9869"),
            "comment" : "some comment 3",
            "author" : "maciejsitko",
            "date" : ISODate("2014-08-28T19:38:03.710Z")
        }, 
        {
            "_id" : ObjectId("53ff8589af105b3f0b3c986b"),
            "comment" : "some comment 4",
            "author" : "maciejsitko",
            "date" : ISODate("2014-08-28T19:39:53.220Z")
        }, 
        {
            "_id" : ObjectId("53ff8599af105b0c0b3c986a"),
            "comment" : "some comment 5",
            "author" : "drummond",
            "date" : ISODate("2014-08-28T19:40:09.149Z")
        }, 
        {
            "_id" : ObjectId("5400b5f3af105b750b3c9869"),
            "comment" : "skdjfksfjksdfj",
            "author" : "maciejsitko",
            "date" : ISODate("2014-08-29T17:18:43.671Z")
        }
    ]
}

And I really need to find the one post with the proper "_id" (using $_GET['id']) inside that "comments" array. How to solve that?

Answer 1

The result you are trying to achieve cannot be done using 'findOne'. Instead you need to use the Mongo DB aggregation.

Essentially, the Mongo DB query looks like this:

db.collection.aggregate([
    $match:{
        "_id": [id of the post document]
    },
    $unwind:"$comments",
    $match:{
        "comments._id": [id of the comment within the comments array (i.e. GET param)]
    },
    $project:{
        "comments":1
    }
])

What each step in the aggregation pipeline does is:

1. Finds the post document by its _id field.
2. Denormalizes the post document by flattening out its comments array. This yields a sub-result in the pipeline which consists of one document per comment in the post document.
3. Find the sub-document corresponding to the comment we seek by its comment._id field.
4. Project the comments field of this sub-result document which contains the comment you seek.

I am not well-versed with PHP but I have attempted to codify this aggregation in PHP using the manual here. Below is the aggregation pipeline, codified in PHP:

$ops = array(    
    array(
        '$match' => array(
            "_id" => [id for the post document]
        ),
    ),
    array('$unwind' => '$comments'),
    array(
        '$match' => array(
            "comments._id" => [id for the comment within the comments array (i.e. GET param)]
        ),
    ),
    array(
        '$project' => array(
            "comments" => 1
        ),
    ),
);
$results = $c->aggregate($ops);

The query should give you the required result, and I hope the PHP snippet works too.