Combining two data with a time stamp and displaying the result in real time
Collections Inocmes
and Expenses
are used separately in many places throughout the application. there is only one page that has the below requirement. I don't believe there is no workaround in Mongodb that actually has millions of users :(
I am using React-Meteor in a project that have two collections named Incomes
and Expenses
. Doc income looks below
{
"_id" : "euAeJYArsAyFWLJs6",
"account" : "3m5Zxsije9b6ZaNpu",
"amount" : 3,
"receivedAt" : ISODate("2017-07-07T06:21:00.000Z"),
"type" : "project",
"project" : {
"_id" : "ec2WLt3GzHNwhK7oK",
"name" : "test"
},
"owner" : "nM4ToQEbYBsC3NoQB",
"createdAt" : ISODate("2017-07-07T06:21:37.293Z")
}
and below is what the flow Doc looks like
{
"_id" : "snWDusDbkLHHY2Yry",
"account" : "3m5Zxsije9b6ZaNpu",
"amount" : 4,
"spentAt" : ISODate("2017-07-07T06:21:00.000Z"),
"description" : "4",
"category" : {
"_id" : "vh593tw9dZgNdNwtr",
"name" : "test",
"icon" : "icon-icons_tution-fee"
},
"owner" : "nM4ToQEbYBsC3NoQB",
"createdAt" : ISODate("2017-07-07T06:22:04.215Z")
}
Now I have a page called transactions where I have to show the whole transaction (income and expenses) based on time, so my publish code for transactions looks like below
import { Meteor } from 'meteor/meteor';
import { Incomes } from '../../incomes/incomes.js';
import { Expenses } from '../../expences/expenses.js';
import { Counter } from 'meteor/natestrauser:publish-performant-counts';
let datefilter = (options, query) => {
let dateQuery = {$gte: new Date(options.dateFilter.start), $lte: new Date(options.dateFilter.end)};
let temp = {$or: [{receivedAt: dateQuery}, {spentAt: dateQuery}]};
query.$and.push(temp);
};
Meteor.publish('transactions', function(options) {
let query = {
owner: this.userId,
$and: []
};
if(options.accounts.length)
query['account'] = {$in: options.accounts};
options.dateFilter && datefilter(options, query);
//here i also apply other filter based on category and project which does not matter so i removed
if(!query.$and.length) delete query.$and;
//computing 'Transactions' below
return [
Incomes.find(query, {
sort: {
receivedAt: -1
},
limit: options.limit,
skip: options.skip
}),
Expenses.find(query, {
sort: {
spentAt: -1
},
limit: options.limit,
skip: options.skip
})
]
});
So far, everything is working fine until I do pagination on the transactions page, so the data has to be sorted by date. that's the real problem. Suppose my both collections have 10 records each, and my template page needs to list the first 10 results, so I sent skip 0 and limit 10, in return I got 10 income and 10 expense records, and there is no record on the second page. because pass and limit posted 10 for both. so how to deal with it? I also used a Technic counter, but it didn't work. remember my data is also in real time. any help would be greatly appreciated :)
source to share
This is a temporary solution for quick release access, you should consider the amount of your data and pass all checks before using Meantime. I'll change my schema when @NeilLunn prompts you and plan on migrating soon.
For adhoc Fix meeting display requirement I applied aggregate
with combination $out
. Now the code looks like below
Meteor.publish('transaction', function(options){
//here I perform many filter based on query and options
//which deleted to shorten code on SO
//call asynchronous method just to get result delay :)
Meteor.call('copyTransactions', (err, res) => {
//something do here
});
//note the we return results from **Transactions** which is comes as third collection
return [
Transactions.find(query, {
sort: sortbyDate,
skip: options.skip,
limit: options.limit,
}),
new Counter('transactionsCount', Transactions.find(query, {
sort: sortbyDate
}))
];
});
Now publish the Transactions (separate collection) I desired as a merge collection. Note that this ends with s
in the name as transactions
, so do not confuse with the previous ( transaction
)
publish the collection of merges separately as "transactions"
Meteor.publish('transactions', function(limit){
return Transactions.find(
{
owner: this.userId
});
});
and here is the most important method that is called in the publication to combine two collections in a third collection in which I first aggregated
all result with $out
and then add the second collection with batch insert
import { Expenses } from '../../../api/expences/expenses'
import { Incomes } from '../../../api/incomes/incomes'
import { Transactions } from '../transactions'
import Future from 'fibers/future';
export const copyTransactions = new ValidatedMethod({
name: 'copyTransactions',
validate:null,
run() {
//we are using future here to make it asynchronous
let fu = new Future();
//here Expenses directly copied in new collection name Transactions
// TODO: use $rename or $addField in aggregate instead of $project
Expenses.aggregate([{
$project: {
account : "$account",
amount : "$amount",
transactionAt : "$spentAt",
description : "$description",
category : "$category",
type: {
$literal: 'expense'
},
owner : "$owner",
createdAt : "$createdAt"
}
}, {
$out: "transactions"
} ]);
//now append Transactions collection with incomes with batch insert
Incomes.aggregate([{
$project: {
account : "$account",
amount : "$amount",
transactionAt : "$receivedAt",
type:{
$literal: 'income'
},
project : "$project",
owner : "$owner",
createdAt : "$createdAt"
}
}], function (err, result) {
//if no doc found then just return
if(!result.length){
fu.return('completed')
}
else{
Transactions.batchInsert(result, function(err, res){
fu.return('completed')
})
}
});
return fu.wait();
}
});
If the second collection is
aggregated
also with$out
, then it will overwrite :(
@client I just need to subscribe to a "transaction" with my parameters and query and get the real time merge results from transactions
source to share