Friday, September 24, 2010

Rails 3 Complex Join Query

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.

8 comments:

  1. Rails 3 is awesome. You might find `has_many :through` useful for doing this sort of thing.

    ReplyDelete
  2. i didn't think :through would help with the double hop: Person -> PricingPlan -> Subscription -> AddOn. maybe it does?

    ReplyDelete
  3. has_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.

    ReplyDelete
  4. I think you missed sting
    belongs_to :add_on

    in class PricingPlan.

    ReplyDelete
  5. You sir, just saved my bacon. Much appreciated! Here's a scope example of the above:

    scope :active, lambda { joins(:order_item => {:order => :payment}).where(:payments => {:status => ["Status1", "Status2"]}) }

    ReplyDelete
  6. Thank you... this was very helpful

    ReplyDelete
  7. Thanks! Had the exact same problem. Funny, I also started with something like:

    AddOn.joins(:pricing_plans).joins(:subscriptions)

    Maybe that would be the intuitive way rails should accommodate?

    ReplyDelete
  8. hi brian,
    your 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

    ReplyDelete