CODEWITHSUNDEEP
mysqllaraveleloquent
Siddharth
Siddharth

Reputation: 1769

Join and SUM in Laravel ELoquent

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.

Upvotes: 11

Views: 26570

Answers (3)

Amirul
Amirul

Reputation: 582

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();

Upvotes: 6

izupet
izupet

Reputation: 1559

$users = User::select(\DB::raw('users.*, SUM(analytics.revenue) as revenue'))
         ->leftJoin('analytics', 'analytics.user_id', '=', 'users.id')
         ->groupBy('users.id')
         ->get();

Upvotes: 4

Vipul
Vipul

Reputation: 941

You may try this 

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

Upvotes: 6

Related Questions