CODEWITHSUNDEEP
ruby-on-railsactiverecordassociations
Twiddr
Twiddr

Reputation: 297

Simplify Rails Query

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

I have 5 models:

They are associated with the following:

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!

Upvotes: 0

Views: 149

Answers (4)

Twiddr
Twiddr

Reputation: 297

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 :)

Upvotes: 0

Kelvin
Kelvin

Reputation: 20857

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:

  1. you always want to avoid the n+1 problem.
  2. 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.

Upvotes: 1

rewritten
rewritten

Reputation: 16435

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")

Upvotes: 1

weexpectedTHIS
weexpectedTHIS

Reputation: 3376

Well here's one pretty easy change:

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

Upvotes: 0

Related Questions