CODEWITHSUNDEEP
ruby-on-railsactiverecord
sergserg
sergserg

Reputation: 22224

How to query active record for at least 1 foreign key record?

My use case is simple. Here are my models.

Auction
===============
id
name
expiration_date

AuctionImage
===============
id
auction_id
image_path
filesize

I'd like to get all Auctions that have at least 1 AuctionImage, and order them by expiration_date.

class Auction < ActiveRecord::Base
  has_many :auction_images
end

class AuctionImage < ActiveRecord::Base
  belongs_to :auction
end

Any suggestions?

Upvotes: 0

Views: 165

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

The fastest SQL method is likely to be an EXISTS correlated subquery, in which performance would be independent of the number of child records found.

To achieve this in Rails you'd need to do:

Auction.where("exists (select null from auction_images where aution_images.auction_id = auctions.id)").order("auctions.expiration_date desc")

If you were only expecting very few images per auction then an outer join based method would work, but it's possible that the need for a group by or distinct would cause a delay in return of the first rows from the query, and increase memory consumption on the database server.

As it is the expiration_date order would slow things anyway unless the optimiser found an index that was beneficial.

Upvotes: 0

BroiSatse
BroiSatse

Reputation: 44675

Auctions.joins(:auction_images).group(:id).order(:expiration_date)

Upvotes: 1

Related Questions