将嵌套的mysql查询转换为codeigniter样式 [英] Convert the nesting mysql query into codeigniter style

问题描述

我有一个嵌套的SQL查询，如下所示。

I have a nested sql query as below.

SELECT  A.ID, A.fName, A.lName,
   COALESCE (B.calls, 0) AS Calls
FROM TableA AS  A
   LEFT JOIN
   (
      SELECT COUNT(B.event) AS  Calls, B.ID
      FROM TableB AS B
      WHERE B.event LIKE 'call'
        AND `Time` >= 1360540800
        AND `Time` <= 1361232000
      GROUP BY B.ID

    ) B
      ON A.ID = B.ID 
WHERE A.State  LIKE 'SENT' OR A.State  LIKE 'SET'

我正在尝试将其转换为codeigniter样式。我知道Code Igniter的Active Record类本身不支持子查询。但是如何做到这一点。我一直在尝试下面的代码。
因此，我进行了两个不同的查询，并使用并集将它们合并，如下所示：

I am trying to convert it in codeigniter style. I know Code Igniter's Active Record class does not natively support subqueries. But how could this be done. I have been trying the below code. So I made two different queries and combined them using union as below:

$query  = $this->db->query("select * from $subQuery2 UNION $subQuery1  as unionTable ");

联盟不是正确的方法，可以帮助我将其转换为Codeigniter样式。

Union is not the right way, can help me help me to convert it into codeigniter style.

推荐答案

为此，您可以使用codeigniter的子查询方式来执行此操作，因此您将不得不破解codeigniter。像这样
转到system / database / DB_active_rec.php从这些函数中删除public或protected关键字

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this Go to system/database/DB_active_rec.php Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)
public function _reset_select()

现在子查询写入可用且现在这里是具有活动记录的查询

Now subquery writing in available And now here is your query with active record

$data   =   array(
                'COUNT(B.event) AS  Calls',
                'B.ID'
            );

$this->db
        ->select($data)
        ->from('TableB AS B')
        ->like('B.event','call','both')
        ->where('`Time` >=',1360540800)
        ->where('`Time` <=',1361232000)
        ->group_by('B.ID');

$subQuery = $this->db->_compile_select();
$this->db->_reset_select();
unset($data);
$data   =   array(
                'A.ID',
                'A.fName',
                'A.lName',
                'COALESCE (B.calls, 0) AS Calls'
            );
$this->db
        ->select($data)
        ->from('TableA AS A')
        ->join("$subquery")
        ->like('A.State','SENT','both')
        ->or_like('A.State','SET','both');

我把两种都喜欢的东西都可以根据您的要求做。
注意：在使用子查询时，您必须使用

I have put both in the likes you can do according to your requirements. Note : While using sub queries you must use

$this->db->from('myTable')

而不是

$this->db->get('myTable')

运行查询。

这篇关于将嵌套的mysql查询转换为codeigniter样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！