How can I write the following SQL query in Codeigniter and using Query Builder Class
SELECT * FROM products AND id NOT IN (SELECT product_id FROM purchases)
Add a comment
|
2 Answers
Codeigniter's Query Builder Class comes with the function where_not_in() but you cannot use it with a subquery or custom string. On the other hand you can use the custom string of the where() function this way:
$query=$this->db->where('id NOT IN (SELECT `product_id` FROM `purchases`)')
->get('products');
echo $this->db->last_query();die;
outputs:
SELECT * FROM `products` WHERE `id` NOT IN (SELECT `product_id` FROM `purchases`)
note: the query of your question has a syntax error, replace AND with WHERE
Comments
This will be the solution if you're strictly looking for CodeIgniter query but if you don't mind mingling it with normal SQL queries then @Vickel's answer should do the deed.
//first get the data you want to ignore-
$not_in_arr = $this->db->select('product_id')->from('purchases')->get()->result();
where_in and where_not_in expect you to pass an indexed array as the 2nd parameter.
//get the data in indexed array format-
foreach($not_in_arr as $not){
$notin[] = $not->product_id;
}
$query = $this->db->select('*')->from('products')->where_not_in('id', $notin)->get()->result();
echo $this->db->last_query(); //returns- SELECT * FROM `products` WHERE `id` NOT IN('1', '2', '3')
Hope this helps you. :)
