CodeIgniter中的SQL查询联接 [英] SQL Query Join in CodeIgniter

问题描述

我有此查询，我需要使用CodeIgniter方式(查询生成器)进行查询.我知道如何使用标准的查询生成器类函数，但是我很难找到一种使用CI查询生成器类使用内部 SELECT 子句构建 LEFT JOIN 的方法.

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 

推荐答案

使用CI查询构建器创建此SQL的困难在于左联接内的select部分.您可以使用 join()进行构建函数将$ table参数替换为 SELECT 部分:

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]])参数:

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

这是最终的CI代码:

$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();

这篇关于CodeIgniter中的SQL查询联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！