NightMICU NightMICU - 6 months ago 13
PHP Question

Tricky Eloquent query

I've been struggling to figure out a table relationship for the past two days, I'm sure that the solution is simple but it is alluding me.

Four tables/models are involved (including a pivot table):


  1. Skill
    - skills table. A
    Skill
    belongs to a
    SkillGroup
    and belongs to many
    Candidates
    (candidate_skill pivot table)

  2. Candidate
    - candidates table. Contains personal information on a candidate, not terribly related to the issue.

  3. SkillGroup
    - skill_groups table. Each Skill Group has many Skills.



I want to be able to retrieve Skill objects possessed by a
Candidate
grouped by the
SkillGroup
. For example:

[
{
"id": 1,
"title": "Information Technology (Skill Group)",
"slug": "information-technology",
"created_at": "2016-05-07 23:58:23",
"updated_at": "2016-05-07 23:58:23",
"skills": [
{
"id": 1,
"title": "Web Development (Skill)",
"slug": "web-development",
"description": "Web developers primarily focus on the back-end of websites",
"created_at": "2016-05-07 23:58:55",
"updated_at": "2016-05-07 23:58:55",
"skill_group_id": 1,
"candidates": [
{
"first_name": "John (Candidate)",
"last_name": "Smith",
"pivot": {
"skill_id": 1,
"candidate_id": 6
}
}
]
}
]
}
]


This is easy to accomplish with the following code, but I want to retrieve only results (
SkillGroup
->
Skills
->
Candidate
) for a specific candidate.

\App\SkillGroup::with('skills.candidates')->get();


I have tried the following (and everything else I can think of), the candidate_id does not seem to affect the query - I see skills that are not possessed by the given candidate.

\App\SkillGroup::with(['skills.candidates' => function($query) {
$query->whereCandidateId(6);
}])->get();


Any help would be greatly appreciated, thanks!

EDIT

Thanks to @Giedrius Kiršys, I was able to come up with the following:

\App\SkillGroup::with(['skills.candidates' => function($query) {
$query->wherePivot('candidate_id', 8)->addSelect('candidates.id', 'first_name', 'last_name');
}])->whereHas('skills.candidates', function($q) {
$q->whereCandidateId(8);
})->get();


This only retrieves
SkillGroup
results with
Skill
s with a
Candidate
with the given ID.

Answer

You want to query by pivot table attribute, but You are querying by candidates.candidate_id attribute.

You can do it like this:

\App\SkillGroup::with(['skills.candidates' => function($query) {
     $query->wherePivot('candidate_id', 6);
}])->get();
Comments