CODEWITHSUNDEEP
phpcodeigniteractiverecordsubqueryquery-builder
SM Khairul
SM Khairul

Reputation: 11

Convert SELECT query containing a LEFT JOINed subquery to CodeIgniter active record script

I have sql query as below, I want to convert the raw SQL to CodeIgniter query building methods.

SELECT *
FROM color c 
LEFT JOIN (
    SELECT pc.*
    FROM product_color pc
    LEFT JOIN product p ON pc.product_id = p.product_id
    WHERE p.product_id = 1
) x ON c.id = x.color_id

Upvotes: 1

Views: 69

Answers (3)

mickmackusa
mickmackusa

Reputation: 47864

Your intended query doesn't need a LEFT JOINed subquery; the SQL can be simplified to just two LEFT JOINs.

SELECT c.*
FROM color c
LEFT JOIN product_color pc ON c.id = pc.color_id
LEFT JOIN product p ON pc.product_id = p.product_id
WHERE p.product_id = 1;

or more concisely with USING:

SELECT c.*
FROM color c
LEFT JOIN product_color pc ON c.id = pc.color_id
LEFT JOIN product p USING (product_id)
WHERE p.product_id = 1;

The chained query builder script can be achieved this way:

return $this->db
    ->select('c.*')
    ->join('product_color pc', 'c.id = pc.color_id', 'LEFT')
    ->join('product p', 'product_id', 'LEFT')
    ->get_where('color c', ['p.product_id' => 1])
    ->result();

The above script will generate a SQL query with all table and column name properly quoted and will return an array of zero or more objects.

Upvotes: 0

Mohit Rathod
Mohit Rathod

Reputation: 1193

Try this into model file. and it's always better to keep sql keywords in capital.

    $query = "SELECT * FROM color c 
        LEFT JOIN (SELECT pc.* FROM product_color pc
        LEFT JOIN product p ON pc.product_id=p.product_id
        WHERE p.product_id=1)x on c.id=x.color_id"

    $query = $this->db->query($query);
    $data =  $query->result_array();

Upvotes: 1

jagad89
jagad89

Reputation: 2643

In codeigniter you can get compiled query using get_compiled_select function.

Let's create inner query first. 

     $inner_query = $this->db->select('pc.*')
     ->from('product_color pc')
     ->join('product p','pc.product_id = p.product_id','left')
     ->where('p.product_id',1)
     ->get_compiled_select();

Now we will use inner query to create our final query.

 $final_query = $this->db->select('*')
 ->from('color c')
 ->join("($inner_query) x",'c.id=x.color_id','left')
  ->get_compiled_select();
echo $final_query; die;

Upvotes: 1

Related Questions