Skip to content

Instantly share code, notes, and snippets.

@amitava82
Last active January 31, 2017 03:13
Show Gist options
  • Save amitava82/77b4f2186a29e9bc2e5807c5db1873ca to your computer and use it in GitHub Desktop.
Save amitava82/77b4f2186a29e9bc2e5807c5db1873ca to your computer and use it in GitHub Desktop.
aggregate user stock
db.getCollection('users').aggregate([
{
$match: {
role: 'USER'
}
},
{
$lookup: {
from: 'ledgers',
localField: '_id',
foreignField: 'userId',
as: 'ledgers'
}
},
{
$lookup: {
from: 'activities',
localField: '_id',
foreignField: 'userId',
as: 'activities'
}
},
{
$project: {
_id: 1,
email: 1,
stock: 1,
fname: '$profile.firstName',
lname: '$profile.lastName',
credit: {
$sum: {
$map: {
input: '$ledgers',
as: 'item',
in: {
"$cond": [
{ "$eq": [ "$$item.type", 'credit' ] },
"$$item.qty",
0
]
}
}
}
},
debit: {
$sum: {
$map: {
input: '$ledgers',
as: 'item',
in: {
"$cond": [
{ "$eq": [ "$$item.type", 'debit' ] },
"$$item.qty",
0
]
}
}
}
},
totalDistribution: {
$sum: {
$map: {
input: '$activities',
as: 'item',
in: "$$item.samples"
}
}
}
}
},
{
$project: {
_id: 1,
email: 1,
fname: 1,
lname: 1,
stock: 1,
creditTotal: '$credit',
debitTotal: '$debit',
totalDistribution: 1,
ledgerBal: {
$subtract: ['$credit', '$debit']
},
activityBal: {
$subtract: ['$credit', '$totalDistribution']
}
}
}
])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment