MySQL 使用 codeigniter 的活动记录模式进行 UNION 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2040655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNION query with codeigniter's active record pattern
提问by strike_ntheitroad
How to do UNION query with PHP CodeIgniter framework's active record query format?
如何使用 PHP CodeIgniter 框架的活动记录查询格式进行 UNION 查询？
回答by Zack
CodeIgniter's ActiveRecord doesn't support UNION, so you would just write your query and use the ActiveRecord's query method.
CodeIgniter 的 ActiveRecord 不支持 UNION，因此您只需编写查询并使用 ActiveRecord 的查询方法。
$this->db->query('SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2');
回答by afinno
This is a quick and dirty method I once used
这是我曾经使用过的一种快速而肮脏的方法
// Query #1
$this->db->select('title, content, date');
$this->db->from('mytable1');
$query1 = $this->db->get()->result();
// Query #2
$this->db->select('title, content, date');
$this->db->from('mytable2');
$query2 = $this->db->get()->result();
// Merge both query results
$query = array_merge($query1, $query2);
Not my finest work, but it solved my problem.
不是我最好的作品，但它解决了我的问题。
note: I didn't need to order the result.
注意：我不需要对结果进行排序。
回答by Somnath Muluk
By doing union using last_query(), it may hamper performance of application. Because for single union it would require to execute 3 queries. i.e for "n" union "n+1" queries. It won't much affect for 1-2 query union. But it will give problem if union of many queries or tables having large data.
通过使用 last_query() 进行联合，它可能会妨碍应用程序的性能。因为对于单个联合，它需要执行 3 个查询。即对于“n”联合“n+1”查询。它不会对 1-2 查询联合产生太大影响。但是如果合并许多查询或具有大量数据的表，则会出现问题。
This link will help you a lot: active record subqueries
这个链接对你有很大帮助：活动记录子查询
We can combine active record with manual queries. Example:
我们可以将活动记录与手动查询结合起来。例子：
// #1 SubQueries no.1 -------------------------------------------
$this->db->select('title, content, date');
$this->db->from('mytable');
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();
$this->db->_reset_select();
// #2 SubQueries no.2 -------------------------------------------
$this->db->select('title, content, date');
$this->db->from('mytable2');
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();
$this->db->_reset_select();
// #3 Union with Simple Manual Queries --------------------------
$this->db->query("select * from ($subQuery1 UNION $subQuery2) as unionTable");
// #3 (alternative) Union with another Active Record ------------
$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();
回答by Souvik
You may use the following method to get the SQL statement in the model:
您可以使用以下方法获取模型中的 SQL 语句：
$this->db->select('DISTINCT(user_id)');
$this->db->from('users_master');
$this->db->where('role_id', '1');
$subquery = $this->db->_compile_select();
$this->db->_reset_select();
This way the SQL statement will be in the $subquery variable, without actually executing it.
这样 SQL 语句将在 $subquery 变量中，而不实际执行它。
You have asked this question a long time ago, so maybe you have already got the answer. if not, this process may do the trick.
你很久以前就问过这个问题，所以也许你已经有了答案。如果没有，这个过程可能会奏效。
回答by am05mhz
by modifying somnath huluks answer, i add these following variable and functions to DB_Active_rec class as follows:
通过修改 somnath huluks 答案，我将以下变量和函数添加到 DB_Active_rec 类中，如下所示：
class DB_Active_records extends CI_DB_Driver
{
....
var $unions;
....
public function union_push($table = '')
{
if ($table != '')
{
$this->_track_aliases($table);
$this->from($table);
}
$sql = $this->_compile_select();
array_push($this->unions, $sql);
$this->_reset_select();
}
public function union_flush()
{
$this->unions = array();
}
public function union()
{
$sql = '('.implode(') union (', $this->unions).')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
public function union_all()
{
$sql = '('.implode(') union all (', $this->unions).')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
}
therefore you can virtually use unions without dependencies to db_driver.
因此，您实际上可以使用联合而不依赖于 db_driver。
to use union with this method, you simply make regular active record queries, but calling union_push instead of get.
要将 union 与此方法一起使用，您只需进行常规活动记录查询，但调用 union_push 而不是 get。
note: you have to ensure your queries have matching columns like regular unions
注意：您必须确保您的查询具有匹配的列，如常规联合
example:
例子：
$this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
$this->db->where(array('l.requirement' => 0));
$this->db->union_push('lessons l');
$this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
$this->db->from('lessons l');
$this->db->join('scores s', 'l.requirement = s.lid');
$this->db->union_push();
$query = $this->db->union_all();
return $query->result_array();
would produce:
会产生：
(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
WHERE `l`.`requirement`=0)
union all
(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
JOIN `scores` s ON `l`.`requirement`=`s`.`lid`)
回答by Matt Browne
I found this library, which worked nicely for me to add UNION in an ActiveRecord style:
我找到了这个库，它非常适合我以 ActiveRecord 样式添加 UNION：
https://github.com/NTICompass/CodeIgniter-Subqueries
https://github.com/NTICompass/CodeIgniter-Subqueries
BUT I had to grab the get_compiled_select()method from the dev branch of CodeIgniter first (available here: https://github.com/EllisLab/CodeIgniter/blob/develop/system/database/DB_query_builder.php-- DB_query_builder will be replacing DB_active_rec). Presumably this method will be available in a future production release of CodeIgniter.
但我必须首先get_compiled_select()从 CodeIgniter 的 dev 分支中获取该方法（可在此处获得：https: //github.com/EllisLab/CodeIgniter/blob/develop/system/database/DB_query_builder.php-- DB_query_builder 将替换 DB_active_rec）。据推测，此方法将在 CodeIgniter 的未来生产版本中可用。
Once I added that method to DB_active_rec.php in system/database it worked like a charm. (I didn't want to use the dev version of CodeIgniter as this is a production app.)
一旦我将该方法添加到系统/数据库中的 DB_active_rec.php 中，它就像一个魅力。（我不想使用 CodeIgniter 的开发版本，因为这是一个生产应用程序。）
回答by Levan Abashidze
This is solution I am using:
这是我正在使用的解决方案：
$union_queries = array();
$tables = array('table1','table2'); //As much as you need
foreach($tables as $table){
$this->db->select(" {$table}.row1,
{$table}.row2,
{$table}.row3");
$this->db->from($table);
//I have additional join too (removed from this example)
$this->db->where('row4',1);
$union_queries[] = $this->db->get_compiled_select();
}
$union_query = join(' UNION ALL ',$union_queries); // I use UNION ALL
$union_query .= " ORDER BY row1 DESC LIMIT 0,10";
$query = $this->db->query($union_query);
回答by Ameen Maheen
try this one
试试这个
function get_merged_result($ids){
$this->db->select("column");
$this->db->distinct();
$this->db->from("table_name");
$this->db->where_in("id",$model_ids);
$this->db->get();
$query1 = $this->db->last_query();
$this->db->select("column2 as column");
$this->db->distinct();
$this->db->from("table_name");
$this->db->where_in("id",$model_ids);
$this->db->get();
$query2 = $this->db->last_query();
$query = $this->db->query($query1." UNION ".$query2);
return $query->result();
}
回答by Dilpazir Ahmad
bwisn's answer is better than all and will work but not good in performance because it will execute sub queries first. get_compiled_selectdoes not run query; it just compiles it for later run so is faster try this one
bwisn 的答案比所有答案都要好，并且可以工作但性能不佳，因为它会首先执行子查询。 get_compiled_select不运行查询；它只是编译它供以后运行所以更快尝试这个
$this->db->select('title, content, date');
$this->db->where('condition',value);
$query1= get_compiled_select("table1",FALSE);
$this->db->reset_query();
$this->db->select('title, content, date');
$this->db->where('condition',value);
$query2= get_compiled_select("table2",FALSE);
$this->db->reset_query();
$query = $this->db->query("$query1 UNION $query2");
回答by bwisn
Here's a solution I created:
这是我创建的解决方案：
$query1 = $this->db->get('Example_Table1');
$join1 = $this->db->last_query();
$query2 = $this->db->get('Example_Table2');
$join2 = $this->db->last_query();
$union_query = $this->db->query($join1.' UNION '.$join2.' ORDER BY column1,column2);
