CODEWITHSUNDEEP
mongodbaggregation-framework
patrickkeller
patrickkeller

Reputation: 1256

MongoDB Aggregation count over a relation

I've two collection, Buildings and Orders. A Building can have many Orders (1:N Relation). I'm trying to achieve a "Top Ten Statistic"(Which Buildings have the most Orders) with the aggregation framework. My Problem is, how can i get the total Orders per Building? Is there a way to "mix" data from two collections in one aggregation?

Currently i'm doing something like this:

db.buildings.aggregate( [
                        { $group : _id : { street : "$street",
                                          city : "$city",
                                          orders_count : "$orders_count" }},
                        { $sort : { _id.orders_count : -1 }},
                        { $limit : 10}
                        ] );

But in this case the "orders_count" is pre-calculated value. It works but is very inefficient and to slow for "live" aggregation.

Is there a way to count the related orders per building directly in the aggregation (im sure there is a way...)?

Many Thanks

Upvotes: 2

Views: 2179

Answers (1)

Asya Kamsky
Asya Kamsky

Reputation: 42352

You don't say how orders relate to buildings in your schema but if an order has a building id or name it references, just group by that:

db.orders.aggregate( { $group : { _id: "$buildingId",
                                  sum: {$sum:1}
                                }
                     },
                     /* $sort by sum:-1, $limit:10 like you already have */
)

Upvotes: 1

Related Questions