Fabio Sasso
Fabio Sasso

Reputation: 185

Laravel get all records from a table except the ones already associated in a pivot table

I have three tables:

Table departments:

|------|-------------|
|  id  |     name    |
|------|-------------|
|  1   | Department1 |
|  2   | Department2 |
|  3   | Department3 |
|------|-------------|

Table groups:

|------|--------|
|  id  |  name  |
|------|--------|
|  1   | Group1 |
|  2   | Group2 |
|------|--------|

Table department_group:

|------|---------------|---------------|
|  id  |    group_id   | department_id |  
|------|---------------|---------------|
|  1   |       1       |       1       |
|  2   |       2       |       2       |
|------|---------------|---------------|

I need to get all the records from the department table there are not associated with group_id 1 in the department_group table. So, in this example, I want Department2 and Department3 for Group1 and Department1 and Department3 for Group2.

I know how to write a MySql query for this, but there is a way to use Eloquent relationship?

Thank you in advance for your answer Fabio

EDIT: The models are:

class Group extends Model
{
    protected $table = 'groups';

    protected $fillable = [
        'name'
    ];

    public function departmentGroups() {
        return $this->hasMany(DepartmentGroup::class, 'group_id','id');
    }



class Department extends Model
{
    protected $table = 'departments';

    protected $fillable = [
        'name'
    ];

    public function departmentGroups() {
        return $this->hasMany(DepartmentGroup::class, 'department_id','id');
    }

Upvotes: 2

Views: 1082

Answers (1)

Christophe Hubert
Christophe Hubert

Reputation: 2951

You need the have the proper Many to Many relationship in place (https://laravel.com/docs/7.x/eloquent-relationships#many-to-many) and then you can easily use whereDoesntHave()

class Group extends Model
{
    ...
    public function departments() {
        return $this->belongsToMany(Department::class, 'department_group');
    }
}


class Department extends Model
{
    ...
    public function groups() {
        return $this->belongsToMany(Group::class, 'department_group');
    }

    $departmentsNotAssociated = Department::whereDoesntHave('groups', function($query) {
        $query->where('id', '=' , 1)
    })->get();

You can learn more about it in the documentation: https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-absence

Upvotes: 3

Related Questions