Question 271254 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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');
}
    Login or Signup to reply.
  2. 0

    update the candidates relationship.

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

    Controller

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

    You can also use withCount with query but I kept your original code.

    blade

    @foreach($contests as $contest)
    <tr>
        <td>{{ $loop->iteration }}</td> {{--<td>1</td> use iteration instead--}}
        <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->candidates->count() }}</td>
        <td>75 ta</td>
    </tr>
@endforeach
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search