I have two table like
The table have same field and data. I wanna union that two table
<?php
public function index() {
$trxSum = TrxModel::sumAmount()->sumTotTrx()->groupByUser();
// get last date on table 1
$trxSum = TrxModel::select('created_at')->first();
$startDate = '2022-10-20 00:00:00';
$qrySumBk = null;
if ($startDate <= $trxSum->created_at) {
$qrySumBk = $this->getDataSumTableTwo($params);
}
if ($qrySumBk) {
$trxSum->unionAll($qrySumBk['trx_sum_table_two']);
}
$trxSum->get();
// this getting result
// data :[
// {
// "user_id": a1,
// "total_trx": 1,
// "total_amount": 1000
// },
// {
// "user_id": a1,
// "total_trx": 1,
// "total_amount": 1000
// }
// ]
// expected result
// data :[
// {
// "user_id": a1,
// "total_trx": 2,
// "total_amount": 2000
// }
// ]
}
public function getDataSumTableTwo() {
$trxSum = TrxModelTwo::sumAmount()->sumTotTrx()->groupByUser();
return [
'trx_sum_table_two' => $trxSum
];
}
// model
public function scopeSumAmount($q) {
return $q->selectRaw('sum(amount) total_amount');
}
public function sumTotTrx($q) {
return $q->selectRaw('count(*) as total_trx');
}
public function scopeGroupByUser($q) {
return $q->groupBy('user_id');
}
I get the query to get like expected result use this
select user_id, sum(amount) as "total_amount"
from
(
select user_id,amount
from Table1
union all
select user_id,amount
from Table2
) table1, table2
group by user_id
How I can get that query on eloquent laravel?? Is it possible ? and if not how I should do to get query like sample but with condition ? example
// if has params user_id then
if ($userId) {
$query = "select user_id, sum(amount) as "total_amount"
from
(
select user_id,amount
from Table1
union all
select user_id,amount
from Table2
) table1, table2
where user_id = a1
group by user_id "
and then if other parameter (example: active field (yes or no) query update with filter condition
