Join and SUM in Laravel ELoquent

Question

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.

Answer 1

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

Answer 2

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