Codeigniter中的嵌套连接

Question

How to run this sql query in Codeigniter framework?

SELECT  users.*,
  GROUP_CONCAT(category.title SEPARATOR ',') as title 
  FROM users 
  LEFT JOIN (
    procducts as pr 
    INNER JOIN category ON pr.category_id =category.id
  ) 
  ON (users.id=pr.user_id)   
GROUP BY users.id

using Active Record (Query Builder Class) in Codeigniter without using db->query

Answer 1

The Codeigniter docs explain a join like follows:

join($table, $cond[, $type = ''[, $escape = NULL]])

there is no documentation on nested join support. So you could create a query like:

$this->db->select('users.*, GROUP_CONCAT(category.title SEPARATOR ',') as title')
         ->from('users')
         ->join('procducts as pr INNER JOIN category ON pr.category_id =category.id','users.id=pr.user_id','left')
         ->group_by('users.id');
$query = $this->db->get();