Filament•7mo ago

Displaying relation count in a column

Hi, I have an event table, a users table and a role table, they're linked through a pivot table named event_user_roles. This represents, what role a user is assigned for a given event. I have relations set up properly in the models. I would now like to display the number of users with a certain role in a upcomingEvents widget. How do I do that? The corresponding eloquent query would be:

$event->users()->join('roles', 'event_user_roles.role_id', '=', 'roles.id')
  ->select('events.id', \Illuminate\Support\Facades\DB::raw('SUM(if((roles.role = "kitchen"), 1, 0)) AS kitchen_count'))
  ->get();

$event->users()->join('roles', 'event_user_roles.role_id', '=', 'roles.id')
  ->select('events.id', \Illuminate\Support\Facades\DB::raw('SUM(if((roles.role = "kitchen"), 1, 0)) AS kitchen_count'))
  ->get();

How does that translate into my widget column?

10 Replies

Dennis Koch•7mo ago

Widget column means you are using a Table Widget? Isn't that just TextColumn::make('kitchen_count')?

jak888OP•7mo ago

How would it know what the kitchen_count is? I've now added a kitchen() relation to the event model, which returns all kitchen members to me. How can I count them?

Dennis Koch•7mo ago

Because you provide it via AS kitchen_count? I thought you added this to your table ->query() Or ->modifyQueryUsing()

jak888OP•7mo ago

Oh, OK. The query I provided, is what I do in this situation for my user facing code Still trying to understand what goes where in filament I'll look into modifyQueryUsing I'm lost My query returns all kitchen staff. Which I can display in a column as 'kitchen'. How do I just count all the entries there?

LeandroFerreira•7mo ago

$table
    ->modifyQueryUsing(fn (Builder $query) => $query->withCount([
        'roles as kitchen_count' => fn (Builder $query) => $query->whereRole('kitchen')
    ]))

$table
    ->modifyQueryUsing(fn (Builder $query) => $query->withCount([
        'roles as kitchen_count' => fn (Builder $query) => $query->whereRole('kitchen')
    ]))

Tables\Columns\TextColumn::make('kitchen_count')

Tables\Columns\TextColumn::make('kitchen_count')

jak888OP•7mo ago

Thanks It didn't quite work like that, complaining that it couldn't find the event_roles.role column. I had to join it. For my understanding: Rather than build the query there, would it be possible to use the kitchen() relation I already have in the query? like, the collection that is the result of that query already has all kitchen entries I've tried $query->withCount(['kitchen as kitchen_count']), but it complains that the role column doesn't exist again. There is also a possibility that I got the relation wrong to begin with

public function kitchen(): BelongsToMany
    {
        return $this->belongsToMany(User::class, 'event_user_roles')->join('event_roles', 'event_user_roles.role_id', '=', 'event_roles.id')->where('event_roles.role', 'ktichen')->withTimestamps();
    }

public function kitchen(): BelongsToMany
    {
        return $this->belongsToMany(User::class, 'event_user_roles')->join('event_roles', 'event_user_roles.role_id', '=', 'event_roles.id')->where('event_roles.role', 'ktichen')->withTimestamps();
    }

LeandroFerreira•7mo ago

Not sure. I would use modifyQueryUsing to achieve it. Maybe the laravel docs ref can help you.. https://laravel.com/docs/11.x/eloquent-relationships#counting-related-models

Laravel - The PHP Framework For Web Artisans

Laravel is a PHP web application framework with expressive, elegant syntax. We’ve already laid the foundation — freeing you to create without sweating the small things.

LeandroFerreira•7mo ago

Did you try $query->join(...)->withCount([... ?

jak888OP•7mo ago

similar errors. Also, my point was more along the lines of: Why do I have to join this again, when my relation already does that? It knows and uses the relation in the ->withCount('kitchen as kitchen_count'), because if I comment out the relation in the model it says that it's not there. or if there was another way of simply telling the count to count whatever is in there. I tried ->counts() but that doesn't see the role either. Anyway, I now have something that works that I'm never going to touch again, so that's fine. Just thought I could get to 'elegant' Thanks for sticking with me! Have a good one!

LeandroFerreira•7mo ago

I proposed using the relationship for counting, but eager loading would actually be more efficient in reducing this operation...

Gaming

Programming

Displaying relation count in a column