raw sql query

Hello I have employees table and emplyees_departements table and departements table employees table structure id name etc...

emplyees_departements table structure id emp_id department_id

departments table structure id name etc...

i need a query using raw sql to get me this format (query to get all employess and each of them has a list of departments he works at)

[
	[
			emp_id => test1,
			emp_departments => [
												[
														department_id => 1,
														department_name => dept_1
												],
												[
														department_id => 2,
														department_name => dept_2
												],
												]
	],
[
			emp_id => test2,
			emp_departments => [
												[
														department_id => 1,
														department_name => dept_1
												],
												[
														department_id => 2,
														department_name => dept_2
												],
												]
	]
]

automica

3 years ago

Level 54

can you reformat your data using three backticks before and after your codeblock?

1 like

JhonD

3 years ago

Level 1

@automica please check it now, thanks in advance

automica

3 years ago

Level 54

you should do this eloquent and then format the result.

to follow laravel naming conventions you should have:

Employee model -> uses 'employees' table -> primary key field = id
Department model -> uses departments table -> primary key field = id
join table employee_department -> fields employee_id, department_id

You should define

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

and

// Department
public function employees
{
return $this->belongsToMany(Employee::class);
}

this will allow you to do the following:

$employees = Employee::with('departments')->get();

which returns:

 [
            "id" => "test1",
            "departments" => [
                [
                    "id" => 1,
                    "name" => "dept_1"
                ],
                [
                    "id" => 2,
                    "name" => "dept_2"
                ],
            ]
        ],
        [
            "id" => "test2",
            "departments" => [
                [
                    "id" => 1,
                    "name" => "dept_1"
                ],
                [
                    "id" => 2,
                    "name" => "dept_2"
                ],
            ]
        ]

https://webdevetc.com/blog/laravel-naming-conventions/#section_naming-database-tables-in-laravel

Please or to participate in this conversation.