Laravel Eager Loading tickets from DB

Question

So I have the following relationship in my Model:

Events Model:

class Events extends Model
{
 use CrudTrait;
 use HasFactory;
 use Sluggable;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'title',
 'url',
 'start',
 'end',
 'category',
 'level',
 'bookable',
 'additional_info',
 'repeats',
 'duration',
 ];
 /**
 * The attributes that should be cast to native types.
 *
 * @var array
 */
 protected $casts = [
 'id' => 'integer',
 'start' => 'datetime',
 'end' => 'datetime',
 'category' => 'string',
 'level' => 'integer',
 'boookable' => 'boolean',
 'repeats' => 'integer',
 'duration' => 'integer',
 ];
 public function tickets()
 {
 return $this->hasMany(Tickets::class, 'event_id');
 }
}

the function public function tickets() above is what I am talking about.

Tickets Model:

class Tickets extends Model
{
 use CrudTrait;
 use HasFactory;
 public $fillable = [
 'title',
 'price',
 'min',
 'max',
 'students',
 'event_id'
 ];
}

then I show the ticket pricings on the frontend in a blade:

@foreach($events as $event)
 <div
 class="group-hover:hidden">
 {{ $event->tickets->count() > 1 ? 'Starting at:' : '' }}
 @if ($event->tickets->min(fn($row) => $row->price) === '0.00')
 Free
 @else ($event->tickets->min(fn($row) => $row->price) !== 0)
 {{$event->tickets->min(fn($row) => $row->price)}} €
 @endif
 </div>
@endforeach

Controller:

public function showEvents($course)
{
 // function for the /events/{$course} pages
 [$view, $id] = match ($course) {
 default => ['error', 'error'],
 // URL Request from ($course) then the set the $view and $id
 'loremimpsum' => ['pages.course.loremimpsum', '2'],
 'impsumlorem' => ['pages.course.impsumlorem', '3'],
 'loremlorem' => ['pages.course.loremlorem', '4'],
 'impsumimpsum' => ['pages.course.impsumimpsum', '5'],
 'looremloorem' => ['pages.course.looremloorem', '6'],
 'impsimps' => ['pages.course.impsimps', '7'],
 'loremloremlorem' => ['pages.course.loremloremlorem', '8'],
 };
 try {
 return view($view, [
 'events' => Events::query()
 ->orderBy('title')
 ->orderBy('start')
 ->where('category', $id)
 ->where('start', '>', now())
 ->get()
 ]);
 } catch (InvalidArgumentException) {
 return Redirect::route('all-events')->with('error', 'You were redirected! This event was not found.');
 }
}

Route:

Route::get('/events/{course}', [EventsController::class, 'showEvents']);

first I check if there are more than two tickets on one event, then it will show Starting at. Then I check if the price on the ticket is 0, if this is the case I set the text on the button to Free. After that I check if the price is not 0 and whow the real price from the DB.

The question:

How would one Eager Load these tickets and are there further optimizations? There are several thousand tickets in the DB.

The documentation from Laravel for Eager Loading only show an example with belongsTo not with hasMany. And StackOverflow has no answers with hasMany and a foreignKey

Answer 1

Review

Readability is good though model naming could be better

As someone who has used laravel for many years the code is simple to read. One critique would be that typically models are named in a singular fashion - e.g. Event instead of Events, even though the database table name may be named in a plural manner.

Template likely has a flaw

There appears to be a mistake in the template:

@else ($event->tickets->min(fn($row) => $row->price) !== 0)

Shouldn't that be an elseif?

Question Response

How would one Eager Load these tickets and are there further optimizations? There are several thousand tickets in the DB.

The section in the documentation above the Section Eager loading is Aggregating Related Models discusses eager loading aggregates for the hasMany relations. The One to Many section sets up the comments relationship for the Post model:

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
 
class Post extends Model
{
 /**
 * Get the comments for the blog post.
 */
 public function comments()
 {
 return $this->hasMany(Comment::class);
 }
}

¹

The first section about aggregating models demonstrates getting the count of comments:

Counting Related Models

Sometimes you may want to count the number of related models for a given relationship without actually loading the models. To accomplish this, you may use the withCount method. The withCount method will place a {relation}_count attribute on the resulting models:

use App\Models\Post;
 
$posts = Post::withCount('comments')->get();
 
foreach ($posts as $post) {
 echo $post->comments_count;
}

²

So to get the count of tickets the withCount() method can be used. Also, the call to query() can be removed since methods like that as well as Model::where() and Model::orderBy() return an instance of \Illuminate\Database\Query\Builder:

Events::withCount('tickets')
 ->orderBy('title')
 ->orderBy('start')
 ->where('category', $id)
 ->where('start', '>', now())
 ->get()

Then each Event model will have an attribute tickets_count that can be used to get the count of tickets instead of $event->tickets->count().

The Other Aggregate Functions section mentions the withMin method.

In addition to the withCount method, Eloquent provides withMin, withMax, withAvg, withSum, and withExists methods. These methods will place a {relation}_{function}_{column} attribute on your resulting models:

use App\Models\Post;
 
$posts = Post::withSum('comments', 'votes')->get();
 
foreach ($posts as $post) {
 echo $post->comments_sum_votes;
}

So to get the minimum price of the tickets for each event a call to withMin() can be added:

Events::withCount('tickets')
 ->withMin('tickets', 'price')
 ->orderBy('title')
 ->orderBy('start')
 ->where('category', $id)
 ->where('start', '>', now())
 ->get() 

Then each Event model should have an attribute tickets_min_price that can be used instead of $event->tickets->min(fn($row) => $row->price).

Thus instead of a query to get the events plus one query for each event, there is a single query like:

select "events".*, 
 (select count(*) from "tickets" where "events"."id" = "tickets"."event_id") as "tickets_count", 
 (select min("tickets"."price") from "tickets" where "events"."id" = "tickets"."event_id") as "tickets_min_price" 
from "events" 
where "category" = '7' and "start" > '2024-01-09 23:55:23' 
order by "title" asc, "start" asc

• Nifty Matrix: First of all, thank you very much for this explanation! The trick with the tickets_min_price and tickets_count is very smart. This did indeed fix the issue I had. :)