I'm using an ActiveRecord scope as a discriminator then passing it to another function for the actual query. It's generating what I consider to be an extra SQL transaction, and since this is in an inner loop, I'd like to optimize it. First the schema:
create_table "thermal_models", :force => true do |t| t.integer "natural_resource_id" t.integer "premise_attribute_name_id" t.integer "premise_group_name_id" t.integer "table_type" t.float "x" t.float "y" t.float "m" t.timestamps end
and the scope (e.g):
myscope = ThermalModel.scoped.where(:natural_resource_id => 1, :premise_attribute_name_id => 5, :premise_group_name_id => 1, :table_type => 7)
Then, this query:
segments = myscope.where("x < 18.7").order("x DESC").limit(1)
generates the following TWO transactions. I guess the first transaction is simply generating a count of the result -- the subquery is not sorted and its outer query simply counts how many 1's the subquery emitted -- but I don't see its utility.
Am I misunderstanding how scopes are meant to be used? Is there something I can do (short of writing direct SQL) that will get this down to one transaction?
SQL (2.5ms) SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `thermal_models` WHERE (`thermal_models`.`natural_resource_id` = 1) AND (`thermal_models`.`premise_attribute_name_id` = 5) AND (`thermal_models`.`premise_group_name_id` = 1) AND (`thermal_models`.`table_type` = 7) AND (x < 18.7) LIMIT 1) AS subquery ThermalModel Load (1.4ms) SELECT `thermal_models`.* FROM `thermal_models` WHERE (`thermal_models`.`natural_resource_id` = 1) AND (`thermal_models`.`premise_attribute_name_id` = 5) AND (`thermal_models`.`premise_group_name_id` = 1) AND (`thermal_models`.`table_type` = 7) AND (x < 18.7) ORDER BY x DESC LIMIT 1
- ff
P.S.: Despite the above, I think scopes are the bees knees. They let you define modules that act on specific columns of a table while leaving the actual _selection_ of rows to another piece of code. It essentially allows you to do duck typing for SQL.