How to count 2 level relationship pivot table

I want to count how many users contest has. Here is my db

My Contest model

protected $fillable = [
        'name',
        'from',
        'until',
        'description',
    ];

public function vacancies()
    {
        return $this->hasMany(Vacancy::class);
    }

My Vacancy model

public function contest()
    {
        return $this->belongsTo(Contest::class);
    }

public function users()
    {
        return $this->belongsToMany(User::class, 'user_vacancy');
    }

My User model

public function vacancies()
    {
        return $this->belongsToMany(Vacancy::class, 'user_vacancy');
    }

My Controller

public function index()
    {
        $contests = Contest::with('vacancies.users')
            ->get();

        return view('contests.index', compact('contests'));
    }

My blade:

@foreach($contests as $contest)
                                    <tr>
                                        <td>1</td>
                                        <td>{{ $contest->name }}</td>
                                        <td>{{ date('d.m.Y', strtotime($contest->from)) }}</td>
                                        <td>{{ date('d.m.Y', strtotime($contest->until)) }}</td>
                                        <td>{{ $contest->vacancies()->count() }}</td>
                                        {{-- TODO: Topshirilgan xujjatlar sonidan kelib chiqib Nomzodlar soni shaklanadigan qilish kerak --}}
                                        <td>{{ $contest->users()->count() }}</td>
                                        <td>75 ta</td>
@endforeach

tisuchi

2 years ago

Level 70

@azizbek29 Try this:

In your Vacancy model:

public function users()
{
    return $this->hasManyThrough(User::class, Vacancy::class);
}

Update your index model:

public function index()
{
    $contests = Contest::with('vacancies')->get();
    return view('contests.index', compact('contests'));
}

In the view:

@foreach($contests as $contest)
    <tr>
        <td>1</td>
        <td>{{ $contest->name }}</td>
        <td>{{ date('d.m.Y', strtotime($contest->from)) }}</td>
        <td>{{ date('d.m.Y', strtotime($contest->until)) }}</td>
        <td>{{ $contest->vacancies->count() }}</td>
        <td>{{ $contest->users->count() }}</td>
        <td>75 ta</td>
    </tr>
@endforeach

2 likes

s4muel

2 years ago

Best Answer

Level 50

since the users() relationship on Vacancy is belongsToMany, i am not sure the suggestion with hasManyThrough from above will work, but sure give it a try!

if it doesnt, i would to this:

public function index()
    {
        $contests = Contest::with(['vacancies' => fn($builder) => $builder->select('id', 'contest_id')->withCount('users')])
            ->get();

        return view('contests.index', compact('contests'));
    }

and in your blade

<td>{{ $contest->vacancies->sum('users_count') }}</td>

note: you eager load the vacancies relation as well as related user_counts, but by using the ->select('id', 'contest_id') you dont select all columns from vacancies and save a bit on data pulled from DB, feel free to tweak if you need to

3 likes

azizbek29

2 years ago

Level 1

@s4muel yeah hasManyThrough didn't work. But your answer is working, thanks.

1 like

Please or to participate in this conversation.