How do I write an eloquent query which joins, merges or unions these 2 queries into 1?

Question

// 2 eloqent collections merged

$publicCategories = Category::where('menu', '=', 1)
    ->where('display_scope', 1)
    ->orderBy('parent_id')
    ->get();
$privateCategories = Category::where('menu', '=', 1)
    ->whereIn('id', $ids)
    ->orderBy('parent_id')
    ->get();
$categories = $publicCategories->merge($privateCategories);

// This query above does these 2 MySQL queries which are duplicated.

The result from this is correct, however, requires 2 queries.
How do I write an eloquent query which joins, merges or unions these 2 queries into 1?

Dilip Hirapara · Accepted Answer · 2020-02-17T05:15:49.567

2

Why you're getting it separately? You can use Orwhere for this.

$publicprivateCategories = Category::where('menu', '=', 1)
                        ->whereIn('id', $ids)
                        ->orWhere('display_scope', 1)
                        ->orderBy('parent_id')
                        ->get();

Update

$publicprivateCategories = Category::where('menu', '=', 1)
                        ->where(function($q) use($ids){
                             $q->whereIn('id', $ids)->orWhere('display_scope', 1);
                        })
                        ->where('id', '!=', 2)
                        ->orderBy('parent_id')
                        ->get();

By this, you'll get both(Public or private) categories.

edited Feb 17 '20 at 05:15

answered Feb 17 '20 at 05:01

Dilip Hirapara

14,810
3
27
49

Thanks I'll check it out. – Josh Feb 17 '20 at 05:02
This answer is correct. On top of this, I recommend you to have a look into `Query Optimisation` https://stackoverflow.com/a/60015685/5326191 – Digvijay Feb 17 '20 at 05:04
Very helpful for `Query Optimisation` I appreciated. – Dilip Hirapara Feb 17 '20 at 05:06
I had to add ->where('id', '!=', 2). Because the previous query is 2 separate queries. How do I do a subquery with eloquent? – Josh Feb 17 '20 at 05:09
Check Update for that. – Dilip Hirapara Feb 17 '20 at 05:16

How do I write an eloquent query which joins, merges or unions these 2 queries into 1?

1 Answers1