Remove single quotes from where_in in codeigniter

问题:

I am working on a search functionality. I have created a search form in which user can search an Application based on Type,ope & Formate.

I have used a subquery in my join query to get the desired result. I have tested my query in MySQL Workbench nad it's working fine.

But when I tried that same query in Codeigniter using query builder technique then I am facing a problem.

Here is the query which is worked fine in workbench:

SELECT (*)
FROM `App`
LEFT JOIN `App_type` 
ON `App_type`.`app_id` = `App`.`id`
LEFT JOIN `App_formate` 
ON `App_formate`.`app_id` = `App`.`id`
WHERE `App`.`id` IN(select app_id FROM App_type WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3)
AND `App_formate`.`formate_id` IN('1', '3')
AND `jobs`.`ope_min` <= '3'
AND `jobs`.`ope_max` >= '3'
GROUP BY `jobs`.`id`;

This is the join query which I use:

$subquery = "select app_id FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";

$search_app_query = $this->db
    ->select('*')
    ->from('App')
    ->join('App_type', 'App_type.app_id = App.id', 'left outer')
    ->join('App_formate', 'App_formate.app_id = App.id', 'left outer')      
    ->where_in('App.id',$subquery)  //<-- Here is the problem
    ->where_in('App_formate.formate_id',$data['selected_formates'])
    ->where('App.ope_min <=',$data['ope_value'])
    ->where('App.ope_max >=',$data['ope_value'])    
    ->group_by("App.id", "desc")
    ->get();

While I am debugging this problem it shows the

 I have found the problem is in this part of the query:
 "WHERE `App`.`id` IN('select app_id 
 FROM App_type 
 WHERE type_id in (3,2,6) 
 group by app_id HAVING COUNT(*) = 3')"

that single quote in this subquery is creating a problem.

What I have tried so far:

To remove this single quote I have tried

1. REPLACE($subquery, '''', '')
2. ->where_in('App.id',trim($subquery,"'"))
3. $subquery_improved = substr($subquery, 1, -1);

But all this solution is not working. They are not removing the single quote.

Note: I am aware of $this->db->query() but do not want to use that.

回答1:

Your task looks pretty simple

instead of

->where_in('App.id',$subquery)  //<-- Here is the problem

you can try the following

->where("App.id IN (".$subquery.")",NULL, false)

You can find this exact information in the Codeigniter Documentation here (point 4 and the section below).

回答2:

My approach would be something like below, in a more generic way. I am always trying to follow the MVC pattern while breaking the functionality in small functions. Although you didn't share all of your code, i will suggest it anyway.

You should change my custom given names to functions, arrays etc. so it matches your code. Hope it helps.

Model function

public function getApp_ids($selected_type, $type_count) {
   $subquery = "select app_id FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";
   $result = $subquery->get();
   if($result->num_rows() > 0) //the check here is relevant to your data
       return $result->result_array();
   return false;
}

Model Function

public function searchApp($appIds, $data) {
  //$appIds and $data are parameters to this function. 
    $search_app_query = $this->db
       ->select('*')
       ->from('App')
       ->join('App_type', 'App_type.app_id = App.id', 'left outer')
       ->join('App_formate', 'App_formate.app_id = App.id', 'left outer')      
       ->where_in('App.id',$appIds)  //pass the array of your IDs
       ->where_in('App_formate.formate_id',$data['selected_formates'])
       ->where('App.ope_min <=',$data['ope_value'])
       ->where('App.ope_max >=',$data['ope_value'])    
       ->group_by("App.id", "desc")
       ->get();

    if($search_app_query->num_rows() > 0) //again, the check is yours
         return $search_app_query->result_array();
    return false;
}

In your Controller, something like this

public function YOUR_FUNCTION($selected_type, $type_count) {
   //call this function from model to grab the app_id you want. Don't forget to pass the parameters you sql needs.
   $appIdsResult = $this->YOUR_MODEL->getApp_ids($selected_type, $type_count);

   //Manipulate your result in another array, so you can get rid off the indexes and do some checks if you want
   $appIds = array();
   foreach ($appIdsResult as $appId) {
       array_push($appIds, $appId['app_id']); //Check the index app_id, it is the result from your DB.
    }

    //Call searchApp from your model and pass the found $appIds and your $data ofcourse
    $result = $this->YOUR_MODEL->searchApp($appIds, $data);
    //In $result will be your answer
}

回答3:

replace this code:

->where_in('App.id',$subquery)  //<-- Here is the problem

with this code:

->where_in("App.id", $subquery, FALSE)

the 3rd parameter accept Boolean to determine if the function should escape the value and identifier or not. When set to FALSE, it does not use single quote as escape char.

Hope this helps.

回答4:

USE MANUAL QUERY ( unsafe ,use very very becareful)

$sql = "SELECT ....[Your Query].....":
$query = $this->db->query($sql);
if ($query->num_rows() > 0){
    // Found
}else{
    // Not Found
}

回答5:

//first confirm subquery return only one result if not change in subquery

$subquery = "select GROUP_CONCAT(CONCAT(\"'\",app_id,\"'\")) FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";

$search_app_query = $this->db
->select('*')
->from('App')
->join('App_type', 'App_type.app_id = App.id', 'left outer')
->join('App_formate', 'App_formate.app_id = App.id', 'left outer')      
->where_in('App.id',$subquery,false)  //<-- pass 3rd parameter as false for removing single quotes
->where_in('App_formate.formate_id',$data['selected_formates'])
->where('App.ope_min <=',$data['ope_value'])
->where('App.ope_max >=',$data['ope_value'])    
->group_by("App.id", "desc")
->get();

回答6:

I think your $subquery was considered as a parameter string, not as a query.
The general syntax of active record method is

->where_in('field_name', array() || 'string values');

You $subquery have to be like this

$subquery = $this->db
        ->select('app_id')
        ->from('App_type')
        ->where_in('type_id',array(3,2,6))
        ->group_by('app_id')
        ->having(' COUNT(*) = 3')
        ->get()
        ->row()
        ->app_id;

Hope it will work :)

回答7:

Instead of

SELECT (*)

use

SELECT *

Instead of

WHERE `App`.`id` IN(
    select app_id FROM App_type
        WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3)

use

JOIN (
    select app_id FROM App_type
        WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3
     ) AS x  ON x.app_id = App.id

(Translating into CodeIgniter is left as an exercise to the reader.)

回答8:

First of all after the query, put this code:

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

This will print the query, and you will have an idea where's the issue. Also copy that query and try to run it in phpmyadmin.

Also try this type of solution as you have single quote issues:

In subquery write $type_count using single quotes i.e '$type_count'

->where("App.ope_min <='",$data['ope_value']."'")
->where("App.ope_max >='",$data['ope_value']."'")    
->group_by("App.id", "desc")
->get();

Let me know if you want to ask something else.

回答9:

Just add a 3rd parameter in where_in statement. Try this -

$subquery = "select app_id FROM app_type WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3";
    $search_app_query = $this->db
    ->select('*')
    ->from('app')
    ->join('app_type', 'app_type.app_id = app.id', 'left outer')
    ->join('app_formate', 'app_formate.app_id = app.id', 'left outer')      
    ->where_in('app.id',$subquery, FALSE)  
    ->where_in('app_formate.formate_id',array(1,3))
    ->where('app.ope_min <=',3)
    ->where('app.ope_max >=',3)    
    ->group_by("app.id", "desc")
    ->get()->result();

回答10:

I would suggest you not to put sub query directly in where in condition because you may also sometimes have zero result. So the best way is to execute your subquery separately like this.

$subdata = $this->db->select("app_id ")->from("App_type ")->where_in("type_id",$selected_type)->group_by(""app_id)->having("COUNT(*) = $type_count")->get()->result_array();

$search_app_query = $this->db
    ->select('*')
    ->from('App')
    ->join('App_type', 'App_type.app_id = App.id', 'left outer')
    ->join('App_formate', 'App_formate.app_id = App.id', 'left outer');
if(!empty($subdata)) {     
    $this->db->where_in('App.id',$subdata);
}
    $this->db->where_in('App_formate.formate_id',$data['selected_formates'])
    ->where('App.ope_min <=',$data['ope_value'])
    ->where('App.ope_max >=',$data['ope_value'])    
    ->group_by("App.id", "desc")
    ->get(); 

回答11:

Thanks To All of you for your suggestions.

I have Find the way to solve my problem without altering my other code.

Thanks to sintakonte-SO user his comment has been the key to solve this problem.

The Solution is just the change of one line code which earlier i want.

$search_app_query = $this->db
    ->select('*')
    ->from('App')
    ->join('App_type', 'App_type.app_id = App.id', 'left outer')
    ->join('App_formate', 'App_formate.app_id = App.id', 'left outer') 

    // This is what he suggested me to change and it works. 
    ->where('App.id IN('.$subquery.')')     

    ->where_in('App_formate.formate_id',$data['selected_formates'])
    ->where('App.ope_min <=',$data['ope_value'])
    ->where('App.ope_max >=',$data['ope_value'])    
    ->group_by("App.id", "desc")
    ->get();

Many thanks to sintakonte-SO user again.