Join and SUM in Laravel ELoquent

user_3892606 0
Viewed 20702

Database Structure is as follows:

Table: users

id | name | email | password

Table: analytics

id | user_id(fk) | article_id(fk) | revenue | date | clicks

Now I want the list of all users with the sum of their own revenue from these two Tables.

What I tried is as follows:

$users = User::select('users*', 'analytics.*', 'SUM(analytics.revenue)')
         ->leftJoin('analytics', 'analytics.user_id', '=', 'users.id)
         ->where('analytics.date', Carbon::today()->toDateString())
         ->get();

But it is throwing me error.

eloquent laravel mysql
3 Answers

This maybe a bit late. Maybe will help someone else.

You need to include every select columns in groupBy. In your case:

$users = User::select('users.id', 'users.name', 'users.password', 'analytics.date', 'analytics.clicks', 'SUM(analytics.revenue) as revenue')
     ->leftJoin('analytics', 'users.id', '=', 'analytics.user_id')
     ->where('analytics.date', Carbon::today()->toDateString())
     ->groupBy('users.id', 'users.name', 'users.password', 'analytics.date', 'analytics.clicks') 
     ->get();
user_4536680
Related