I just struggled mightily to have the new Rails 3 query stuff create the SQL I wanted. I figured it out eventually. Here's the story.
I have the following models:I wanted to add a method to Person that would return all the add-ons to which a person subscribed through payment plans:So, I knew I would need this SQL query:I tried things like this, cursing the new query stuff the whole while:They all failed. Finally, I got what I wanted:It turned out to be nice and simple and I can do sweet chaining things like this:Now, I love the new Rails 3 query stuff.
Rails 3 is awesome. You might find `has_many :through` useful for doing this sort of thing.
ReplyDeletei didn't think :through would help with the double hop: Person -> PricingPlan -> Subscription -> AddOn. maybe it does?
ReplyDeletehas_many :through doesn't chain, so you definitely came up with the best solution here. Had to do a similar thing for a Rails 2 project, once.
ReplyDeleteI think you missed sting
ReplyDeletebelongs_to :add_on
in class PricingPlan.
You sir, just saved my bacon. Much appreciated! Here's a scope example of the above:
ReplyDeletescope :active, lambda { joins(:order_item => {:order => :payment}).where(:payments => {:status => ["Status1", "Status2"]}) }
Thank you... this was very helpful
ReplyDeleteThanks! Had the exact same problem. Funny, I also started with something like:
ReplyDeleteAddOn.joins(:pricing_plans).joins(:subscriptions)
Maybe that would be the intuitive way rails should accommodate?
hi brian,
ReplyDeleteyour post was very helpfu, thank you
learning rails but got stuck
dont know if i could ask this question here
if not i m sorry
models
bill
attr_accessible :billdt, :billno, bill_amt
has_many :parbills, :dependent => :destroy
has_many :particulars, :through => :parbills
particulars
attr_accessible :description, :name
has_many :parbills
has_many :bills, :through => :parbills
parbill
attr_accessible :bill_id, :paramt, :particular_id
belongs_to :bill
belongs_to :particular
client
attr_accessible :name, :ctype
has_one :bill, :dependent => :destroy
able to query particulars billed for a given date
@rpar = Parbill.where(:particular_id => params[:particular_id])
@rparbills = @rpar.joins(:bill).where('bills.billdt between ? and ?', @sdate, @edate)
but trying to query particulars billed for a given date on ctype(client type in client model)
please suggest