Laravel DB table - binding related information. Is there a better way here?

Question

I have tables "Seminars", "Users" and "UserSeminarAttendance".

Users can sign up for a seminar. They can take multiple tickets (think user + child + another parent etc).

The "UserSeminarAttendance" table stores the seminar ID, user ID who made the purchase, + number of tickets the user wants.

I want to know how many tickets have been taken for a seminar, and apply this sum as a virtual parameter in the Seminar object. This is easy enough:

$tickets = 0;
$attendance_rows = UserSeminarAttendance::where( "seminar_id", $seminarid )->get();
foreach($attendance_rows as $row){
   $tickets += (int) $row->num_tickets;
}
$s->numAttendances = $tickets;

..this works. But is there a better way using a model relation? Ideally I always want there to be a ticket count in the seminar request, but I don't want to perform the extra DB request + a foreach every time.

Answer 1

Assuming your models are set up like this:

class Seminar extends Model
{
    // Define the relationship
    public function attendance()
    {
        return $this->hasMany(UserSeminarAttendance::class, 'seminar_id');
    }

    // Define an accessor to get the sum of tickets
    public function getNumAttendancesAttribute()
    {
        return $this->attendance->sum('num_tickets');
    }
}

UserSeminarAttendance model:

class UserSeminarAttendance extends Model
{
    // Your model definition
}

Now, when you retrieve a Seminar instance, you can access the total number of tickets directly without explicitly querying the database or using a foreach loop:

$seminar = Seminar::find($seminarId);
$numAttendances = $seminar->numAttendances;

Laravel will automatically perform the necessary SQL query to get the sum of tickets associated with that seminar, making your code cleaner and more efficient.