CODEWITHSUNDEEP
laravellaravel-5eloquent
Jovs
Jovs

Reputation: 892

How to group by with union in laravel

I have two table that I want to union and after I union them I want to groupBy the one column that I used in union

Here is what I tried:

$issuance = DB::table('issuance as i')
            ->select('i.issue_to as stud_id', 'i.created_on');

$stud= DB::table('transfer as t')
            ->select('t.transfer_to as stud_id', 't.created_on')
            ->union($issuance)
            ->select('stud_id', 'created_on', DB::raw('COUNT(*) as total_asset'))
            ->groupBy('stud_id')
            ->orderBy('created_on', 'DESC')->get();

This is the MySQL query in what I tried

"(select `stud_id`, `created_on`, COUNT(*) as total_asset from `transfer` as 
`t` group by `stud_id`) union (select `i`.`issued_to` as `stud_id`, `i`.`created_on` from 
`issuance` as `i`) order by `created_on` desc"

What I really want in MySQL is like this:

select stud_id, count(*) from ((select `t`.`transfered_to` as `stud_id`, 
`t`.`created_on` from `transfer` as `t`) union (select `i`.`issued_to` as 
`stud_id`, `i`.`created_on`, COUNT(*) as asset from `issuance` as `i`)) as t 
group by stud_id order by `created_on` desc

Thank you for the help

Upvotes: 4

Views: 2465

Answers (2)

Maxim Colesnic
Maxim Colesnic

Reputation: 465

Maybe nobody cares anymore, but after an hour of searching I understand it

return DB::query()
    ->select(['*'])
    ->from($query1->union($query2), 'tmp')
    ->groupBy(['common_field'])
    ->get();

for (Laravel 9)

Upvotes: 3

sss S
sss S

Reputation: 384

//try this example ,it will helps you

$query1 = DB::table('table1')->where(....);

$query2 = DB::table('table2')->where(....);

$data = DB::select(DB::raw('id, MAX(created_at) as max_created_at')>union($query1)->union($query2)->orderBy('max_created_at')->groupBy('id')->get();

Upvotes: 1

Related Questions