How can I convert mysql to laravel eloquent left join?

Question

Mysql query like this :

SELECT a.id, a.name, b.TotalMeal, c.TotalCollection
FROM users a
LEFT JOIN (
    SELECT user_id, SUM( breakfast + dinner + lanch ) AS TotalMeal
    FROM meals
    GROUP BY user_id) b ON a.id = b.user_id
LEFT JOIN (
    SELECT user_id, SUM( amount ) AS TotalCollection
    FROM collections
    GROUP BY user_id) c ON a.id = c.user_id
LIMIT 0, 30

I want to convert it to Laravel eloquent, but I'm confused. I have Three tables. eg - users( id, name,email ..), meals( user_id, breakfast,lanch,dinner) and collections(user_id, amount) .

You should use relations if you don't want to use joins. If you need joins, then this is the correct way.enter link description here — user11324665
– user11324665, Commented Apr 7, 2019 at 15:13

Namoshek · Accepted Answer · 2019-04-07 15:24:44Z

0

There is a little hack that uses withCount() to select sums of related tables which can be used in your scenario:

User::query()
    ->select([
        'id',
        'name',
    ])
    ->withCount([
        'meals as total_meals' => function ($query) {
            $query->select(DB::raw('SUM(breakfast + dinner + lunch)'));
        },
        'collections as total_collections' => function ($query) {
            $query->select(DB::raw('SUM(amount)'));
        },
    ])
    ->get();

In this query, withCount will perform the joins for you.

answered Apr 7, 2019 at 15:24

Namoshek

6,5562 gold badges21 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How can I convert mysql to laravel eloquent left join?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related