mysql - rails : can't get the attributes from the joined table

Question

当我连接两个表(rails 2.2.2)时，rails 仅返回应用 find_by 方法的模型中的属性值:

关系:

class User < ActiveRecord::Base
  ..
  has_one  :company_info, :dependent => :destroy
  has_many :preferred_categories, :dependent => :destroy
end


class PreferredCategory < ActiveRecord::Base
  ..
  belongs_to :user
  belongs_to :category

end


class Category < ActiveRecord::Base
  ..
  has_many :preferred_categories, :dependent => :destroy
  has_many :users, :through => :preferred_categories
end


class CompanyInfo < ActiveRecord::Base
  ..
  belongs_to :user 
end

查询:

class Category < ActiveRecord::Base
  ..
  def total_tradesmen   #returns tradesmen with a certain skill-profile
  a = self.self_and_all_children_for.collect {|cat| cat.id}
  total_tradesmen = User.find_by_sql(
    "select *
    from users u
    inner join preferred_categories pc on pc.user_id = u.id
    inner join company_infos ci on ci.user_id = pc.user_id
    where pc.category_id in ( #{a.join(',')} )"
  )
  end
..
end

=> 结果:我仅从用户表中获取属性。

我还需要其他表(preferred_categories、company_infos)中的属性。有什么想法吗？

Answer 1

就您的情况而言，我认为 ActiveRecord 可以处理您的查询。您应该使用 include 预先加载所需的关联。

total_tradesmen = User.all(
  :include => [:company_info, :preferred_categories],
  :conditions => {:preferred_categories => {:category_id => a}}
)

然后访问关联模型上所需的属性:

# Examples since I don't know the attributes on your models
company_address = total_tradesmen.first.company_info._address_
preferred_category_names = total_tradesmen.first.preferred_categories.map(&:_name_)

更新尝试显式指定连接

total_tradesmen = User.all(
  :include => [:company_info, :preferred_categories],
  :joins => [:company_info, :preferred_categories],
  :conditions => {:preferred_categories => {:category_id => a}}
)