如何选择基于Rails中的外键列独特的记录? [英] How to select unique records based on foreign key column in Rails?
问题描述
我有以下的模型结构,我的Rails 4.1的应用程序:
I have the following model structure in my Rails 4.1 application:
delivery_service.rb
delivery_service.rb
class DeliveryService < ActiveRecord::Base
attr_accessible :name, :description, :courier_name, :active, :country_ids
has_many :prices, class_name: 'DeliveryServicePrice', dependent: :delete_all
end
delivery_service_price.rb
delivery_service_price.rb
class DeliveryServicePrice < ActiveRecord::Base
attr_accessible :code, :price, :description, :min_weight, :max_weight, :min_length, :max_length,
:min_thickness, :max_thickness, :active, :delivery_service_id
belongs_to :delivery_service
end
正如你所看到的,快递服务有许多的送货服务价格。我尝试从交货服务价格表中的记录; 选择外键的独特的范围内,用最低的价格属性记录的,delivery_service_id(所以基本上是最廉价的递送服务每次递送服务价格)。
As you can see, a delivery service has many delivery service prices. I'm trying to retrieve records from the delivery service price table; selecting the record with the lowest price attribute within the unique scope of the foreign key, delivery_service_id (so essentially the cheapest delivery service price per delivery service).
如何从一个表中选择唯一的记录,与外键属性作为范围有多大?
我希望我已经说明了这就够了,让我知道,如果你需要的信息了。
I hope I've explained this enough, let me know if you need anymore information.
感谢
更新#1:
什么我想要实现示例:
delivery_service_prices表:
ID:1,价格:2.20,delivery_service_id:1
id: 1, price: 2.20, delivery_service_id: 1
ID:2,价格:10.58,delivery_service_id:1
id: 2, price: 10.58, delivery_service_id: 1
ID:3,价格:4.88,delivery_service_id:2
id: 3, price: 4.88, delivery_service_id: 2
ID:4,价格:1.20,delivery_service_id:2
id: 4, price: 1.20, delivery_service_id: 2
ID:5,价格:14.99,delivery_service_id:3
id: 5, price: 14.99, delivery_service_id: 3
预期的结果:
ID:1,价格:2.20,delivery_service_id:1
id: 1, price: 2.20, delivery_service_id: 1
ID:4,价格:1.20,delivery_service_id:2
id: 4, price: 1.20, delivery_service_id: 2
ID:5,价格:14.99,delivery_service_id:3
id: 5, price: 14.99, delivery_service_id: 3
推荐答案
由于PostgreSQL的更加严格遵守SQL标准(这是正确的),它需要一些调整,以获得正确的结果。
Due to PostgreSQL being more strict with abiding the SQL standard (rightly so), it requires a bit of tweaking to get the correct results.
下面的查询返回正确的结果为最低的送货服务价格,每送货服务:
The following query returns the correct results for the lowest delivery service price, per delivery service:
DeliveryServicePrice.select('DISTINCT ON (delivery_service_id) *').order('delivery_service_id, price ASC')
我需要添加的 delivery_service_id 的属性订单状态,或PostgreSQL引发以下列错误:
I need to add the delivery_service_id attribute to the order condition, or PostgreSQL throws the following column error:
PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
希望这可以帮助任何人在其上谁绊倒!
Hope this helps anyone who stumbles upon it!
这篇关于如何选择基于Rails中的外键列独特的记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!