CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-3associations
johnnycakes
johnnycakes

Reputation: 2450

Ruby/Rails: Complex conditions in a has_many association

I have a Transaction model. A transaction has a seller_id column and a buyer_id. Both are filled with a User ID.

so: 

class Transaction
  belongs_to :seller, :class_name => 'User'
  belongs_to :buyer, :class_name => 'User'
end

_

class User
  has_many :selling_transactions, :class_name => 'Transaction', :foreign_key => 'seller_id'
  has_many :buying_transactions, :class_name => 'Transaction', :foreign_key => 'buyer_id'
end

What I want to do is add a has_many to User to associate transactions that are incomplete, whether the User is the seller or the buyer.

class User
  has_many :incomplete_transactions, :class_name => 'Transaction', :conditions => ???
end

_

I wrote it out in pure SQL, and got the results I wanted. The join in my SQL is:

left outer join transactions t on ((t.seller_id = users.id and t.buyer_id is NULL) or (t.buyer_id = users.id and t.seller_id is NULL))

How do I translate that join to a has_many association?

EDIT:
I was hoping to keep incomplete_transactions as a ActiveRecord::Relation (instead of an Array), so I can do something like user.incomplete_transactions.limit(15)

Thanks.

Upvotes: 1

Views: 7775

Answers (4)

Tilo
Tilo

Reputation: 33732

Fixed it!

This is very similar to @DRobinson's solution, but using a proc and a has_many definition instead of defining a local method.

According to the 3.1 release notes you can now use a proc in a condition!

Inside the proc, self is the object which is the owner of the association, unless you are eager loading the association, in which case self is the class which the association is within.

class User < ActiveRecord::Base
  has_many :incomplete_transactions , :class_name => 'Transaction', 
    :conditions => proc { incomplete_sales + incomplete_purchases }

  has_many :incomplete_sales, :class_name => 'Transaction', :foreign_key => 'seller_id', :conditions => { :buyer_id => nil }
  has_many :incomplete_purchases, :class_name => 'Transaction', :foreign_key => 'buyer_id', :conditions => { :seller_id => nil }

  has_many :selling_transactions, :class_name => 'Transaction', :foreign_key => 'seller_id'
  has_many :buying_transactions, :class_name => 'Transaction', :foreign_key => 'buyer_id'
end

class Transaction < ActiveRecord::Base
  belongs_to :seller, :class_name => 'User'
  belongs_to :buyer, :class_name => 'User'

# scope :incomplete_sales , :conditions => {:buyer_id => nil}
# scope :incomplete_purchases , :conditions => {:seller_id => nil}
end

See:

Upvotes: 0

pdevisser
pdevisser

Reputation: 1

Assuming you are using rails 3.2, I'd recommend creating named scopes

class Transaction
  belongs_to :seller, :class_name => 'User'
  belongs_to :buyer, :class_name => 'User'

  scope :incomplete_sales, :conditions => { :buyer_id => nil }
  scope :incomplete_purchases, :conditions => { :seller_id => nil }
end

Then you can access the incomplete as such,

user.selling_transactions.incomplete_sales
user.buying_transactions.incomplete_purchases

EDIT - also corrected associations above

If you want to limit, you can always just do the following with an array

user.selling_transactions.incomplete_sales[0,15]

Upvotes: 0

Loken Makwana
Loken Makwana

Reputation: 3848

you can use option like below

:conditions = ["(t.seller_id = #{self.id} and t.buyer_id is NULL) or (t.buyer_id = #{self.id} and t.seller_id is NULL)"]

Upvotes: 0

DRobinson
DRobinson

Reputation: 4471

Similar answer to pdevisser's: You could use something similar to the answer given on this question: https://stackoverflow.com/a/307724/624590

Which would basically result in:

class User
  has_many :selling_transactions, :class_name => 'Transaction', :foreign_key => 'seller_id'
  has_many :buying_transactions, :class_name => 'Transaction', :foreign_key => 'buyer_id'

  has_many :incomplete_sales, :class_name => 'Transaction', :foreign_key => 'seller_id', :conditions => { :buyer_id => nil }
  has_many :incomplete_purchases, :class_name => 'Transaction', :foreign_key => 'buyer_id', :conditions => { :seller_id => nil }

  def incomplete_transactions
    incomplete_sales + incomplete_purchases
  end
end

EDIT: Alright, not totally sure how to set it up the way you wish (using has_many), but something along these lines might work for you:

class User
  has_many :selling_transactions, :class_name => 'Transaction', :foreign_key => 'seller_id'
  has_many :buying_transactions, :class_name => 'Transaction', :foreign_key => 'buyer_id'

  def incomplete_transactions
    Transaction.where("(buyer_id = ? and seller_id = NULL) or (seller_id = ? and buyer_id = NULL)", id, id)
  end
end

The where statement will return an ActiveRecord::Association, so you can follow it with limit (or other activerecord functions) when calling it.

Upvotes: 1

Related Questions