在MongoDB中计数

Question

i've this query in SQL:

"SELECT code, name, count(*) FROM table WHERE customer = "XXX" GROUP BY code ORDER BY count(*) DESC;

I'm working with PHP, and I've to translate this SQL query for mongodb. I know that I've to do something like this for counting in mongodb:

$array_where_condition = array("customer" => "XXX");
$db->find($array_where_condition)->count();

but I can't understand how to write a query like the one above.

Answer 1

If you are looking to use map reduce for such a query then you are already doing it wrong.

MR would not be a performant replacement to the SQL aggregation framework in this case and would not run realtime inline to your own application.

You can however use the aggregation framework, this might provide the performance you are looking for. I am a little rusty with the aggregation framework and this is written quickly however this might be something close to what you need:

db.col.aggregate(
    {$match: {customer: XXX}}, 
    {$group: 
        {_id: "$code", name: "$name", count: {$sum: 1}}
    }
)

Here is the reference: http://docs.mongodb.org/manual/reference/aggregation/

Of course the most performant and easiest way to sovle this problem is to design your schema in a NoSQL manner to avoid these sorts of queries: http://www.mongodb.org/display/DOCS/Schema+Design I have no idea of your current schema but it might not be NoSQL orientated if your looking to do a query like that frequently.