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.

Lamination

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) ? – Hierogram

As already mentioned you need to use subquery. I was able to do that using the solution from the laracasts reply laracasts.com/discuss/channels/laravel/… – Valance

Answer 1

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();

Hiroshima

Wish there was a better way – Ciera

Answer 2

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

Hiles

Answer 3

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();

Ribband

Answer 4

In Laravel, the where clause is applied only to the last segment of the query builder chain. If you want to apply a where condition to the entire union result, you can use a subquery.

$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);

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

Astrophysics

Answer 5

To add a where clause to the entire union instead of just one query, you need to use a subquery. Here's an example of how you can modify your code:

$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);

$result = DB::table(DB::raw("({$posts->toSql()}) as combined"))
    ->mergeBindings($posts) // This is important to merge the bindings
    ->orderBy('created_at', 'desc')
    ->where('brand_id', '=', '2')
    ->get();

I use a subquery by using DB::raw("({$posts->toSql()}) as combined") and mergeBindings($posts) to ensure that bindings are properly merged. The where clause is then applied to the entire result set.

Astrophysics

Answer 6

To apply the where clause "->where('brand_id' , '=' , '2')" to both queries after the unionAll, you can enclose the entire union operation within a subquery. Then, you can apply the where clause to the outer query. Here's how you can do it:

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

Cryogenics

Answer 7

Don't use union queries for just one. use where clasue to entire union result. here is example:

$notifications = DB::table('notifications')->select('uuid', 'brand_id');

$posts = DB::table('posts')->select('uuid', 'brand_id')->unionAll($notifications);

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

Pirouette