CODEWITHSUNDEEP
rubyruby-on-rails-3activerecord
minikomi
minikomi

Reputation: 8503

Optimizing nested activerecord query

Activerecord question.. How to optimize this query..

Prefectures (have many) Cities (have many) Shops (have many) Sales (have many) Brands

I'd like to get a list of one sale per prefecture, which is yet to end.. and then list the brands available at the sale.

The nesting is making this kind of tricky for me!

Here is what I came up with, though it's pretty ugly & I think it can be optimized at the query level rather than getting all unfinished sales..

#Get all sales which are yet to finish, ordered by finish date
upcoming_sales = Sale.includes([{:shop => {:city => :prefecture}}, :brands])
  .where("finish > ?", Date.today)
  .order('start ASC, finish ASC')
  .select(['shop.city.prefecture.name', 'brands.name'])

#filter down to a single sale per prefecture
 @sales = upcoming_sales.each_with_object({}){ |s,o|
    o[s.shop.city.prefecture.name] = o[s.shop.city.prefecture.name] ||= s
  }

Upvotes: 2

Views: 782

Answers (3)

alf
alf

Reputation: 18530

How about something like this?

class Sale < ActiveRecord::Base

    belongs_to :shop
    has_many :brands

    def self.first_sale_per_prefecture()
        first_sale_id_per_prefecture = %(
            select max(sales.id) 
            from sales
            inner join shops on shop_id = shops.id
            inner join cities on city_id = cities.id
            where finish > #{Date.today}
            group by prefecture_id
            order by finish desc)

        where("sales.id in (#{first_sale_id_per_prefecture})").includes(:brands, :shop => {:city => :prefecture})
    end
end

Upvotes: 2

Eric Hu
Eric Hu

Reputation: 18198

I'm going to try this using Arel

class Sale < ActiveRecord::Base

  belongs_to :shop


  class << self
    # Returns only Sale objects with obj.finish > today
    # add on other ActiveRecord queries:
    # Sale.unfinished.all
    # Sale.unfinished.all :limit => 10
    def unfinished
      where(Sale.arel_table[:finish].gt(Date.today))
    end

    # should select only one set of unfinished sales, 
    # and where the prefecture name is distinct
    def distinct_prefecture
      Sale.unfinished.joins({:shop => {:city => :prefecture}}).where(Prefecture.arel_table[:name].distinct)
    end
  end
end

Then, where you want it:

@sales = Sale.distinct_prefecture \
  .includes(:brands]) \     # should already include the other stuff with joins
  .order('start ASC, finish ASC')
@brand_list = @sales.collect{|s| s.brands}

If you want a limited result, this should be ok:

@sales = Sale.distinct_prefecture  \
  .limit(10)  \
  .includes(:brands]) \
  .order('start ASC, finish ASC')

Upvotes: 0

Mario Visic
Mario Visic

Reputation: 2663

You could get the upcoming sales and then join to shops => cities => prefectures and SELECT DISTINCT prefecture_id

this would ensure you only have one sale per prefecture. Something like this:

@sales = Sale.includes([{:shop => :prefecture},:brands])
  .order('finish DESC')
  .where("finish > ?", Date.today)
  .joins(:shops => { :cities => :prefectures })
  .select('sales.*, DISTINCT prefecture.id')

Upvotes: 2

Related Questions