CODEWITHSUNDEEP
laravellaravel-5eloquent
Creatix
Creatix

Reputation: 111

How to write this raw query in Eloquent

I have a sql query, and i want to know if it can be written in eloquent.

There are two tables Brands and Products.

I am trying to get the brands which are in particular category of products

Below is how it looks currently

\DB::select('select * FROM brands WHERE id in ( select brand_id from products WHERE category_id IN (220, 222, 223) GROUP by brand_id )');

The above is working, i want help on converting it to eloquent.

Upvotes: 0

Views: 83

Answers (3)

Meister
Meister

Reputation: 84

Considering that you have your model Brand and Products to do it you will do: $brands = Brand::with(['category'=>function($query){ $query->select('category_id')}])->whereIn('category_id',[220, 222, 223])->get();

Upvotes: 0

d3jn
d3jn

Reputation: 1410

Assuming you've already prepped Brand and Product models, you can specify closure to include subquery inside your WHERE IN clause like this:

Brand::whereIn('id', function ($query) {
    $query
        ->select('brand_id')
        ->from('products')
        ->whereIn('category_id', [220, 222, 223])
        ->groupBy('brand_id');
})->get();

Upvotes: 2

party-ring
party-ring

Reputation: 1871

You want to have your models set up so you can query them using Eloquent; when you have those you can do:

$categoryIds = [220, 222, 223];

$brandIds = Product::whereIn('category_id', $categoryIds)
                ->groupBy('brand_id')
                ->pluck('brand_id');

$yourQuery = Brand::whereIn('id', $brandIds)->get();

Upvotes: 0

Related Questions