How to get count from HasManyThrough relationship with pivot table in Laravel

Question

I want to count how many candidates contest has.

I have 4 tables:

CONTEST

• id
• name
• from
• until

VACANCY

• id
• contest_id
• name
• description

USER

• id
• name

pivot table:

USER_VACANCY

• id
• user_id
• vacancy_id

My Contest model

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

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

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

My Vacancy model

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

public function candidates()
    {
        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.candidates')
            ->get();

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

I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.vacancy_id' in 'on clause'
select
  count(*) as aggregate
from
  `users`
  inner join `vacancies` on `vacancies`.`id` = `users`.`vacancy_id`
where
  `vacancies`.`contest_id` = 2

When I do this:

@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->candidates()->count() }}</td>
                                        <td>75 ta</td>
@endforeach


  [1]: https://i.stack.imgur.com/cc57S.png
  [2]: https://i.stack.imgur.com/n3rOV.png

Answer 1

Kindly update Contest model function of relationship candidate to the following

public function candidates()
{
   return $this->hasManyThrough(User::class, UserVacancy::class, 'vacancy_id', 'id', 'id', 'user_id');
}

Answer 2

Define your models and relationships:

Contest Model:

class Contest extends Model
{
   public function vacancies()
   {
        return $this->hasMany(Vacancy::class);
   }
        
   public function candidates()
   {
        return $this->hasManyThrough(User::class, Vacancy::class, 'contest_id', 'user_id');
   }
}

Vacancy Model:

class Vacancy extends Model
{
    public function contest()
    {
        return $this->belongsTo(Contest::class);
    }
        
    public function candidates()
    {
        return $this->belongsToMany(User::class, 'user_vacancy');
     }
}

User Model:

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

You can then query your Contest model to retrieve the count of candidates for each contest:

$contests = Contest::withCount('candidates')->get();

foreach ($contests as $contest) {
        echo "Contest: {$contest->name}, Candidates Count: {$contest->candidates_count}\n";
}