CodeIgniter Active记录子查询中的位置

问题描述：

How do I write where_in statement a subquery using codeigniter active records ?

$query = $this->db->query("SELECT SUM(a.transaction_payment_amount) FROM 
         transaction_payment a WHERE a.transaction_link IN 
         (SELECT transaction_link FROM transaction WHERE transaction_type = '22'");
$result = $query->result();

Now how to convert the above query into CI active records ?

I have tried:

$this->db->select("SUM(a.transaction_payment_amount)");
$this->db->from('transaction_payment a');
$this->db->where_in('a.transaction_link', "SELECT transaction_link from transaction WHERE transaction_type = '22'");
$query = $this->db->get();
$result = $query->result();

But it doesn't work.

答

Try This

$this->db->where_in('a.transaction_link', "SELECT transaction_link from transaction WHERE transaction_type = '22'",false);

if you use false then it will remove single quotes from where_in condition

答

Sharmas Answer should do the job but if you wish fully supported Query Builder Methods you can try this

$strSubQuery = $this->db
    ->select("transaction_link")
    ->from("transaction")
    ->where("transaction_type",22)
    ->get_compiled_select();

$query = $this->db
    ->select("SUM(a.transaction_payment_amount)", false)
    ->from('transaction_payment a')
    ->where_in('a.transaction_link', $strSubQuery, false)
    ->get();

答

You can change your code as following solution.

Changes your query

$this->db->select("SUM(a.transaction_payment_amount)");
$this->db->from('transaction_payment a');
$this->db->where("a.transaction_link IN (SELECT transaction_link from transaction WHERE transaction_type = '22')", null, false);
//OR you can try other where condition if Sub query return null value than used this below query
$this->db->where("IF(SELECT transaction_link from transaction WHERE transaction_type = '22',a.transaction_link IN (SELECT transaction_link from transaction WHERE transaction_type = '22'), NULL)", null, false);
$query = $this->db->get();
$result = $query->result();

I hope this will helps you. Thanks!

CodeIgniter Active记录子查询中的位置

相关推荐