mysql - SQL Query Join in CodeIgniter

Question

I have this query and I need it to be in the CodeIgniter way (query builder). I know how to use the standard query builder class functions, but I have difficulties to find a way how to build a LEFT JOIN with an inner SELECT clause using the CI Query Builder Class.

SELECT * 
FROM   sma_products p 
       LEFT JOIN (SELECT product_id, 
                         Count(*) 
                  FROM   sma_sale_items 
                  GROUP  BY product_id) s 
              ON p.id = s.product_id 
ORDER  BY ` Count(*) ` DESC 

Answer 1

The difficulty to create this SQL with CI query builder lies in the select part inside the left join. You can build it using the join() function replacing the $table parameter with the SELECT part:

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

    $table (string) – Table name to join
    $cond (string) – The JOIN ON condition
    $type (string) – The JOIN type
    $escape (bool) – Whether to escape values and identifiers

this is the final CI code:

$q=$this->db1   ->select ('*')
                ->join('(
                                select `product_id`
                                ,count(*) 
                                from `sma_sale_items`
                                group by `product_id`
                            ) s','p.id = s.product_id','left')
                ->order_by('count(*)', 'DESC')
                ->get('sma_products p');

return $q->result();