Nov 19, 2015
0
Level 1
Working with a diferent relation tables
I have a table with three important keys that I need to Inner Join with two other tables. MY currently code:
class Professor extends Model
{
protected $table = "professor";
protected $primaryKey = 'cod_professor';
public function turmas()
{
return $this->belongsToMany('App\Turma',professor_turma','cod_professor', 'cod_turma')->withPivot('CH','ano_semestre');
}
}
The currently code, provides me the following Query:
"select `turma`.*, `professor_turma`.`cod_professor` as `pivot_cod_professor`, `professor_turma`.`cod_turma` as `pivot_cod_turma`,
`professor_turma`.`CH` as `pivot_CH`, `professor_turma`.`ano_semestre` as `pivot_ano_semestre` from `turma`
inner join `professor_turma` on `turma`.`cod_turma` = `professor_turma`.`cod_turma` where `professor_turma`.`cod_professor` = ?"
But what I really need is something like this:
SELECT DISTINCT professor.nome, professor_turma.cod_professor, professor_turma.cod_turma, professor_turma.cod_disciplina, turma.nome_disciplina
FROM professor_turma
INNER JOIN professor ON professor_turma.cod_professor = professor.cod_professor
INNER JOIN turma ON professor_turma.cod_turma = turma.cod_turma AND professor_turma.cod_disciplina = turma.cod_disciplina
WHERE professor_turma.cod_professor = ?
On WHERE professor_turma.cod_professor = ?
I need to loop through all() my professors.
As you can see in my query, I need to check: cod_professor, cod_turma and cod_disciplina. I need to compare all these three fields into two tables.
Maybe create the model of professor_turma and from this one I make the joins??
How could I specify the joins?
Please or to participate in this conversation.