CODEWITHSUNDEEP
laraveleloquentwhere-clauserelationshiphas-many
pantech123
pantech123

Reputation: 3

Laravel | Fetch query results by matching multiple where conditions on multiple columns

I have two tables , users and user_skills Schema Design. Each user can have multiple skills where there are experience in number of years associated with each skills .

users

id name email age
1 A a@mail 15
2 C c@mail 16
3 D d@mail 17

user_skills

id user_id skill_id experience
1 1 1 3
2 1 2 5
3 2 1 2
4 3 1 5
5 3 2 2

I want to fetch the users who have skill 1 with experience between 1 to 5 and skill 2 with experience greater than 3 .

I already tried with has many relationship ,

$min=1;
$max=5; 
$skillId=1; 
$min2=3;
$max2='';
$skillId2=2;

$searchQuery=$searchQuery
                ->whereHas('getSkill', function ($query) use ($min, $max, $skillId, $min2, $max2, $skillId2) {
                    $query =  $query->where('skill_id', $skillId);
                    if (!empty($max) && !empty($min)) {
                        $query=$query->whereBetween('experience', [$min, $max]);
                    } }
                    
                    $query =  $query->where('skill_id', $skillId2);
                    if (!empty($min2) && !empty($max2)) {
                        $query=$query->where('experience','>', $min2);
                    } }
                
                );

here getSkill is my relationship with has many . The query does not give any error but yields no result . If i don't use the second where clause i get my results .

Upvotes: 0

Views: 51

Answers (1)

Nehal
Nehal

Reputation: 1022

You are using $query variable twice in a same code block. Instead of single ->whereHas you can try following.

$searchQuery = $searchQuery
    ->whereHas('getSkill', function ($query) use ($min, $max, $skillId) {
        $query =  $query->where('skill_id', $skillId);
        if (!empty($max) && !empty($min)) {
            $query = $query->whereBetween('experience', [$min, $max]);
        }
    })
    ->whereHas('getSkill', function ($query) use ($min2, $skillId2) {
        $query =  $query->where('skill_id', $skillId2);
        if (!empty($min2)) {
            $query = $query->where('experience','>', $min2);
        }
    });

Upvotes: 0

Related Questions