php - 将 MySQL 查询转换为 CI 语法

Question

我在将 MySQL 查询转换为 codeigniter 语法时遇到问题。

这是我的 MySQL 查询。

Select id, name, code, status, question_count, session
from s3r_set  
left join 
 (Select set_id, count(id) as question_count
    from  s3r_question group by set_id) question 
on question.set_id = id
left join
 (SELECT session_id as session, set_id
    from s3r_session) s3rsession
on s3rsession.set_id = id
order by id

这是我目前在 CI 中得到的:

$this->db->select('id, name, code, status, question_count, session');
$this->db->from('s3r_set');
$this->db->join('s3r_question', '(Select set_id, count(id) as question_count
                                from  s3r_question group by set_id) question
                                on question.set_id = id', 'left');
$this->db->join('s3r_session', '(SELECT session_id as session, set_id
                                from s3r_session) s3rsession
                                on s3rsession.set_id = id', 'left');
$this->db->order_by('id', "desc");

$q = $this->db->get();

if($q->num_rows() > 0)
{
    return $q->result();
}
else
{
    return false;
}

提前感谢您的帮助。

Answer 1

好吧，你做的 join 是错误的:

$this->db->join('s3r_question', '(Select set_id, count(id) as question_count
                                from  s3r_question group by set_id) question
                                on question.set_id = id', 'left');

应该是:

$this->db->join('(Select set_id, count(id) as question_count 
from  s3r_question group by set_id) question', 'question.set_id = id');

请引用这里的格式:
http://ellislab.com/codeigniter/user-guide/database/active_record.html#join

...join(FROM_TABLE, ON WHAT VALUE TO JOIN ON, OPTIONAL > WHAT TYPE OF JOIN, LEFT/RIGHT/ETC;)