Laravel add where clause to union result

Question

I'm doing a union on two queries, and I want to add a where clause to the result, but the where clause is added only to the first query. how can I fix that?

    $notifications = DB::table('notifications')
        ->select(DB::raw("notifications.uuid ,notifications.brand_id" ))
    $posts = DB::table('posts')
        ->select(DB::raw("posts.uuid ,posts.brand_id" ))
        ->unionAll ($notifications)
        ->orderBy('created_at' , 'desc')
        ->where('brand_ids' , '=' , '2')
    $result  = $posts->get();

I want this line

           ->where('brand_id' , '=' , '2')

to be added to the whole union, but it is added only to one of the queries.

Can't you add the `->where('brand_id', '=', '2')` on both queries, and for the second before the `unionAll` (and remove the `brand_ids` where clause) ? — Mtxz, Sep 04 '21 at 01:27
As already mentioned you need to use subquery. I was able to do that using the solution from the laracasts reply https://laracasts.com/discuss/channels/laravel/cant-use-where-clause-after-union-of-2-queries?page=1&replyId=897198 — electroid, Jul 18 '23 at 08:20

score 0 · Answer 1 · answered Sep 14 '16 at 13:55

I am not sure if there is a better way, but this works for me:

$notifications = DB::table('notifications')
                ->select(DB::raw("notifications.uuid ,notifications.brand_id"));
$posts = DB::table('posts')
        ->select(DB::raw("posts.uuid ,posts.brand_id"))
        ->unionAll($notifications)
        ->orderBy('created_at' , 'desc');
$result = DB::table(DB::raw("({$posts->toSql()}) as posts"))
                ->mergeBindings($posts)
                ->where('brand_id', '2')
                ->get();

score 0 · Answer 2 · answered May 14 '22 at 08:07

0

That is down to MYSQL rather than Laravel.

As you can see from the example here if you want to do a where after a union, it needs to be done as a subquery. WHERE statement after a UNION in SQL?

Let me hope this solution helps anyone.

Regards

answered May 14 '22 at 08:07

Masaba James Moses

564
4
17

score 0 · Answer 3 · answered May 14 '22 at 23:55

Firstly, your query is invalid because you order the results by created_at and you did not select this column. I wish my code helps you

$notifications = DB::table('notifications')
    ->select(DB::raw("uuid, brand_id, created_at"))
    ->where('brand_id', 2);

DB::table('posts')
    ->select(DB::raw("uuid, brand_id, created_at"))
    ->unionAll($notifications)
    ->orderBy('created_at', 'DESC')
    ->where('brand_id', 2)
    ->dd();

Laravel add where clause to union result

3 Answers3