CODEWITHSUNDEEP
ruby-on-railsdatabaseruby-on-rails-3postgresqlactiverecord
JellyFishBoy
JellyFishBoy

Reputation: 1798

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

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

Upvotes: 0

Views: 2861

Answers (2)

JellyFishBoy
JellyFishBoy

Reputation: 1798

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!

Upvotes: 4

xlembouras
xlembouras

Reputation: 8295

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 )

Upvotes: 0

Related Questions