Laravel query with where and where

Question

I've trying to basically do two SQL requests into one laravel array.

These are my SQL

SELECT * FROM `transactions` WHERE `plan` LIKE '1050' ORDER BY `updated_at` DESC
SELECT * FROM `transactions` WHERE `user` LIKE '1050' ORDER BY `updated_at` DESC

I want to have all transaction that have "plan" == "1050" AND all transactions that have "user" == "1050" in one variable.

This is not working for me:

$ct=transactions::where('user',$user->id)
    ->orWhere('plan', $user->id)
    ->orderby('created_at',"asc")->get();

why do you use `orWhere` if you want *all transaction that have "plan" == "1050" **AND** all transactions that have "user" == "1050" in one variable* ? — apokryfos, Jun 17 '20 at 09:00

score 1 · Accepted Answer · answered Jun 17 '20 at 09:16

The context agnostic way to combine the results of two union compatible queries (such as yours) would be:

$result = transactions::where('plan', 'LIKE', $user->id)->orderBy('updated_at', 'DESC')
                 ->union(
                      transactions::where('user', 'LIKE', $user->id)
                         ->orderBy('updated_at', 'DESC')
                 )
                 ->get();

This of course means that the results are ordered by plan then user. You can sort them by updated_at globally using the below function on the result:

$result = $result->sortBy('updated_at', 'DESC');

There's also a way to do it on the query level but I don't see much benefit since you're getting all the data anyway.

The above query should be functionally equivalent to:

$result = transactions::orWhere('plan', 'LIKE', $user->id)
                        ->orWhere('user', 'LIKE', $user->id)
                        ->orderBy('updated_at', 'DESC')
                        ->get();

This will have the same results but in a different order than the union.

Laravel query with where and where

1 Answers1