CODEWITHSUNDEEP
phpsqllaravelmodellaravel-5
Yahya Uddin
Yahya Uddin

Reputation: 28861

How to use "where not" in a Laravel Model Relationship?

I have a user table similar to:

=== users ===
id: int (PK)
name: string
is_coach: bool
...

and a coach request table similar to:

=== coach_requests ===
id: int (PK)
student_id: int(FK => users.id)
coach_id: int(FK => users.id)
...

I also have the corresponding Laravel models (i.e. User and CoachRequest).

In the User model, I wish to make a method such that given a specified user, return all users with is_coach = true, except:

For example consider the following sample data:

users

(1, "A", false)
(2, "B", true)
(3, "C", true)
(4, "D", true)
(5, "E", true)
(6, "F", true)

coach_requests

(1, 2, 3)
(2, 2, 4)
(3, 3, 2)
(4, 3, 6)
(5, 4, 5)
(6, 4, 6)
(7, 5, 6)
(8, 6, 5)
(9, 1, 4)

Now if I was user with:

How can I do this using Laravel?

So far all I have is this:

public function scopeOfFreeCoaches($query) {
    return $query->where([
        'is_coach' => true,
    ]);
}

So not much!

Many Thanks!

Upvotes: 0

Views: 3473

Answers (2)

Yahya Uddin
Yahya Uddin

Reputation: 28861

Thanks to @purpleninja raw query, I managed to figure out how to do this using Laravel:

public function getPotentialCoaches()
{
    return
        User::where('is_coach', true)
        ->where('id', '<>', $this->id)
        ->whereNotIn('id', function ($query) {
            $query->select('coach_id')
                ->from('coach_requests')
                ->where('student_id', $this->id);
        })
        ->get();
}

Upvotes: 3

purpleninja
purpleninja

Reputation: 374

This is a raw query on the top of my head:

SELECT u.id FROM users AS u
WHERE u.is_coach = true
AND u.id <> ’$userId’
AND u.id NOT IN(
    SELECT student_id FROM coach_requests
    WHERE coach_id = ’$userId’
)

Quickly done and not tested so you may have to change it a little.

Upvotes: 1

Related Questions