How to make a multiple join query with Eloquent ORM

Question

Long story "short", How can I execute the next MySql query using the Eloquent ORM approach?

Ps: I would like to use the multiple join option instead of multiple selects or subqueries that I know Eloquent ORM performs behind the curtains, I don't know if it's possible, to improve DB performance

select t.*,
       d.name,
       tt.name
from template t
         inner join template_type tt on t.id_template_type = tt.id_template_type
         inner join department d on t.id_department = d.id_department
         inner join user_department ud on d.id_department = ud.id_department
         inner join `user` u on ud.id_user = u.id_user
where u.id_user = 1;

Template table

| id_template | name    | id_department | id_template_type | content       |
|-------------|---------|---------------|------------------|---------------|
| 1           | temp 14 | 1             | 1                | some content  |
| 2           | temp 25 | 2             | 3                | other content |

Template type table

| id_template_type | name       | active |
|------------------|------------|--------|
| 1                | my type    | 1      |
| 2                | elses type | 1      |

Department table

| id_department | name         | active |
|---------------|--------------|--------|
| 1             | my dept      | 1      |
| 2             | another dept | 1      |

Pivot table department user

| id_user_department | id_user | id_department |
|--------------------|---------|---------------|
| 1                  | 2       | 1             |
| 2                  | 6       | 2             |
| 3                  | 6       | 3             |

User table

| id_user | name         |
|---------|--------------|
| 1       | My User      |
| 2       | Another User |

Template class

class Template extends Model
{
    protected $primaryKey = 'id_template';

    protected $table = 'template';

    public function departments()
    {
        return $this->belongsTo(Department::class);
    }

    public function types()
    {
        return $this->belongsTo(TemplateType::class);
    }
}

TemplateType class

class TemplateType extends Model
{
    protected $primaryKey = 'id_template_type';

    protected $table = 'template_type';

    public function templates()
    {
        $this->hasMany(Template::class, 'id_template_type', 'id_template_type');
    }
}

Department class

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

    protected $primaryKey = 'id_department';

    public function users()
    {
        return $this->belongsToMany(Users::class, 'user_department', 'id_department', 'id_user');
    }
}

User class

class User extends Model
{
    protected $table = 'user';

    protected $primaryKey = 'id_user';

    public function departments()
    {
        return $this->belongsToMany(Department::class, 'user_department', 'id_user', 'id_department');
    }
}

mihnsen · Accepted Answer · 2020-10-01 19:11:03Z

1

If you setup your relationship right. You can do something like this:

$templates = Template::with('type', 'department')
                     ->whereHas('department', function($query) use ($user_id) {
                         return $query->whereHas('users', function($user_query) use ($user_id) {
                             return $user_query->where('id', $id')
                         });
                     })

From department to user it should be many to many here

edited Oct 1, 2020 at 19:11

answered Oct 1, 2020 at 16:33

mihnsen

4254 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martín Cortés Over a year ago

I've updated the question with the models and relations I made, maybe I'm doing something wrong, thanks in advance.

mihnsen Over a year ago

First, you should change types and departments from Template to type and department, because of code convention, a template belong to a type, department.

Collectives™ on Stack Overflow

How to make a multiple join query with Eloquent ORM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related