php - Zend 框架 sql 查询产生不同的结果

Question

我正在尝试在 zend 中执行此 sql 查询:

SELECT a.id AS aid, 
    a.assignment_name AS name, 
    IFNULL((a.id = sa.aid),'0') AS status,
    a.assignment_name AS assignment_name,
        c.class_name AS class_name,
    a.due_date AS due_date,
        a.post_date AS post_date,
       (a.id = sa.aid) AS status,
       IF(a.description="",'No description given',a.description) AS description,
       IF(a.grading="",'No grading policy',a.grading) AS policy,
       CONCAT(u.prefix,' ',u.lname) AS teacher
FROM `assignments` as a
JOIN classes AS c ON a.class_id = c.id
JOIN enrollments as e ON e.student_id = 9 AND e.approved = 1 AND c.id = e.class_id
JOIN users as u on u.id = a.teacher_id
LEFT JOIN student_assignments as sa ON a.id = sa.aid
group by a.id;

我的 zend 代码是这样的:

public function getAllStudent($count, $offset) {
        $result = Zend_Db_Table::getDefaultAdapter()->select()
                        ->from(array('a' => 'assignments'),
                                array(
                                    'id' => 'a.id',
                                    'assignment_name' => 'a.assignment_name',
                                    'class_name' => 'c.class_name',
                                    'due_date' => 'a.due_date',
                                    'post_date' => 'a.post_date',
                                    'status' => "(a.id = sa.aid)",
                                    'description' => 'IF(a.description="",\'No description given\',a.description)',
                                    'policy' => 'IF(a.grading="",\'No grading policy\',a.grading)',
                                    'teacher' => 'CONCAT(u.prefix,\' \',u.lname)'
                        ))

                        ->join(array('c' => 'classes'), 'a.class_id = c.id', array())
                        ->join(array('e' => 'enrollments'), 'e.student_id = ' . $this->auth->id . ' AND e.approved = 1 AND c.id = e.class_id', array())
                        ->join(array('u' => 'users'), 'u.id = a.teacher_id', array())
                        ->joinLeft(array('sa' => 'student_assignments'), 'a.id = sa.id', array())
                        ->group('a.id')
                        ->order('a.due_date DESC')
                        ->limit($count, $offset)
                        ->query()
                        ->fetchAll();
        return $result;
    }

我从我的 Controller 调用它如下:

$this->view->assignments = $this->assignments->getAllStudent(20, 0);

当我在 navicat 中执行此查询(第一个代码段)时，我得到这些结果:

ID         name  !STATUS!   assignment_name  class   date              etc                      etc
24  Kill Animal 0   Kill Animal biology 2011-01-27  2011-01-26      You must kill a frog    Instant death = 100points. Slow death = 40points    Mr. Mihai
25  Solve Exercies  1   Solve Exercies  Math    2011-01-31  2011-01-26  1   You must solve all exercises    All exercises = 100points   Mr. Mihai

一切都很好，这就是我想要得到的结果。查看 STATUS 列(我用 !! 来强调它)。对于一个结果 STATUS = 0，对于另一个结果 STATUS = 1。这是它应该返回的内容。然而如果我使用上面的代码(第二个代码段)执行相同的查询，我会得到以下信息:

array(2) {
  [0]=>
  array(9) {
    ["id"]=>
    string(2) "25"
    ["assignment_name"]=>
    string(14) "Solve Exercies"
    ["class_name"]=>
    string(4) "Math"
    ["due_date"]=>
    string(10) "2011-01-31"
    ["post_date"]=>
    string(10) "2011-01-26"
    ["status"]=>
    NULL
    ["description"]=>
    string(28) "You must solve all exercises"
    ["policy"]=>
    string(25) "All exercises = 100points"
    ["teacher"]=>
    string(9) "Mr. Mihai"
  }
  [1]=>
  array(9) {
    ["id"]=>
    string(2) "24"
    ["assignment_name"]=>
    string(11) "Kill Animal"
    ["class_name"]=>
    string(7) "biology"
    ["due_date"]=>
    string(10) "2011-01-27"
    ["post_date"]=>
    string(10) "2011-01-26"
    ["status"]=>
    NULL
    ["description"]=>
    string(20) "You must kill a frog"
    ["policy"]=>
    string(48) "Instant death = 100points. Slow death = 40points"
    ["teacher"]=>
    string(9) "Mr. Mihai"
  }
}

这是 $result->__toString 在 zend 中为查询返回的内容:

 SELECT `a`.`id`, 
    `a`.`assignment_name`, 
    `c`.`class_name`, 
    `a`.`due_date`, 
    `a`.`post_date`,
     (a.id = sa.aid) AS `status`, 
    IF(a.description="",'No description given',a.description) AS `description`, 
    IF(a.grading="",'No grading policy',a.grading) AS `policy`, 
    CONCAT(u.prefix,' ',u.lname) AS `teacher` FROM `assignments` AS `a`
INNER JOIN `classes` AS `c` ON a.class_id = c.id
INNER JOIN `enrollments` AS `e` ON e.student_id = 9 AND e.approved = 1 AND c.id = e.class_id
INNER JOIN `users` AS `u` ON u.id = a.teacher_id
LEFT JOIN `student_assignments` AS `sa` ON a.id = sa.id 
GROUP BY `a`.`id` 
ORDER BY `a`.`due_date` 
DESC LIMIT 20

如你所见，这里的status为NULL。为什么会这样？

Answer 1

您的 Zend 查询缺少 IFNULL 函数调用。应该是:

'status' => "IFNULL(a.id = sa.aid,0)",

由于您使用的是 LEFT JOIN，如果没有 sa 记录，sa.aid 将为空，并且 任何数字 = NULL 都将返回 NULL。

还有一点我注意到，在你的查询中你加入了 a.id = sa.aid，但是在 zend 查询中你加入了 'a.id = sa.id '(不是缺少的 a)。