Forum: Ruby on Rails "Subselect" type functionality in ActiveRecord

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jean N. (Guest)
on 2007-07-12 08:32
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.
Mark Reginald J. (Guest)
on 2007-07-12 14:05
(Received via mailing list)
Jean N. 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.
Jean N. (Guest)
on 2007-07-12 19:25
Mark Reginald J. wrote:
> Jean N. 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.
Jaime C. (Guest)
on 2007-07-18 12:41
Mark Reginald J. wrote:
> Jean N. 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?
Brian H. (Guest)
on 2007-07-18 18:59
(Received via mailing list)
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.
This topic is locked and can not be replied to.