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();
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…