"Subselect" type functionality in ActiveRecord

Rails
1

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.

2

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.

3

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?

4

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. :slight_smile:

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.

5

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.