How to make query on laravel with eloquent with sum and different table

Viewed 32

I have two table like

enter image description here

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

0 Answers
Related