Ruby/Rails: Complex conditions in a has_many association

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 12:20

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.

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) — johnnycakes, Aug 08 '12 at 20:58
You're totally right. Not sure off hand how to do it with has_many, could use a definition with as above (in the edit). — DRobinson, Aug 08 '12 at 21:13
Great solution, +1! I added a solution which uses has_many but is otherwise very similar to your's. — Tilo, Aug 11 '12 at 19:51
Not the exact answer to my question, but the most practical, and accomplishes what I want. Thanks! — johnnycakes, Jan 16 '13 at 16:04

score 0 · Answer 2 · edited Aug 11 '12 at 19:39

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]

Tilo · Answer 3 · 2012-08-11T20:43:00.763

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:

http://guides.rubyonrails.org/3_1_release_notes.html

No go. The proc generates SQL that tries to access seller_id and buyer_id on the User model instead of the Transaction model. If I am more explicit about it (similar to Loken Makwana's answer) it still fails because it is trying to join to the `transactions` table on the user_id field, which doesn't exist. — johnnycakes, Aug 09 '12 at 19:50
yes, unfortunately it uses the User class, not the Transaction class as `self` in the proc. I fixed it. Very similar to DRobinson's solution, but using `has_many`.. — Tilo, Aug 11 '12 at 19:57
hmm.. I'm getting "Object doesn't support #inspect". The result shoud be an array of Transactions. Possible Rails bug? — Tilo, Aug 11 '12 at 20:47

score 0 · Answer 4 · answered Aug 09 '12 at 17:00

0

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

answered Aug 09 '12 at 17:00

Loken Makwana

3,788
2
21
14

1

This does not work in Rails 3.1 (you need "=>" instead of "=" and you have to use a `proc` to be able to access `self.id`. – johnnycakes Aug 09 '12 at 19:12
Correcting the syntax doesn't help. it still fails because it is trying to join to the `transactions` table on the `user_id` field, which doesn't exist. – johnnycakes Aug 09 '12 at 19:50

Ruby/Rails: Complex conditions in a has_many association

4 Answers4