CODEWITHSUNDEEP
mongodbmongoid
aliibrahim
aliibrahim

Reputation: 1965

Mongoid: Query with group by, distinct and having count operations

Suppose I have a model by the name of ItemAvailability that have a product_id , vendor_id, shop_id

I would like to group_by on product_id and select all those product_id that have distinct(vendor_id) having count >= 3.

Is this something doable using MongoID?

Sample Data:

ItemAvailability
product_id vendor_id shop_id
1              1        1
1              1        2
1              1        3
2              2        1
2              2        2
1              2        1
1              2        2
1              2        3
1              3        1
1              3        2

In the above data-set the result should be 1 as its available in all the vendors 1, 2 and 3.

Where as 2 should not be in result as it is only supplied by vendor 2.

Let me know if any more details are required on this?

Upvotes: 2

Views: 3033

Answers (1)

Anand Jayabalan
Anand Jayabalan

Reputation: 12904

You can use the aggregation framework to do group by queries in MongoDB. Take a look at the docs for an introduction. It's very powerful and you can get a hang of it by looking at the examples.

For the scenario you've described, the following query should help:

db.ItemAvailability.aggregate(
    [
        // Group By product_id, vendor_id
        {
            "$group": { _id: { product_id: "$product_id", vendor_id: "$vendor_id" } }
        },
        // Group results from above step by product_id and get the count
        {
            "$group": { _id: "$_id.product_id", count: { "$sum": 1 } }
        },
        // Filter the records for count >= 3
        {
            "$match": { count: { "$gte": 3 } }
        }
    ]
)

Upvotes: 6

Related Questions