RAILS 3 的复杂查询有很多并且属于

发布于 2024-11-11 06:59:51 字数 1080 浏览 2 评论 0原文

我正在尝试在控制器中执行查询以获取具有类别 ID 的供应商列表。

我的模型是这样设置的。

  class Supplier < ActiveRecord::Base
    has_and_belongs_to_many :sub_categories
  end

  class Category < ActiveRecord::Base
    has_many :sub_categories
  end

  class SubCategory < ActiveRecord::Base
    belongs_to :category
    has_and_belongs_to_many :suppliers
  end

供应商可以在一个类别下拥有许多子类别。所以我可以通过这样做来获取供应商的类别。

  @supplier.sub_categories.first.category.name

这将返回供应商所属的类别，因为他们必须至少有 1 个子类别，然后将其链接到一个类别。

我想做的是通过传递一个category_id，我希望返回该类别下的所有供应商。我是这样写的，但似乎不起作用。

  @category = Category.find(params[:category_id])
  @suppliers = Supplier.where('sub_category.first.category.id = ?', @category.id)

我收到以下 sql 错误

  Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = 20)' at line 1: SELECT     `suppliers`.* FROM       `suppliers`  WHERE     (sub_category.first.category.id = 20)

原文

I am trying to do a QUERY in my controller to get a list of suppliers with a category ID.

I have my models set up like this.

  class Supplier < ActiveRecord::Base
    has_and_belongs_to_many :sub_categories
  end

  class Category < ActiveRecord::Base
    has_many :sub_categories
  end

  class SubCategory < ActiveRecord::Base
    belongs_to :category
    has_and_belongs_to_many :suppliers
  end

A supplier can have Many sub_categories that are under one single category. So i can grab the category of a supplier by doing this.

  @supplier.sub_categories.first.category.name

This returns the category that the supplier comes under because they have to have at least 1 sub category which is then linked to a category.

What i am trying to do is by passing a category_id i wish to return all suppliers that come under that category.
I had it written like this but it doesnt seem to be working.

  @category = Category.find(params[:category_id])
  @suppliers = Supplier.where('sub_category.first.category.id = ?', @category.id)

i get the following sql error

  Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = 20)' at line 1: SELECT     `suppliers`.* FROM       `suppliers`  WHERE     (sub_category.first.category.id = 20)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼角的笑意。 2024-11-18 06:59:51

嗯，这肯定是一个sql错误。 where() 调用中的内容直接转换为 SQL，而不是 sq;l。 :)

您需要将表连接在一起。我假设有一个 sub_category_suppliers 表完成 habtm 关联。（顺便说一句，我更喜欢专门使用 has_many :through ）

我认为它会是这样的：

Supplier.joins(:sub_category_suppliers => :sub_categories).
         where('sub_categories.category_id =?', @category.id).
         group('suppliers.id')

正如 Caley Woods 所建议的，这应该作为范围放置在供应商模型中：

scope :by_category, lambda { |category_id|
  joins(:sub_category_suppliers => :sub_categories).
         where('sub_categories.category_id =?', category_id).
         group('suppliers.id')
}

然后调用为Supplier.by_category(@category.id)

Well, that's certainly an sql error. The stuff inside the where() call gets translated directly to SQL, and that's not sq;l. :)

You need to join tables together. I'm assuming there's a sub_category_suppliers table that completes the habtm association. (BTW, I much prefer to use has_many :through exclusively)

I think it would be something like this:

Supplier.joins(:sub_category_suppliers => :sub_categories).
         where('sub_categories.category_id =?', @category.id).
         group('suppliers.id')

As Caley Woods suggested, this should be placed in the Supplier model as a scope:

scope :by_category, lambda { |category_id|
  joins(:sub_category_suppliers => :sub_categories).
         where('sub_categories.category_id =?', category_id).
         group('suppliers.id')
}

and then called as Supplier.by_category(@category.id)

回复收藏 0 原文

~没有更多了~