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)'
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.id not 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.