How to find documents from collections and group by that documents in Meteor.js?

help
1

Hello Everyone!
I would like to find documents from collections and group it by some fields and sum it together to return it to the helper.

For example : I have Sales, SaleDetails and Products Collections and I want to get product name and quantity from saleDetails where Sales is paid. like SQL Code:

SELECT  Products.name as name,
sum(SaleDetails.quantity) AS quantity
FROM (( Sales INNER JOIN SaleDetails ON Sales.Id=SaleDetails.SaleId)
INNER JOIN Product ON Product.Id=SaleDetails.ProductId)
Where Sale.IsPay=true
GROUP BY SaleDetails.ProductId

What should I do with Meteor.js?

2

For example (not tested):

var saleIds = Sales.find({ IsPay: true }, { fields: { Id: 1 } }).map(function(sale) { 
    return sale.Id; 
});
var saleDetails = SaleDetails.find(
    { SaleId: { $in: saleIds } }, 
    { fields: { ProductId: 1, quantity: 1 } });
var result = saleDetails.map(function(sd) { 
    return {
        product: Products.findOne(sd.ProductId),
        quantity: sd.quantity
    };
});
3

Thank you for your help. But How can I sum the quantity together if the ProductId in SaleDetails is the same. I means I would like to get quanity and product name by Group it by ProductId.

4

I also have problems with grouping in Meteor. Why meteor don’t allow to use db.collection.group? I really would like to delegate grouping into database and not do it on my own (especially on client).

5

Maybe something like:

var saleIds = Sales.find({ IsPay: true }, { fields: { Id: 1 } }).map(function(sale) { 
    return sale.Id; 
});
var saleDetails = SaleDetails.find(
    { SaleId: { $in: saleIds } }, 
    { fields: { ProductId: 1, quantity: 1 } });
var result = {};
saleDetails.forEach(function(sd) { 
    if (result[sd.ProductId])
        result[sd.ProductId].quantity += sd.quantity;
    else
        result[sd.ProductId] = { 
            name: Products.findOne(sd.ProductId).name,
            quantity: sd.quantity
        }
});
6

Thank you for your help.

7

Meteor uses MongoDB and the only way to group using MongoDB is to use its aggregation framework. There are community packages that expose the aggregate() method, but unfortunately it is not going to be reactive.

You may need to aggregate and save those results in a separate collection and then query that collection instead.

If you don’t like any of this then you would have to manually group it with JavaScript after fetching the documents with underscore.js’ _.groupBy method which could help.

3 Likes