Question 335197 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to use Eloquent query builder in Laravel to get a count of related records in a Join

I am trying to use a count(..) to get the count value of matching records from a related table, using Laravel 8 (Eloquent). The idea is simply to count the number of tasks assigned to each user.

I already have some SQL that achieves the correct result (see near the bottom of the question), however I’m trying to determine an easy way to get leverage Laravel’s Eloquent query builder.

Here is the DB schema & some sample values:

create table user_tasks (
    id integer primary key,
    user_id integer not null
);

create table users (
    id integer primary key,
    name text not null
);

insert into user_tasks values (1, 1);
insert into user_tasks values (2, 2);
insert into user_tasks values (3, 1);
insert into user_tasks values (4, 1);
insert into user_tasks values (5, 1);
insert into user_tasks values (6, 2);

insert into users values (1, 'joe');
insert into users values (2, 'sally');
insert into users values (3, 'bob');

The output is:

1, 'joe', 4
2, 'sally', 2
3, 'bob', 0

This is the SQL that correctly achieves this result:

SELECT id, users.name, COALESCE(subquery.task_count, 0) task_count
FROM users
LEFT JOIN (
  SELECT user_id id, COUNT(*) as task_count 
  FROM user_tasks
  GROUP BY 1
) subquery USING (id)

The question at this point is: how do I put this in Laravel’s Eloquent builder?

This is what I am starting with. I don’t mind using "DB:Raw" to make this work.

User->select(['id', 'users.name', 'COALESCE(subquery.task_count, 0) task_count'])
    ->leftJoin(DB::raw("SELECT id, users.name, COALESCE(subquery.task_count, 0) task_count
FROM users GROUP BY 1"))
    ->get();

// where does "subquery using id" fit into this?

To summarize: I’m trying to figure out a way to use Laravel Eloquent query build to get the same result as the SELECT SQL statement above.

Tags:

2

Answers


  1. 0

    You don’t need that fancy query to get the results. Counting relationships is all you need:

    class User extends Model
{
    public function tasks()
    {
        return $this->hasMany(Task::class);
    }
}

class Task extends Model
{
    protected $table = 'user_tasks';
}

$users = User::withCount('tasks')
    ->get();

    That will do the work.

    This is not necessary, but if you still want to convert to above DB query to Eloquent, you might try subquery joins:

    use IlluminateDatabaseQueryJoinClause;

$numberOfTasks = Task::selectRaw('user_id id, COUNT(*) as task_count')
    ->groupByRaw('1');

return User::selectRaw('id, users.name, COALESCE(subquery.task_count, 0) task_count')
    ->join($numberOfTasks, 'subquery', function (JoinClause $join) {
        $join->on('users.id', '=', 'subquery.user_id');    
    });
    Login or Signup to reply.
  2. 0

    You only need to add one relationship inside User model for count and that’s it.

    class User extends Model {
    public function userTasks()
    {
        return $this->hasMany(UserTask::class, 'user_id');
    } 
}

    Now your query eloquent should be :

    $result = User::select(['id', 'name'])
    ->withCount(['userTasks as task_count'])
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search