user2851129 user2851129 - 1 year ago 53
JSON Question

merging table using date field if common and add new row if date field is not common

I am developing house app.I retrieve data using query for expense and income from different table name as expenses and incomes respectively.
If I use expense only then it would print and if i try to mix income value too it give me problem.


$expenses = $expense->dailyExpense();
$income = $income->dailyIncome();
return response()->json(['data' => ['expenses' => $expenses , 'income' => $income] , 'msg' => 'Daily Expense']);

the query portion for income is:

public function dailyIncome()

return $this->makeModel()
->select(DB::raw("sum(cost) as income"),"date")

the query portion for income is:

public function dailyExpense()

return $this->makeModel()
->select(DB::raw("sum(cost) as cost") , "date" , DB::raw("dayname(date) as calendar"))

Client portion:

$scope.genereateReport = function () {

$scope.choices = ['Daily', 'Monthly', 'Yearly'];
$scope.$watch('selection', function (newVal, oldVal) {
switch (newVal) {
case 'Daily':

$scope.url = $scope.base_path + 'dailyExpenses';
$http.get($scope.url).success(function (response) {
$scope.expenses =;
$scope.income =;
$scope.totalExpenses =;


$scope.displayedCollection = [].concat($scope.expenses,$scope.income);
console.log("collection:" + $scope.displayedCollection);
$scope.totalExpenses = [].concat($scope.$totalExpenses);
// $scope.income = [].concat($scope.income);

$scope.itemsByPage = 10;

View Portion:

<th class="table-header-check">S.N</th>
<th st-sort="date" class="table-header-repeat line-left minwidth-1">Date</th>
<th st-sort="date" class="table-header-repeat line-left minwidth-1">Calandar</th>
<th st-sort="cost" class="table-header-repeat line-left minwidth-1">
</th> <th st-sort="cost" class="table-header-repeat line-left minwidth-1">



<tr data-ng-repeat="row in displayedCollection track by $index" >
<td><% $index+1 %></td>


<td colspan="4">
Total Expense: <%totalExpenses%>


Result of above query in json form is:

enter image description here

View is like this without displaying income:
enter image description here

Desired output is like this..

enter image description here]3

Answer Source

You can get your expected result by using sql union and join. Instead creating two separate functions, you can create one function and use raw query like below :

select,e.cost,'0' as income
from test.expenses as e
where not in (select from test.incomes as i)
union all
select, e.cost as cost, i.income as income
from test.expenses as e
inner join test.incomes as i on = 
union all
select,'0' as cost,i.income as income
from test.incomes as i
where not in (select from test.expenses as e);

Here I created two tables named expenses and incomes with the given fields in the the table you specified above.And I use the query mentioned above and result is as follows.

enter image description here

I hope this is what you expected.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download