How to select unique records based on foreign key column in Rails?

Question

I have the following model structure in my Rails 4.1 application:

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

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

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).

How can I select unique records from a table, with the foreign key attribute as the scope?

I hope I've explained this enough, let me know if you need anymore information.

Thanks

UPDATE #1:

Example of what I'm trying to achieve:

delivery_service_prices table:

id: 1, price: 2.20, delivery_service_id: 1

id: 2, price: 10.58, delivery_service_id: 1

id: 3, price: 4.88, delivery_service_id: 2

id: 4, price: 1.20, delivery_service_id: 2

id: 5, price: 14.99, delivery_service_id: 3

expected results:

id: 1, price: 2.20, delivery_service_id: 1

id: 4, price: 1.20, delivery_service_id: 2

id: 5, price: 14.99, delivery_service_id: 3

score 4 · Answer 1 · answered Oct 19 '14 at 09:17

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')

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!

xlembouras · Answer 2 · 2014-10-19T08:30:18.060

0

To get the minimum for a single record you can use

DeliveryServicePrice.where(delivery_service_id: x).order(:price).limit(1).first

or if you have a delivery_service object available

delivery_service.prices.order(:price).limit(1).first

UPDATE

If you want all minimums for all service_delivery_ids you can use a group query

DeliveryServicePrice.group(:delivery_service_id).minimum(:price)

which will get you almost where you want to go

{
  1: 2.20,
  2: 1.20,
  3: 14.99
}

with a hash containing the delivery_service_id and the price. (you can't see the price_id )

edited Oct 19 '14 at 08:30

answered Oct 19 '14 at 08:04

xlembouras

8,215
4
33
42

Thanks for the reply. I'm not trying to retrieve the delivery service prices from one delivery service, I need to select the minimum priced delivery service price from all delivery services. – JellyFishBoy Oct 19 '14 at 08:06
The above query is still requiring a delivery service id parameter it be passed into it `where(delivery_service_id: x)`. I want to select the minimum price delivery service price, which has unique scope of the foreign key attribute, delivery_service_id. – JellyFishBoy Oct 19 '14 at 08:13
you want the query to return minimum prices for all delivery_services ? ( a minimum per _id ) ? – xlembouras Oct 19 '14 at 08:18

How to select unique records based on foreign key column in Rails?

2 Answers2

UPDATE

Linked