php - Zend 1.12 中的复杂查询

Question

我试图在 Zend 1.12 中获得与下面的查询相同的结果。

    SELECT te.id_usuario, u.nome, te.id_texto, t.titulo 
    FROM usuarios u, tex_turmas_alunos ta, tex_testes te, tex_textos t, publicacoes p
    WHERE te.id_usuario=u.id 
        and u.id=ta.id_aluno and ta.id_turma=10
        and te.id_texto=t.id
        and te.acertou = 'sim'
        and ta.ativo='sim'
        and te.id_texto=p.id_texto and p.id_turma=ta.id_turma
    GROUP BY te.id_usuario, t.id
    ORDER BY u.nome, t.titulo

这是我的代码:

1:

$select = 
$this -> getAdapter()
-> select()
-> from(array('u' => "usuarios"), array("nome" => "u.nome"))
-> join(array( 'ta' => 'tex_turmas_alunos'), "ta.ativo = 'sim' AND ud.id = ta.id_aluno",    null )
-> join(array('te' => 'tex_testes'), "te.acertou = 'sim'", array('te.id_texto',   'te.id_usuario'))
-> join(array('t' => 'tex_textos'), 'te.id_texto = t.id', array('t.titulo'))
-> join(array('p' => 'publicacoes'), 'p.id_turma = ta.id_turma', null )
-> where('ta.id_turma = '10')
-> group(array('te.id_usuario', 't.id' ))
-> order(array('u.nome', 't.titulo'));

return  $this ->getAdapter ()->fetchAll ( $select );

2:

    $sqlString = 
    "SELECT te.id_usuario, u.nome, te.id_texto, t.titulo 
    FROM usuarios u, tex_turmas_alunos ta, tex_testes te, tex_textos t, publicacoes p
    WHERE te.id_usuario=u.id 
        and u.id=ta.id_aluno and ta.id_turma= ?
        and te.id_texto=t.id
        and te.acertou = 'sim'
        and ta.ativo='sim'
        and te.id_texto=p.id_texto and p.id_turma=ta.id_turma
    GROUP BY te.id_usuario, t.id
    ORDER BY u.nome, t.titulo"; 
    $sql = $this->getAdapter()->query($sqlString, '10');

第一个查询的结果是:

    SELECT `u`.`nome`, `te`.`id_texto`, `te`.`id_usuario`, `t`.`titulo` FROM `usuarios` AS `u` INNER JOIN `tex_turmas_alunos` AS `ta` ON ta.ativo = 'sim' AND ta.id_aluno = u.id INNER JOIN `tex_testes` AS `te` ON te.acertou = 'sim' INNER JOIN `tex_textos` AS `t` ON te.id_texto = t.id INNER JOIN `publicacoes` AS `p` ON p.id_turma = ta.id_turma WHERE (ta.id_turma = '11') GROUP BY `te`.`id_usuario`, `t`.`id` ORDER BY `u`.`nome` ASC, `t`.`titulo` ASC

这个结果不是正确的返回。我的查询有什么问题？

--错误的结果是这样的:

nome                  id_texto id_usuario titulo
Another person          182      116       t2
Another person          183      18        t3
Another person          183      14        t3
Another person          183      24        t3
Another person          183      33        t3
Another person          183      19        t3
Another person          183      10        t3

我期望的结果:

id_usuario     nome     id_texto     titulo
   108       Person 1      131       Text A
   108       Person 1      132       Text B
   108       Person 1      166       Text C
   108       Person 1      304       Text D

你明白其中的区别吗？这是两个完全不同的结果。

谢谢

Answer 1

这是我的固定功能。效果很好。

public function testesCorretos($id_turma){

        $sqlString = 
                "SELECT te.id_usuario,  u.nome, u.matricula, te.id_texto, t.titulo 
                FROM usuarios u, tex_turmas_alunos ta, tex_testes te, tex_textos t, publicacoes p
                WHERE te.id_usuario=u.id 
                  and u.id=ta.id_aluno 
                  and te.id_texto=t.id
                  and te.acertou = 'sim'
                  and ta.ativo='sim'
                  and te.id_texto=p.id_texto 
                  and p.id_turma=ta.id_turma
                  and ta.id_turma= ?
                GROUP BY te.id_usuario, t.id
                ORDER BY u.nome, t.titulo"; 

        $sql = $this->getAdapter()->query($sqlString, $id_turma);

        while($rows = $sql->fetchAll() ){           
            for($i =0; $i <= count($rows); $i++){
                $row[] =  array(
                        'id' => $rows[$i]['id_usuario'],
                        'nome' => $rows[$i]['nome'],
                        'matricula' =>  $rows[$i]['matricula'],
                        'id_texto' => $rows[$i]['id_texto'],
                        'titulo' => $rows[$i]['titulo']
                );
            }
        }

        return $row;

    }