如何在Rails中对4个表使用联接查询 [英] How to use join query for 4 tables in rails

问题描述

我有下表4种型号

class ItemCode < ActiveRecord::Base
  belongs_to :item_point
end

class ItemPoint < ActiveRecord::Base
  belongs_to :item
  has_many :item_codes
end

class Item < ActiveRecord::Base
  belongs_to :prodcut_category
  has_many :item_points
end

class ProductCategory < ActiveRecord::Base  
  has_many :items
end

现在，我必须使用 product_category 查找商品代码详细信息，为此我使用了内部联接.这是mysql查询

Now I have to find item_codes details using product_category for this I used inner join. Here is mysql query

SELECT *
FROM `item_codes` utc
INNER JOIN item_points rtp ON rtp.id = utc.item_point_id
INNER JOIN items ri ON ri.id = rtp.item_id
INNER JOIN product_catagories rpc ON rpc.id = ri.product_catagory_id
WHERE rpc.id =1
LIMIT 0 , 30

现在，我必须以Acitve记录格式编写相同的查询.

Now I have to write the same query in the Acitve record format.

ItemCode.joins(:item_point).joins(:item).joins(:product_catagory).where("product_catagories.id = 1")

出现以下错误

ActiveRecord::ConfigurationError: Association named 'item_points' was not found; perhaps you misspelled it?

那么如何以活动记录格式编写给定查询.

So how can write given query in the Active record format.

编辑

product_catagories

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| client_id            | int(11)      | YES  |     | NULL    |                |
| category_name        | varchar(255) | YES  |     | NULL    |                |
| category_description | varchar(255) | YES  |     | NULL    |                |
| created_at           | datetime     | NO   |     | NULL    |                |
| updated_at           | datetime     | NO   |     | NULL    |                |
| scheme_id            | int(11)      | YES  | MUL | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

item_points

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_id        | int(11)      | YES  | MUL | NULL    |                |
| created_at     | datetime     | NO   |     | NULL    |                |
| updated_at     | datetime     | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

项目

+ ---------------------------- + -------------- +- ---- + ----- + --------- + ------------

+----------------------------+--------------+------+-----+---------+------------

----+
| Field                      | Type         | Null | Key | Default | Extra          |
+----------------------------+--------------+------+-----+---------+----------------+
| id                         | int(11)      | NO   | PRI | NULL    | auto_increment |
| created_at                 | datetime     | NO   |     | NULL    |                |
| updated_at                 | datetime     | NO   |     | NULL    |                |
| product_catagory_id        | int(11)      | YES  | MUL | NULL    |                |
+----------------------------+--------------+------+-----+---------+----------------+

商品代码

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_point_id        | int(11)      | YES  | MUL | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

推荐答案

尝试一下:

ItemCode.joins(item_point: {item: :product_category}).where(product_category: {id: 1})

让我简化联接表:

Let me simplify the Joining Tables:

具有直接关系时:

# item_code belongs_to item_point
ItemCode.joins(:item_point) 

# ItemPoint has_many item_codes & belongs_to item
ItemPoint.joins(:item_codes, :item) 

具有间接/嵌套关系时:

# ItemCode belongs_to item_point, item_point belongs_to item
ItemCode.joins(item_point: :item) 

# ItemCode belongs_to item_point, item_point belongs_to item, item belongs_to product_category 
ItemCode.joins(item_point: {item: :product_category})

这篇关于如何在Rails中对4个表使用联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！