php - 如何将 Sql 代码转换为 codeigniter 语法？

Question

select
    x1.category
    , x1.title
from yourtable x1
where
    (
    select count(*)
    from yourtable x2
    where x2.category = x1.category
    and x2.title <= x1.title
    ) <= 2
order by category, title;

我的模型中有类似的东西。

$this->db->select('x1.category','x1.title, false);
         $this->db->from('yourtable as x1');
         $sub = $this->subquery->start_subquery('where');
         $sub ->select('count(*) ')<=25;
         $sub ->from('yourtable as x2');
         $sub ->where('x2.category = x1.category and x2.title<= x1.title');
         $this->subquery->end_subquery('count(*)'); 
        $this->db->order_by('category','title','RANDOM');
        $query = $this->db->get('yourtable');

我收到错误

Fatal error: Call to a member function start_subquery() on a non-object in ....

Answer 1

您需要为 codeigniter 设置子查询:

http://labs.nticompassinc.com/CodeIgniter-Subqueries/

此外，您可以这样做:

$this->db->select('x1.category','x1.title', false);
$this->db->from('yourtable as x1');
$this->db->where('(SELECT count(*) FROM yourtable x2 WHERE x2.category = x1.category AND x2.title <= x1.title) <= 2', NULL, false);
$this->db->order_by('category','title');