2

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
  }
Improve this question
2
  • Those suggestions are good, but a really good optimization would be having correct indexes on your tables if you don't have them already. Commented Aug 18, 2011 at 6:21
  • I'll look into it thanks.. any pointers, most welcome. Commented Aug 18, 2011 at 11:20

3 Answers 3

Reset to default
2
+50

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Interesting.. unfortunately, this does not include the brands, shop, city data for each returned record.
Just add the includes after the where statement. From your example, it should be something like this: where(...).includes([{:shop => :prefecture},:brands]).joins(:shops => { :cities => :prefectures })
ActiveRecord::ConfigurationError: Association named 'shops' was not found; perhaps you misspelled it?
I edited the answer to show how to eager fetch the brands, shop, city and prefecture.
2

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')
Improve this answer

7 Comments

Seems close.. but it's choking on the join: Association named 'sales' was not found; perhaps you misspelled it?
oh my bad, the hash should start wish shops.
Hmmm.. This is working.. @sales = Sale.includes([{:shop => :prefecture},:brands]).order('finish DESC').where("finish > ?", Date.today).joins(:shop => { :city => :prefecture }).select('sales.*, DISTINCT prefecture.id') However, it's giving me every unfinished sale, not being distinct by prefecture.. I couldn't get it working using the pluralized versions in the hash..
call to_sql on the method and check what SQL is being generated.
It's pretty long so I made a gist.. thanks for keeping involved! gist.github.com/1146043
|
0

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')
Improve this answer

3 Comments

Error: NoMethodError: undefined method 'distinct' for #<Arel::Attributes::String:0x00000102f8d948> in regards to Prefecture.arel_table[:name].distinct
Sorry about that. I haven't used the distinct operator before and thought it would work like .eq and the other operators. I'll see if I can get this right after work, as I've just spent an hour or two trying to figure it out.
No problem. I've already accepted the other answer as it gave me what I wanted, but if there's another way it'd be great to have a record of it here!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.