How to convert Sql query to codeigniter Active Record
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;
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();