codeigniter mysql left join include select [英] codeigniter mysql left join include select

问题描述

如何转换左连接包括select to codeigniter sql方法？谢谢。我只想知道。

  SELECT c1.c1_id，c1.c1_name，c2.c2_id，c2.c2_name，c2.c2_type ，c2.c2_status，f.f_id，f.f_name，f2.f2_id，f2.f2_name FROM category2 c2 
 LEFT JOIN category1 c1 ON c1.c1_id = c2.c1_id 
 LEFT JOIN（
 SELECT DISTINCT c2_id，f_id，f_name FROM file ORDER BY f_id DESC 
）f ON f.c2_id = c2.c2_id 
 LEFT JOIN（
 SELECT DISTINCT c2_id，f2_id，f2_name FROM file2 ORDER BY f2_id DESC 
）f2 ON f2.c2_id = c2.c2_id 
 WHERE c2.c2_status = 1 
 GROUP BY c2.c2_id 
  

解决方案

你可以使用codeigniter的子查询方式来做到这一点，你必须破解codeigniter。像这样
转到系统/数据库/ DB_active_rec.php从这些函数中删除public或protected关键字

  public function _compile_select （$ select_override = FALSE）
 public function _reset_select（）
  

现在子查询可用现在这里是您的活动记录查询

  $ select = array（'DISTINCT c2_id'，'f_id'，'f_name'） ; 
 $ this-> db-> select（$ select）; 
 $ this-> db-> from（'file'）; 
 $ this-> db-> order_by（'f_id'，'DESC'）; 
 $ subQuery1 = $ this-> db-> _compile_select（）; 
 
 unset（$ select）; 
 
 $ this-> db-> _reset_select（）; 
 
 $ select = array（'DISTINCT c2_id'，'f_id'，'f2_name'）; 
 $ this-> db-> select（$ select）; 
 $ this-> db-> from（'file2'）; 
 $ this-> db-> order_by（'f2_id'，'DESC'）; 
 $ subQuery2 = $ this-> db-> _compile_select（）; 
 
 unset（$ select）; 
 
 $ this-> db-> _reset_select（）; 
 
 //现在你的主查询
 
 $ select = array（
'c1.c1_id'，
'c1.c1_name'，
'c2.c2_id'，
'c2.c2_name'，
'c2.c2_type'，
'c2.c2_status'，
'f.f_id'，
'f.f_name'，
'f2.f2_id'，
'f2.f2_name'
）; 
 
 $ this-> db-> select（$ select）; 
 $ this-> db-> from（'category2 c2'）; 
 $ this-> db-> join（（$ subQuery1），'f.c2_id = c2.c2_id'，'left'）; 
 $ this-> db-> join（（$ subQuery2），'f2.c2_id = c2.c2_id'，'left'）; 
 $ this-> db-> where（'c2.c2_status'，1）; 
 $ this-> db-> group_by（'c2.c2_id'）; 
 $ main_query = $ this-> db-> get（）; 
  

事情已经完成了。干杯！！！
注意：在使用子查询时，您必须使用

  $ this-> db-> from '）
  

而不是

  $ this-> db-> get（'myTable'）
  

现在，您可以检查已建立为
的查询。
  echo $ this-> db-> last_query（）; 
  

How to convert the left join include select to codeigniter sql method? Thanks. I just want to know.

SELECT c1.c1_id, c1.c1_name, c2.c2_id, c2.c2_name, c2.c2_type, c2.c2_status, f.f_id, f.f_name, f2.f2_id, f2.f2_name FROM category2 c2 
LEFT JOIN category1 c1 ON c1.c1_id = c2.c1_id 
LEFT JOIN (
    SELECT DISTINCT c2_id, f_id, f_name FROM file ORDER BY f_id DESC
) f ON f.c2_id = c2.c2_id
LEFT JOIN (
    SELECT DISTINCT c2_id, f2_id, f2_name FROM file2 ORDER BY f2_id DESC
) f2 ON f2.c2_id = c2.c2_id
WHERE c2.c2_status = 1
GROUP BY c2.c2_id

解决方案

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

$select =   array('DISTINCT c2_id','f_id','f_name');
$this->db->select($select);
$this->db->from('file');
$this->db->order_by('f_id','DESC');
$subQuery1 = $this->db->_compile_select();

unset($select);

$this->db->_reset_select();

$select =   array('DISTINCT c2_id','f_id','f2_name');
$this->db->select($select);
$this->db->from('file2');
$this->db->order_by('f2_id','DESC');
$subQuery2 = $this->db->_compile_select();

unset($select); 

$this->db->_reset_select();

// And now your main query

$select =   array(
                  'c1.c1_id',
                  'c1.c1_name',
                  'c2.c2_id',
                  'c2.c2_name',
                  'c2.c2_type',
                  'c2.c2_status',
                  'f.f_id',
                  'f.f_name',
                  'f2.f2_id',
                  'f2.f2_name'
            );

$this->db->select($select);
$this->db->from('category2 c2');
$this->db->join("($subQuery1)",'f.c2_id = c2.c2_id','left');
$this->db->join("($subQuery2)",'f2.c2_id = c2.c2_id','left');
$this->db->where('c2.c2_status',1);
$this->db->group_by('c2.c2_id');
$main_query = $this->db->get();

And the thing is done. Cheers!!! Note : While using sub queries you must use

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

instead of

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

which runs the query.

Now, you can check query that has been built as

echo $this->db->last_query();

这篇关于codeigniter mysql left join include select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！