ruby-on-rails - 定义关系 Ruby-on-Rails

Question

我有一个“位置”表和一个“角色”表，

位置表:

id|location_name|location_code|category|
1 |  location1  |   0001      | Urban  |
2 |  location2  |   0002      | Rural  |
3 |  location3  |   0003      |Suburban|
________________________________________

收入表:

id|role_code|rural|urban|suburban|
1 |   1001  |  5  |  10 |   15   |
2 |   1002  |  7  |  12 |   17   |
1 |   1003  |  9  |  14 |   19   |
__________________________________

位置表用于了解区域的类别(农村、城市或郊区)，角色表用于了解基于位置类别的收入价格。

我如何将这两个表连接在一起，以便无论何时用户输入位置和角色，他们都能看到价格？

Answer 1

当前的结构本质上不是很可扩展。我强烈建议您更改构建数据的方式。看看下面的例子。

# Instead of storing the location category in as text you can store references
#   of location_category in locations & revenues table
class LocationType # Location Category
end
# +----+-----------+
# | id | name      |
# +----+-----------+
# |  1 | Urban     |
# +----+-----------+
# |  2 | Rural     |
# +----+-----------+
# |  3 | Sub Urban |
# +----+-----------+


# Similarly instead of storing role_code, you can store references to roles 
#   in your revenue table
class Role
end
# +----+------+--------+
# | id | code | name   |
# +----+------+--------+
# |  1 | 1001 | Role 1 |
# +----+------+--------+
# |  2 | 1002 | Role 2 |
# +----+------+--------+
# |  3 | 1003 | Role 3 |
# +----+------+--------+

class Location
  belongs_to :location_type
end
# +----+-----------+---------+------------------+
# | id | name      | code    | location_type_id |
# +----+-----------+---------+------------------+
# |  1 | location1 | 001     |                1 |
# +----+-----------+---------+------------------+
# |  2 | location2 | 002     |                2 |
# +----+-----------+---------+------------------+
# |  3 | location3 | 003     |                3 |
# +----+-----------+---------+------------------+

class Revenue
  belongs_to :location_type
  belongs_to :role
end
# References for roles & location_type stored instead of actual values
# +----+-----------+---------+------------------+
# | id | role_id   | revenue | location_type_id |
# +----+-----------+---------+------------------+
# |  1 |         1 |       5 |                2 |
# +----+-----------+---------+------------------+
# |  2 |         2 |       7 |                2 |
# +----+-----------+---------+------------------+
# |  3 |         3 |       9 |                2 |
# +----+-----------+---------+------------------+
# |  4 |         1 |      10 |                1 |
# +----+-----------+---------+------------------+
# |  5 |         2 |      12 |                1 |
# +----+-----------+---------+------------------+
# |  6 |         3 |      14 |                1 |
# +----+-----------+---------+------------------+
# |  7 |         1 |      15 |                3 |
# +----+-----------+---------+------------------+
# |  8 |         2 |      17 |                3 |
# +----+-----------+---------+------------------+
# |  9 |         3 |      19 |                3 |
# +----+-----------+---------+------------------+

现在有了这个结构，您可以在数据库上运行优化查询来提取数据。

# Example
location_type_id = params[:location_type_id]
role_id = params[:role_id]

@revenue = Revenue.where({
  location_type_id: location_type_id,
  role_id: role_id
}).sum(:revenue)

如果您愿意，您还可以在 Revenue 表中包含 location_id，然后您可以找到特定位置的收入，如下所示:

@revenue = Revenue.where({
  role_id: role_id,
  location_id: location_id
}).sum(:revenue)