Rails eager loading seems to be querying wrong

I'm attempting to eager load in my Rails 3 app. I've narrowed it down to a very basic sample, and instead of generating the one query I'm expecting, it's generating 4.

First, here's a simple breakdown of my models.

class Profile < ActiveRecord::Base
  belongs_to :gender

  def to_param
    self.name
  end
end

class Gender < ActiveRecord::Base
  has_many :profiles, :dependent => :nullify
end

I then has a ProfilesController::show action, where's I'm querying for the model.

def ProfilesController < ApplicationController
  before_filter :find_profile, :only => [:show]

  def show
  end

  private

    def find_profile
      @profile = Profile.find_by_username(params[:id], :include => :gender)
      raise ActiveRecord::RecordNotFound, "Page not found" unless @profile
    end
end

When I look at the queries this generates, it shows the following:

SELECT `profiles`.* FROM `profiles` WHERE `profiles`.`username` = 'matt' LIMIT 1
SELECT `genders`.* FROM `genders` WHERE (`genders`.`id` = 1)

What I expected to see is a single query:

SELECT `profiles`.*, `genders`.* FROM `profiles` LEFT JOIN `genders` ON `profiles`.gender_id = `genders`.id WHERE `profiles`.`username` = 'matt' LIMIT 1

Anyone know what I'm doing wrong here? Everything I've found on eager loading makes it sound like this should work.

Edit: After trying joins, as recommended by sled, I'm still seeing the same results.

The code:

@profile = Profile.joins(:gender).where(:username => params[:id]).limit(1).first

The query:

SELECT `profiles`.* FROM `profiles` INNER JOIN `genders` ON `genders`.`id` = `profiles`.`gender_id` WHERE `profiles`.`username` = 'matt' LIMIT 1

Again, you can see no genders data is being retrieved, and so a second query to genders is being made.

I even tried adding a select, to no avail:

@profile = Profile.joins(:gender).select('profiles.*, genders.*').where(:username => params[:id]).limit(1).first

which correctly resulted in:

SELECT profiles.*, genders.* FROM `profiles` INNER JOIN `genders` ON `genders`.`id` = `profiles`.`gender_id` WHERE `profiles`.`username` = 'matt' LIMIT 1

...but it still performed a second query on genders later when accessing @profile.gender's attributes.

Edit 2: I also tried creating a scope that includes both select and joins in order to get all the fields I r开发者_Python百科equire, (similar to the custom left join method sled demonstrated). It looks like this:

class Profile < ActiveRecord::Base
  # ...
  ALL_ATTRIBUTES = [:photo, :city, :gender, :relationship_status, :physique, :children,
    :diet, :drink, :smoke, :drug, :education, :income, :job, :politic, :religion, :zodiac]

  scope :with_attributes,
    select((ALL_ATTRIBUTES.collect { |a| "`#{reflect_on_association(a).table_name}`.*" } + ["`#{table_name}`.*"]).join(', ')).
    joins(ALL_ATTRIBUTES.collect { |a|
      assoc = reflect_on_association(a)
      "LEFT JOIN `#{assoc.table_name}` ON `#{table_name}`.#{assoc.primary_key_name} = `#{assoc.table_name}`.#{assoc.active_record_primary_key}"
    }.join(' '))
  # ...
end

This generates the following query, which appears correct:

SELECT `photos`.*, `cities`.*, `profile_genders`.*, `profile_relationship_statuses`.*, `profile_physiques`.*, `profile_children`.*, `profile_diets`.*, `profile_drinks`.*, `profile_smokes`.*, `profile_drugs`.*, `profile_educations`.*, `profile_incomes`.*, `profile_jobs`.*, `profile_politics`.*, `profile_religions`.*, `profile_zodiacs`.*, `profiles`.* FROM `profiles` LEFT JOIN `photos` ON `profiles`.photo_id = `photos`.id LEFT JOIN `cities` ON `profiles`.city_id = `cities`.id LEFT JOIN `profile_genders` ON `profiles`.gender_id = `profile_genders`.id LEFT JOIN `profile_relationship_statuses` ON `profiles`.relationship_status_id = `profile_relationship_statuses`.id LEFT JOIN `profile_physiques` ON `profiles`.physique_id = `profile_physiques`.id LEFT JOIN `profile_children` ON `profiles`.children_id = `profile_children`.id LEFT JOIN `profile_diets` ON `profiles`.diet_id = `profile_diets`.id LEFT JOIN `profile_drinks` ON `profiles`.drink_id = `profile_drinks`.id LEFT JOIN `profile_smokes` ON `profiles`.smoke_id = `profile_smokes`.id LEFT JOIN `profile_drugs` ON `profiles`.drug_id = `profile_drugs`.id LEFT JOIN `profile_educations` ON `profiles`.education_id = `profile_educations`.id LEFT JOIN `profile_incomes` ON `profiles`.income_id = `profile_incomes`.id LEFT JOIN `profile_jobs` ON `profiles`.job_id = `profile_jobs`.id LEFT JOIN `profile_politics` ON `profiles`.politic_id = `profile_politics`.id LEFT JOIN `profile_religions` ON `profiles`.religion_id = `profile_religions`.id LEFT JOIN `profile_zodiacs` ON `profiles`.zodiac_id = `profile_zodiacs`.id WHERE `profiles`.`username` = 'matt' LIMIT 1

Unfortunately, it doesn't seem that calls to relationship attributes (e.g.: @profile.gender.name) are using the data that was returned in the original SELECT. Instead, I see a flood of queries following this first one:

Profile::Gender Load (0.2ms)  SELECT `profile_genders`.* FROM `profile_genders` WHERE `profile_genders`.`id` = 1 LIMIT 1
Profile::Gender Load (0.4ms)  SELECT `profile_genders`.* FROM `profile_genders` INNER JOIN `profile_attractions` ON `profile_genders`.id = `profile_attractions`.gender_id WHERE ((`profile_attractions`.profile_id = 2))
City Load (0.4ms)  SELECT `cities`.* FROM `cities` WHERE `cities`.`id` = 1 LIMIT 1
Country Load (0.3ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`id` = 228 ORDER BY FIELD(code, 'US') DESC, name ASC LIMIT 1
Profile Load (0.4ms)  SELECT `profiles`.* FROM `profiles` WHERE `profiles`.`id` = 2 LIMIT 1
Profile::Language Load (0.4ms)  SELECT `profile_languages`.* FROM `profile_languages` INNER JOIN `profile_profiles_languages` ON `profile_languages`.id = `profile_profiles_languages`.language_id WHERE ((`profile_profiles_languages`.profile_id = 2))
SQL (0.3ms)  SELECT COUNT(*) FROM `profile_ethnicities` INNER JOIN `profile_profiles_ethnicities` ON `profile_ethnicities`.id = `profile_profiles_ethnicities`.ethnicity_id WHERE ((`profile_profiles_ethnicities`.profile_id = 2))
Profile::Religion Load (0.5ms)  SELECT `profile_religions`.* FROM `profile_religions` WHERE `profile_religions`.`id` = 2 LIMIT 1
Profile::Politic Load (0.2ms)  SELECT `profile_politics`.* FROM `profile_politics` WHERE `profile_politics`.`id` = 3 LIMIT 1

---

your example is fine and it will end up in two queries because that's how eager loading is implemented in rails. It becomes handy if you have many associated records. You can read more about it here

What you probably want is a simple join:

@profile = Profile.joins(:gender).where(:username => params[:id])

Edit

If the profile consists of many pieces there are multiple approaches here:

Custom left joins - maybe there is a plugin out there which does the job otherwise I'd suggest to do something like:

class Profile < ActiveRecord::Base

  # .... code .....

  def self.with_dependencies

    attr_joins    = []
    attr_selects  = []

    attr_selects << "`profiles`.*"
    attr_selects << "`genders`.*"
    attr_selects << "`colors`.*"

    attr_joins << "LEFT JOIN `genders` ON `gender`.`id` = `profiles`.gender_id"
    attr_joins << "LEFT JOIN `colors` ON `colors`.`id` = `profiles`.color_id"

    prep_model  = select(attr_selects.join(','))

    attr_joins.each do |c_join|
      prep_model = prep_model.joins(c_join)
    end

    return prep_model
  end

end

Now you could do something like:

@profile = Profile.with_dependencies.where(:username => params[:id])

Another solution is to use the :include => [:gender, :color] it may be some queries more but it's the cleaner "rails way". If you run into performance issues you may want to rethink your DB Schema but do you have really such a heavy load?

A friend of mine wrote a nice little solution for this simple 1:n relations (like genders) it's called simple_enum

---

After working with sled's suggestions, I finally came up with this solution. I'm sure it could be made cleaner with a plugin, but here's what I've got for now:

class Profile < ActiveRecord::Base
  ALL_ATTRIBUTES = [:photo, :city, :gender, :relationship_status, :physique, :children,
    :diet, :drink, :smoke, :drug, :education, :income, :job, :politic, :religion, :zodiac]

  scope :with_attributes,
    includes(ALL_ATTRIBUTES).
    select((ALL_ATTRIBUTES.collect { |a| "`#{reflect_on_association(a).table_name}`.*" } + ["`#{table_name}`.*"]).join(', '))
end

The two main points are:

• A call to includes, which passes the symbols of the relationships I want
• A call to select that makes sure to retrieve all columns for the related tables. Note that I call reflect_on_association so that I don't have to hard-code the related tables' names, letting the Rails models do the work for me.

I can now call:

Profile.with_attributes.where(:username => params[:id]).limit(1).first

Going to mark sled's answer as correct since it's his help (answers + comments combined) that led me here, even though this is the code I'm ultimately using.