Simplify Rails Query

Question

I'm trying to reduce the number of queries in my application and need some help with the following setup:

I have 5 models:

Bet
Choice
Spotprice
Spotarea
Product

They are associated with the following:

Bet belongs_to Choice
Choice belongs_to Spotarea
Choice belongs_to Product
Choice has_many Bets
Spotprice belongs_to Spotarea
Spotprice belongs_to Product
Spotarea has_many Spotprices
Spotarea has_many Choices
Product has_many Sprotprices
Product has_many Choices

My goal is to find the Spotprices that matches a Specific Bet. To do that I uses the following queries, but I'm sure it can be done in a better way, so when I run through 100 bets and want to see if they are above or below the corrosponding Spotprice I don't overload the DB with queries.

a = Bet.find(5)

b = Choice.find(a.choice_id)

c = Spotprice.where(:spotarea_id => b.spotarea_id, :product_id => b.product_id, 
    :deliverydate => b.deliverydate).first

Thanks!

score 1 · Answer 1 · answered Sep 28 '12 at 18:59

1

first of all, set up join bridges:

class Choice
  has_many :spotprices, :through => :spotarea
end

class Bet
  has_many :spotprices, :through => :choice
end

then you can query things like

Bet.joins(:spotprices).where("spotprices.price > bets.value")

answered Sep 28 '12 at 18:59

rewritten

16,280
2
47
50

Thanks.. The join bridges sure helped, but still didn't fix the problem entirely.. I've posted an answer with the solution.. Thanks for the help to guide me in the right direction.. – Twiddr Sep 29 '12 at 09:51

score 1 · Answer 2 · answered Sep 28 '12 at 19:51

Before trying to decrease the number of queries, you should run a performance test on your app, and monitor the database load. Sometimes it's better to run a few small queries rather than one huge query with a few joins. Certain versions of Oracle seem especially bad at joins.

An alternative to joins, if you're trying to avoid the n+1 query problem, is to use preload and pass the association (preload takes the same arguments as includes). This makes ActiveRecord run one query per table.

Basically:

you always want to avoid the n+1 problem.
trying to combine multiple queries into a join could in the best case be a premature optimization, and in the worst case actually make performance worse.

Thanks for a great answer.. I'm surely going to look into the `preload` thing and try to avoid the n+1 problems.. which I had to look up cause I didn't know what you ment :) — Twiddr, Sep 29 '12 at 09:47

score 0 · Answer 3 · answered Sep 28 '12 at 18:47

0

Well here's one pretty easy change:

b = Bet.includes(:choice).find(5).choice

answered Sep 28 '12 at 18:47

weexpectedTHIS

3,358
1
25
30

:includes does a separate query and joins in ruby. Use :joins to make a real join – rewritten Sep 28 '12 at 18:55
That's not entirely true. It sometimes does an extra query and sometimes doesn't. – weexpectedTHIS Sep 28 '12 at 19:51

score 0 · Accepted Answer · edited May 23 '17 at 11:56

After a few hours and a lot of Google search I found a solution that works.. After adding the join bridges I wanted to do:

Bet.find(5).spotprice

But that didn't work because to do that I needed something like this in my Choice model:

has_one :spotprice, :through => [:spotarea, :product] :source => :spotprices

I that is not possible.. apperently..

So I found this link has_one :through => multiple and I could use that answer in my situation.

class Choice < ActiveRecord::Base
  belongs_to :user
  belongs_to :spotarea
  belongs_to :product
  has_many   :bets

  def spotprice
    Spotprice.where(:product_id => self.product_id, :spotarea_id => self.spotarea_id, :deliverydate => self.deliverydate).first
  end

class Bet < ActiveRecord::Base
  belongs_to :user
  belongs_to :choice
  has_one    :spotprice, :through => :choice

With the above I can now do:

Bet.find(5).choice.spotprice

If anybody got a better solution please let me know :)

Simplify Rails Query

4 Answers4