Reputation: 185
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
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