+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: DB Lib: active record: HOW TO "WHERE NOT EXISTS" (/showthread.php?tid=57168)
DB Lib: active record: HOW TO "WHERE NOT EXISTS" - El Forum - 02-22-2013
[eluser]Jan_1[/eluser]
I want to get all lines of the table 'contacts', which are not yet in the table 'customers'.
Code:
SELECT contacts.id,
contacts.etc
FROM contacts
WHERE NOT EXISTS ( SELECT 1
FROM customers
WHERE customers.id_source = contacts.id
AND customers.table_source = contacts.source
)What is the way to use "where_not_exists()" in active record class, please?
like $this->db->where...
Have tryed without success:
Code:
$sql = "WHERE NOT EXISTS(...)";
$this->db->query($sql);DB Lib: active record: HOW TO "WHERE NOT EXISTS" - El Forum - 02-22-2013
[eluser]Otemu[/eluser]
Hi,
Try something like this:
Code:
$this->db->where('NOT EXISTS (//your query)', '', FALSE);Quote:$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
$query = $this->db->get('mytable');
Active record doesn't allow you to do sub-queries, there are some workarounds or libraries if you Google. If it giving you to much hassle just use normal mySQL to do this query, although active record can be quite handy there many times you end up wasting time for hardly any benefit.
DB Lib: active record: HOW TO "WHERE NOT EXISTS" - El Forum - 02-22-2013
[eluser]Aken[/eluser]
Could also just do a left join and check for null values. Google around, there's examples of the query.