Converting MySQL query to Laravel

Question

How I can convert this MySQL query to a Laravel query?

select *
from marques
where id in (select marque_id from products
             where category_id = 'valeur1' or category_id in (select id from categories
                                                              where parent_id = 'Valeur1'))

Your current query is convoluted, and it would help to see some sample data. There is probably an easier way of phrasing your query, and we should start there before hitting the Laravel code. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 16, 2018 at 9:17
You should try first. If you fail then ask here. So show us your code here as proven that you already tried. — Yasin Arafat
– Yasin Arafat, Commented Oct 16, 2018 at 9:18
you can use query builder like- DB::select( SELECT * FROM marques WHERE id IN ( SELECT marque_id FROM products WHERE category_id = :category_id OR category_id in (SELECT id FROM categories WHERE parent_id = :parent_id ) ), ['category_id' => 'valeur1', 'parent_id' => 'Valeur1'] ); — Manoj Singh
– Manoj Singh, Commented Oct 16, 2018 at 9:22

Tim Biegeleisen · Accepted Answer · 2018-10-16 09:35:21Z

1

I think your current query is equivalent to the following:

SELECT *
FROM marques m
LEFT JOIN products p
    ON m.id = p.marque_id
LEFT JOIN categories c
    ON p.category_id = c.id AND c.parent_id = 'Valeur1'
WHERE
    p.category_id = 'valeur1' OR
    c.id IS NOT NULL

Here is a rough guess at what your Laravel code might look like:

$res = DB::table('marques')
        ->join('products', 'marques.id', '=', 'products.marque_id')
        ->join("categories", function($join) {
            $join->on('products.category_id', '=', 'categories.id')
                 ->on('categories.parent_id', '=', 'Valeur1')
        })
        ->whereNotNull('categories.id')
        ->orWhere('products.category_id', '=', 'valeur1')
        ->select('*')
        ->get();

edited Oct 16, 2018 at 9:35

answered Oct 16, 2018 at 9:30

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Niklesh Raut Over a year ago

I think you made it correctly, thanks for your ans, I just guess you put whereNotNull('categories.id') extra to not get unwanted data

Tim Biegeleisen Over a year ago

@C2486 This was the most painful question of the day...I hope it's right too, I can't test it though.

Collectives™ on Stack Overflow

Converting MySQL query to Laravel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related