1

I've got a many-to-many relationship between a user and a match, and I have a availability column on the pivot table which is an enum:

'available', 'not-available', 'tbc'

<?p
...
class User extends Model

public function matches() {
  return $this->belongsToMany(Match::class)->withPivot('availability');
}

<?p
...
class Match extends Model

public function users() {
  return $this->belongsToMany(User::class)->withPivot('availability');
}
match_id user_id availability
4 2 available
4 1 tbc
4 3 available
4 5 tbc
4 4 not-available
... ... ...
4 4 not-available

I'm trying to get the count of the availabilities for the players for a given match.

So that I can return something like:

[
  "availability" => "available",
  "count" => 17,
],
[
  "availability" => "not-available",
  "count" => 12,
],
[
  "availability" => "tbc",
  "count" => 4,
]

I feel like I should be able to use a withCount when I eager load the users on a model, but I can't workout how to do it on the pivot data.

n8udd
  • 657
  • 1
  • 9
  • 30
  • Hmm, I don't know of a way to query that from the `Match` model, for example. `Match::with(['users'])->find(4)` would include them all, with their pivot, and you could then do a [Collection `groupBy()`](https://laravel.com/docs/9.x/collections#method-groupby), but it might just be simpler to do `DB::table('match_user')->select('availability', DB::raw('COUNT(user_id) AS count'))->groupBy('availability')->where('match_id', 4)->get()`. Do either of those work for your needs? – Tim Lewis Jul 22 '22 at 17:59
  • Does this answer your question? [Laravel Eloquent groupBy() AND also return count of each group](https://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group) – Salman Malik Jul 22 '22 at 18:03
  • Thanks both. That's what I've implemented until now, but I was hoping to do it using the Eloquent builder rather than DB so that I could use it in as a method in my `MatchBuilder` class which extends the `Builder`. – n8udd Jul 23 '22 at 11:27

0 Answers0