user2851129 user2851129 - 4 months ago 9
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.

Code:

$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")
->groupBy('date')
->get();
}


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"))
->groupBy('date')
->get();
}


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 = response.data.expenses;
$scope.income = response.data.income;
$scope.totalExpenses = response.data.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;
break;
}
);
};


View Portion:

<tr>
<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">
Expense
</th> <th st-sort="cost" class="table-header-repeat line-left minwidth-1">
Income
</th>

</tr>


</thead>
<tbody>

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

<td><%income['row.date']%></td>


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


</tr>


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

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.date,e.cost,'0' as income
from test.expenses as e
where e.date not in (select i.date from test.incomes as i)
union all
select e.date, e.cost as cost, i.income as income
from test.expenses as e
inner join test.incomes as i on e.date = i.date 
union all
select i.date,'0' as cost,i.income as income
from test.incomes as i
where i.date not in (select e.date 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.