无法在 Rails 中加入自联接表 [英] Unable to join self-joins tables in Rails

问题描述

我有两个模型

class Category < ActiveRecord::Base
  belongs_to :parent, :class_name => "Category"
  has_many :children,  :class_name => "Category", :foreign_key => "parent_id"
  has_many :products
  attr_accessible :description, :title, :parent

end

class Product < ActiveRecord::Base
  belongs_to :category
end

特别是，Category 有一个名为tea"的父项，而这个项有很多子项:红茶"、白茶"...

In particular, Category has a parent item titled "tea" and this item has many children items: "black tea", "white tea"...

我需要选择属于父类别茶"的产品.这是我的做法:

I need to select products that belong to a parent category "tea". Here is how I'm doing that:

Product.joins(:category=>:parent).where(:category=>{:parent=>{:id=>1}}).all

它抛出异常(无法很好地格式化)

It throws an exception (unable to format it well)

Product Load (0.8ms)  SELECT `products`.* FROM `products` INNER JOIN `categories` ON `categories`.`id` = `products`.`category_id` INNER JOIN `categories` `parents_categories` ON `parents_categories`.`id` = `categories`.`parent_id` WHERE `parent`.`id` = 1

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'parent.id' in 'where clause': SELECT `products`.* FROM `products` INNER JOIN `categories` ON `categories`.`id` = `products`.`category_id` INNER JOIN `categories` `parents_categories` ON `parents_categories`.`id` = `categories`.`parent_id` WHERE `parent`.`id` = 1

因为未知的 parent.id 列.

because of unknown parent.id column.

显然，查询应该是(它工作完美):

Obviously, the query should be (it's working perfect):

    SELECT `products`.* FROM `products` 
    INNER JOIN `categories` ON `categories`.`id` = `products`.`category_id` 
INNER JOIN `categories` as `parents_categories` ON `parents_categories`.`id` = `categories`.`parent_id` WHERE `parents_categories`.`id` = 1

我什至尝试过

Product.joins(:category=>:parent).where(:category.parent=>{:id=>1}).all

它没有帮助

请说出你的想法.

推荐答案

虽然这里 joins() 的操作很聪明，但 where() 部分不是'太聪明了AFAIK 它对连接一无所知，实际上只是将其参数转换为字符串.因此，试试这个:

While the operation of joins() here is pretty smart, the where() part isn't so clever. AFAIK it doesn't know anything about the joins and really just converts its arguments to strings. As such, try this:

Product.joins(:category=>:parent).where(:parents_categories=>{:id=>1})

为了避免将 AR 内部使用的名称泄露到您的代码中，请考虑使用 AREL 进行查询.最近有一些关于这个主题的精彩节目.

In order to avoid bleeding the name used internally by AR to your code, consider using AREL for your query. There have been some excellent railscasts on that subject recently.

这篇关于无法在 Rails 中加入自联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！