ActiveRecord: where not in select ? - Printable Version +- 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: ActiveRecord: where not in select ? (/showthread.php?tid=42943) |
ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]Piero[/eluser] Hi, I have 3 tables, contact, contact_group and group, which allow me contact management by group. The contact_group table has 2 fields, id_contact and id_group, referencing to the primary keys of the 2 other tables. I want to get the contact entries where the contact.id is NOT in the contact_group table How can I achieve this with active record...? Thanks. ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]Piero[/eluser] So at the end I did it with $this->db->query(): $this->db->query(' SELECT * FROM contact c WHERE NOT EXISTS ( SELECT id_contact FROM contact_group cg WHERE c.id = cg.id_contact ) '); That returns what I was looking for, but apparently it's not possible to do it with the Active Record methods.. ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]iain.b[/eluser] Came across this very same thing today, seems like subqueries are easiest done without using active record. ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]pmoroom[/eluser] [quote author="iain.b" date="1308945289"]Came across this very same thing today, seems like subqueries are easiest done without using active record.[/quote] That's how I do it yes. ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]toopay[/eluser] I will say that CI AR is actually not really using AR pattern. It kinda just like a helper, with a lot limitation (nested where, subquery, etc). And if you already comfort with writing SQL directly, you will face some ackward when using CI AR. Actually, thats why i create my own db helper, for generates my needs of crafting SQL with object orientation. ActiveRecord: where not in select ? - El Forum - 06-24-2011 [eluser]pmoroom[/eluser] [quote author="toopay" date="1308978220"]I will say that CI AR is actually not really using AR pattern. It kinda just like a helper, with a lot limitation (nested where, subquery, etc). And if you already comfort with writing SQL directly, you will face some ackward when using CI AR. Actually, thats why i create my own db helper, for generates my needs of crafting SQL with object orientation.[/quote] I found it to be the same as you. It's great for basic things, but for more complex you need to go back to standard SQL or perhaps there is a way to do it in AR CI, but it's not worth the effort to work it out |