faridsilva's avatar

Refactoring an sql sentence to eloquent style

Hello, I'm refactoring an old e-commerce site from plain PHP to Laravel 5.5. I have a list of products by categories and subcategories, which need to be filtered by brands.

I have an opportunities table which depends on products table which is related to a brands table, too. Tables have foreign keys and relations are working fine when lazy loading queries. So, If I call something like {{ $opportunity->product->brand->name }} it displays correct brand name.

Actually my sql query to obtain all pertinent brands in a given list, is such as:

SELECT brands.id, brands.name from brands join products on products.brand_id = brands.id join opportunities on opportunities.`product_id` = products.id where opportunities.`status`=1 GROUP BY brands.name

This query is working as expected, but I wish to recode it in an eloquent style, so I need your help. Thank you in advance.

0 likes
7 replies
faridsilva's avatar

Thank you for your input Mina, as I expressed before, my relations are working fine, but how can I instrument that with "distinct brand" or "group by brand" is what I can't still figured out.

kevinbui's avatar

I assume that you have a Brand model, you might rewrite the query to something like:

$brands = Brand::select('brands.id', 'brands.name')
    ->join('products', 'products.brand_id', '=', 'brands.id')
    ->join('opportunities', 'opportunities.product_id', '=', 'products.id')
    ->where('opportunities.status', 1)
    ->groupBy('brands.name')
    ->get();

An alternative is to use whereHas and relationships. From your query I assume that a brand has many products and a product has many opportunities:

$brands = Brand::select('brands.id', 'brands.name')
    ->whereHas('products.opportunities', function ($query) {
        $query->where('status', 1);
    })->get();
walidabou's avatar

better way is to make relationships between your models and use eager loading as MINA mentioned instead of using join method frequently.

1 like
kevinbui's avatar

Good point. I have updated my answer accordingly.

faridsilva's avatar

Thank you @kevinbui, @walidabou for your clarifications, that are very useful to me. Join method is the first alternative I've tested because it is close to my original query, and it works pretty good. Now I'll give a try to whereHas idea.

I was wondering if it could be done using collection->filter in order to reduce mysql queries, because when I select brands I already have queried for opportunities list.

$results = Opportunity::isAble()->where('category_id', $cat->id)->with('product')->get(); here, isAble is a scope that test for status = 1;

Of course, as you guessed, brands can have many products and products can have many opportunities.

Thank you again for your interest.

kevinbui's avatar

As I understand, it would be more performant if we handle everything in one query.

Please or to participate in this conversation.