Rails - Jul 2007 - "Subselect" type functionality in ActiveRecord

Hi

Is there any way (Using my Model clases) to be able to do a sort of
subselect.

Ex: select a, b, c from table_a where a not in (SELECT aa from table_b
);

I really hope I don''t have to select all the models from each table and
us ''each'' on ModelA list and ''find'' on the
ModelB list.

I"m still learning, but as I use this I find more and more.. CRUD
ActiveRecord, anything else hit the DB directly.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jean Nibee wrote:
> Is there any way (Using my Model clases) to be able to do a sort of
> subselect.
> 
> Ex: select a, b, c from table_a where a not in (SELECT aa from table_b
> );
> 
> I really hope I don''t have to select all the models from each
table and
> us ''each'' on ModelA list and ''find'' on
the ModelB list.
> 
> I"m still learning, but as I use this I find more and more.. CRUD >
ActiveRecord, anything else hit the DB directly.
Yes, just have :conditions => ''a not in (SELECT aa from
table_b)''

-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Mark Reginald James wrote:
> Jean Nibee wrote:
> 
>> ActiveRecord, anything else hit the DB directly.
> Yes, just have :conditions => ''a not in (SELECT aa from
table_b)''
> 
> --
> We develop, watch us RoR, in numbers too big to ignore.
Well that seems easy enough. :)

Thank you..

I also discovered find_by_sql() last night too and that can also do the 
job, just with less ''Rails'' and more ''Sql
dependant'' code.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Mark Reginald James wrote:
> Jean Nibee wrote:
> 
>> ActiveRecord, anything else hit the DB directly.
> Yes, just have :conditions => ''a not in (SELECT aa from
table_b)''
> 
> --
> We develop, watch us RoR, in numbers too big to ignore.
But is there a way to do this without writing SQL? e.g.

   :conditions => [ ''a not in ?'', Aaaa.find(...).the_sql ]

or some other even cleaner way?


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

A more "rails" olution might be to do something like this:

array_of_ids_of_users_to_skip =
User.find_all_by_status("disabled").collect{|u|
u.id}
@profiles = Profile.find :all, :include=>[:users],
:conditions=>["users.idnot in(?)", array_of_ids_of_users_to_skip]

This would result in two database hits, but it may perform better.

I would caution that subselects in the WHERE clause can be bad unless your
database is smart enough to handle them.  Some databases would execute that
subselect once per every row in the table.  (Where clauses are usually run
for every row)

Again, this all depends on your database. You have a development.log file
that shows you all the sql statements that the app runs. Take those and run
them through your database''s query analyzer / explain plan.

This code is typed on the fly, not tested. You should be able to grasp the
basic idea though. Let me know how it all goes.


On 7/18/07, Jaime Cham
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
>
> Mark Reginald James wrote:
> > Jean Nibee wrote:
> >
> >> ActiveRecord, anything else hit the DB directly.
> > Yes, just have :conditions => ''a not in (SELECT aa from
table_b)''
> >
> > --
> > We develop, watch us RoR, in numbers too big to ignore.
>
> But is there a way to do this without writing SQL? e.g.
>
>    :conditions => [ ''a not in ?'',
Aaaa.find(...).the_sql ]
>
> or some other even cleaner way?
>
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---