phpcodeigniterjoinactiverecordquery-builder

Convert this SQL containing comma-JOINs related by identical column names to CodeIgniter active record scripting

This is the MySQL query that I need to convert:

SELECT pdg_matriculas.id_matricula, aluno.nome_aluno, pdg_serie.nome_serie, pdg_turma.nome_turma, aluno.status_aluno, pdg_matriculas.ano_letivo
FROM pdg_matriculas, pdg_serie, pdg_turma, aluno
WHERE pdg_matriculas.id_aluno = aluno.id_aluno
AND pdg_matriculas.id_serie = pdg_serie.id_serie
AND pdg_matriculas.id_turma = pdg_turma.id_turma
ORDER BY `aluno`.`nome_aluno` ASC
LIMIT 0 , 30

I tried to use direct query, It did not work.

$this->db->select('SELECT pdg_matriculas.id_matricula, aluno.nome_aluno, pdg_serie.nome_serie, pdg_turma.nome_turma, aluno.status_aluno, pdg_matriculas.ano_letivo
FROM pdg_matriculas, pdg_serie, pdg_turma, aluno
WHERE pdg_matriculas.id_aluno = aluno.id_aluno
AND pdg_matriculas.id_serie = pdg_serie.id_serie
AND pdg_matriculas.id_turma = pdg_turma.id_turma');
$this->db->limit(10);
$query = $this->db->get();

tried with Active Record and also failed

$this->db->select('T1.id_matricula, T2.nome_aluno, T3.nome_serie, T4.nome_turma, T2.status_aluno, T1.ano_letivo',FALSE);
$this->db->from('pdg_matriculas T1, aluno T2, pdg_serie T3, pdg_turma T4');
$this->db->where('T1.id_matricula','T2.id_aluno');
$this->db->where('T1.id_serie','T3.id_serie');
$this->db->where('T1.id_turma','T4.id_turma');
$this->db->order_by('T2.nome_aluno', 'asc'); 
$this->db->limit(10);
$query = $this->db->get();
Solution

  • Sorry for the delay, now it worked, of course a few minor changes, more myth thanks to everyone and keep your reference.

    $this->db->select('T1.id_matricula,aluno.nome_aluno,pdg_serie.nome_serie,pdg_turma.nome_turma,T1.ano_letivo,T1.id_aluno as id_aluno_m,aluno.id_aluno as id_aluno_a,aluno.status_aluno');
$this->db->from('pdg_matriculas T1');
$this->db->join('aluno', 'aluno.id_aluno = T1.id_aluno');
$this->db->join('pdg_serie', 'pdg_serie.id_serie = T1.id_serie');
$this->db->join('pdg_turma', 'pdg_turma.id_turma = T1.id_turma');
$page_data ['alunos_lista'] = $this->db->get ()->result_array ();

    Worked Perfect