Issue with CodeIgniter subquery

Question

$query=$this->db->query('SELECT g.gallery_id, g.gallery_title, (
                           SELECT i.gallery_image_path
                           FROM nepal_gallery_image AS i
                           WHERE i.gallery_id = g.gallery_id
                           AND i.gallery_image_status =  "Enabled"
                           LIMIT 0 , 1) AS gallery_image_path
                         FROM nepal_gallery AS g
                         WHERE g.gallery_status =  "Enabled"');

The above query gives the correct output.

However, when I use the CI subquery library:

$this->load->library('Subquery');
$this->db->select('g.gallery_id,g.gallery_title');
$sub = $this->subquery->start_subquery('select');
$sub ->select('i.gallery_image_path')
     ->from('nepal_gallery_image AS i')                 
     ->where(array('i.gallery_image_status'=>"Enabled",  
                   'i.gallery_id'=>"g.gallery_id")) 
     ->limit(0,1);
$this->subquery->end_subquery('gallery_image_path'); 
$this->db->from('nepal_gallery AS g');
$this->db->where('g.gallery_stats',"Enabled");
$query=$this->db->get();

the resulting query is:

SELECT `g`.`gallery_id` , `g`.`gallery_title` , (
  SELECT `i`.`gallery_image_path`
  FROM (`nepal_gallery_image` AS i)
  WHERE `i`.`gallery_image_status` = 'Enabled'
  AND `i`.`gallery_id` = 'g.gallery_id'
  LIMIT 0 , 1 ) AS gallery_image_path
FROM (`nepal_gallery` AS g)
WHERE `g`.`gallery_status` = 'Enabled'

and the output is not as expected.

the output in first query is

    <table>
     <tr>
         <th> gallery_id</th>
         <th> gallery_title</th>
         <th> gallery_image_path </th>
     </tr>
     <tr>
        <td>1</td>
        <td>Gallery 1</td>
        <td>images/image1.jpg</td>
     </tr>
     <tr>
        <td>2</td>
        <td>Gallery 2</td>
        <td>images/image2.jpg</td>
     </tr>
     </table>

However, in later query, the output is

    <table>
     <tr>
         <th> gallery_id</th>
         <th> gallery_title</th>
         <th> gallery_image_path </th>
     </tr>
     <tr>
        <td>1</td>
        <td>Gallery 1</td>
        <td>NULL</td>
     </tr>
     <tr>
        <td>2</td>
        <td>Gallery 2</td>
        <td>NULL</td>
     </tr>
     </table>

It seems the problem is with the subquery.

Can anyone please help me with this?

Answer 1

the problem is that the subqueries where condition is taking your table alias g as string 'g.gallery_id' one way to avoid the CI's protect fields is by passing third parameter as false in where condition so CI will not try to protect your field or table names with backticks.

try this

 $sub ->select('i.gallery_image_path')
 ->from('nepal_gallery_image AS i')                 
 ->where(array('i.gallery_image_status'=>"Enabled",  
               'i.gallery_id'=>"g.gallery_id"), NULL, FALSE);
                                           //--^^^^^^^^^^^^^^--here 
 ->limit(0,1);

or the simply write your clauses manaully

  $where = "i.gallery_id = g.gallery_id AND i.gallery_image_status =  'Enabled'";
  $sub ->select('i.gallery_image_path')
 ->from('nepal_gallery_image AS i')                 
 ->where($where,NULL,FALSE); 
 ->limit(0,1);

Answer 2

 ->where(array('i.gallery_image_status'=>"Enabled",  
               'i.gallery_id'=>"g.gallery_id")) 

The problem is with 'i.gallery_id'=>"g.gallery_id" - CI is building the query with "g.gallery_id" as being a literal String... when it aught to be a table column reference. If you manually replace - "g.gallery_id" with 'g'.'gallery_id' it should serve as a demonstration.

If you don't know how to remedy that, please leave a comment. (it may just be a matter of using single quotes).