• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
ActiveRecord: where not in select ?

#1
[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.

#2
[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..

#3
[eluser]iain.b[/eluser]
Came across this very same thing today, seems like subqueries are easiest done without using active record.

#4
[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.

#5
[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.

#6
[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 Smile


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.