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

JellyFishBoy · Accepted Answer · 2014-10-19 09:17:58Z

4

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!

answered Oct 19, 2014 at 9:17

JellyFishBoy

1,7981 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xlembouras · Accepted Answer · 2014-10-19 08:30:18Z

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, 2014 at 8:30

answered Oct 19, 2014 at 8:04

xlembouras

8,3254 gold badges35 silver badges44 bronze badges

3 Comments

JellyFishBoy Over a year ago

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 Over a year ago

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.

xlembouras Over a year ago

you want the query to return minimum prices for all delivery_services ? ( a minimum per _id ) ?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

UPDATE

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

UPDATE

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related