Fred Willmore Fred Willmore - 4 months ago 15
MySQL Question

ActiveRecord select does not create accessor method when relation is ordered by attribute of joined model

I am using Ruby 2.1, Rails 4.2, and mysql2 (0.4.4). My app has the following 2 models:

class Batch < ActiveRecord::Base
belongs_to :batch_type
end

class BatchType < ActiveRecord::Base
has_many :batches
end


Created with the following migrations:

class CreateBatches < ActiveRecord::Migration
def change
create_table :batches do |t|
t.integer :batch_type_id
t.string :title

t.timestamps
end
end
end

class CreateBatchTypes < ActiveRecord::Migration
def change
create_table :batch_types do |t|
t.string :short_name
t.timestamps
end
end
end


I seeded the database with the following:

BatchType.create short_name: 'Type 1'
Batch.create batch_type: BatchType.first


I am trying to use a dynamically created attribute accessor named 'duration', which is the difference between the timestamps for
created_at
and
updated_at
. The accessor is available when I order the result by the
title
attribute:

2.1.0 :007 > Batch.order('title').select('batches.*', 'IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration').includes(:batch_type).first.duration
Batch Load (0.7ms) SELECT batches.*, IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration FROM `batches` ORDER BY title LIMIT 1
BatchType Load (0.7ms) SELECT `batch_types`.* FROM `batch_types` WHERE `batch_types`.`id` IN (1)
=> 0


But the accessor is not available when I order the result by an attribute on the joined model
BatchType
:

2.1.0 :008 > Batch.order('batch_types.short_name').select('batches.*', 'IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration').includes(:batch_type).first.duration
SQL (0.6ms) SELECT batches.*, IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration, `batches`.`id` AS t0_r0, `batches`.`batch_type_id` AS t0_r1, `batches`.`batch_status_id` AS t0_r2, `batches`.`title` AS t0_r3, `batches`.`created_at` AS t0_r4, `batches`.`updated_at` AS t0_r5, `batch_types`.`id` AS t1_r0, `batch_types`.`short_name` AS t1_r1, `batch_types`.`created_at` AS t1_r2, `batch_types`.`updated_at` AS t1_r3 FROM `batches` LEFT OUTER JOIN `batch_types` ON `batch_types`.`id` = `batches`.`batch_type_id` ORDER BY batch_types.short_name LIMIT 1
NoMethodError: undefined method `duration' for #<Batch:0x0000010125ede0>


How is the order of the result affecting the creation of accessors by
select
?

Answer

I think you're abusing includes a bit. includes is intended to mash several tables together into one query to reduce the number of queries you need to do. If you just want to JOIN then use joins:

Batch.order('title')
     .select('batches.*', 'IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration')
     .joins(:batch_type)
     .first
     .duration

Alternatively, add a method to Batch that calculates the duration in Ruby and keep using includes:

class Batch < ActiveRecord::Base
  def duration
    updated_at - created_at
  end
  #...
end

I haven't been able to find a way to make select, includes, and order work together they way you need them too, sorry.


Here comes a bit of hand waving and conjecture. You'd need to dig through the ActiveRecord source to figure what is really going on that is a very non-trivial task, there be dragons and other fearsome beasts.

The generated t0_r0 ... column names in the SELECT are probably at the root of the problem:

SELECT batches.*,
       IF(batches.updated_at AND batches.created_at, batches.updated_at - batches.created_at, 0) AS duration,
       `batches`.`id` AS t0_r0, ...
       `batch_types`.`id` AS t1_r0, ...

When you use includes, you're telling ActiveRecord that you want it to do one query with everything mashed together (hence the LEFT JOIN and funny looking tN_rM column aliases you see in the SQL) and then ActiveRecord has to pull all the different models out of the combined query. Along the way it probably forgets about your select completely because it has to build its own SELECT clause in a very specific format; there's a conflict here: calling select tells AR to look at the SELECT to figure out what the query returns and what methods should be available on the model instances, calling includes tells AR to build its own SELECT.

In your first query:

Batch.order('title')...

AR isn't forced to do a JOIN so it doesn't do one and your SELECT gets through the bowels of ActiveRecord unscathed. In your second query:

Batch.order('batch_types.short_name')...

AR is forced to do a JOIN and that activates the SELECT mangling logic that sends everything sideways.

includes is a bit of a hack so it isn't surprising that it is a little fragile.

I'd call this a bug in AR, it should either complain that you're confusing its little mind with your select call or it should do it right.