Home / Questions / 548598
Accepted
jmrufo
Asked: 2022-07-27 23:22:10 +0800 CST 2022-07-27 23:22:10 +0800 CST

Count records from related tables grouped by pivot table field

  • 772

I request your help to perform the following query. I have 3 related tables (courses, course_user, users) with the following fields:

  1. courses table : id, denomination, hours.
  2. course_user table : id, course_id, user_id, inscription_type (this field can have two possible values ​​HOLDER or RESERVE.
  3. Users table : id, nif, name, surname.

In the models I have created the corresponding relationship functions:

Course model :

public function courseUsers()
{
    return $this->belongsToMany(User::class)->withPivot('inscription_type')->withTimestamps();
}

User model :

public function userCourses()
{
    return $this->belongsToMany(Course::class)->withPivot('inscription_type')->withTimestamps();
}

What I need to obtain is the relationship of all the existing courses with the total enrollment per course, also adding two counter fields where I know the breakdown of the total enrollment obtained per course, indicating how many of those enrollments have the value HOLDER in the inscription_type field and another that indicates how many have the RESERVE value .

The result should be something like this:

id Grade Hours Registration No. Number of Holders No. Reservations
1 Excel twenty 150 125 25

I have created the following join:

$courses = DB::table('courses') ->leftJoin('course_user', 'courses.id', '=', 'course_user.course_id') ->select('courses.*', DB::raw('count(*) as TotalMatriculas')) ->groupBy('courses.id') ->get();

With this I get all the courses with the total number of enrollments that each one has, but I don't know how to obtain now the count of the enrollments that are of the OWNER type and those that are of the RESERVE type.

I appreciate your help.

All the best.

php

1 Answers

  1. Best Answer

    You could use IF()and SUM()like this:

    SUM(IF(course_user.inscription_type = "TITULAR", 1, 0)) AS TotalTitulares,
SUM(IF(course_user.inscription_type = "RESERVA", 1, 0)) AS TotalReservas,

    Example:

    $courses = DB::table('courses') 
    ->leftJoin('course_user', 'courses.id', '=', 'course_user.course_id') 
    ->select('courses.*', DB::raw('count(*) as TotalMatriculas'), DB::raw('SUM(IF(course_user.inscription_type = "TITULAR", 1, 0)) AS TotalTitulares'), DB::raw('SUM(IF(course_user.inscription_type = "RESERVA", 1, 0)) AS TotalReservas')) 
     ->groupBy('courses.id') ->get();
    • 0