I have the following database.
I am interested in showing the information of the user, the department and the position, I have a query that presents me with the officials and the department but I would like to show the position at once.
return DB::table('funcionario as f')
->join('dep_has_fun as dhf','dhf.funcionario_idfuncionario','=','f.idfuncionario')
->join('departamento as d', 'd.iddepartamento','=','dhf.departamento_iddepartamento')
->where('dhf.estado','=',1)->get();
What occurred to me to do is: de_has_fun
add the position key to the table, in this way in a single query I would obtain the department and position to which it belongs, but I am not sure if it is correct. I am open to any comments and suggestions.
As I see in the structure of your database, an official can have more than one department and more than one position.
If your relations are defined correctly in Eloquent (following its documentation to the letter), with this syntax you get what you want:
And you get the positions and departments of each official as follows:
I can give you a solution with SQL and you already adapt it. Basically you are missing JOINS from what I see, I have created the tables in the diagram with oracle SQL developer and performed the query to check its operation:
Reviewing the example code that you put, you need to do the joins to obtain the position (you only have the joins for the department), you can try the following:
It is worth telling you that you can use Eloquent and the relationships provided by Laravel that will make everything easier for you.