Codeigniter JOIN（选择查询 [英] Codeigniter JOIN (SELECT query

问题描述

可以通过CI Query Builder类生成以下查询？

It is possible to generate the following query by CI Query Builder class ?

SELECT name 
       FROM table1 t1 
                   JOIN 
                     (SELECT ID FROM table2 ORDER BY id LIMIT 5) t2 
                   ON t2.id=t1.t2_id 
       WHERE t1.id>5

推荐答案

好几种方法可以做到。一种方法是破解。

Well there are a couple of ways of doing it. One way is here which is a hack.

如何将这个SQL重写为CodeIgniter的活动记录？

另一种方法很简单。

$this->db
        ->select('ID')
        ->from('table2')
        ->order_by('id')
        ->limit('5');   

$subquery = $this->db->_compile_select();

$this->db->_reset_select(); 

$query  =       $this->db
                    ->select('t1.name')
                    ->from('table1 t1 ')
                    ->join("($subquery)  t2","t2.id = t1.t2_id")
                    ->get('table1 t1');

它的一些要点。

您必须在子查询中使用from子句

在codeigniter 2中，由于它们是受保护的方法，因此无法访问_compile_select和_reset_select。

您可能必须在系统/数据库中的两个方法之前都删除关键字。 /DB_active_rec.php

Some point about it.
You are bound to use from clause in subqueries because get runs the query.
In codeigniter 2 _compile_select and _reset_select can not be accessed because they are protected methods.
You may have to remove the keyword before both methods in system/database/DB_active_rec.php

这篇文章也很有用。

这篇关于Codeigniter JOIN（选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！