CODEWITHSUNDEEP
ruby-on-rails
calyxofheld
calyxofheld

Reputation: 2198

rails find_by returns incorrect record?

I have an app where a User creates a Transaction to purchase an Item from a different User. I am suddenly having difficulty with the find_by in one method on Item. I want to find the very first Transaction involving the Item on which it is called, and I want to further limit that result by searching against a number of invalid states.

class Item < ApplicationRecord
 def first_find
  Transaction.find_by("item_id = ? AND recipient_id = ? AND state != ? OR state != ? OR state != ?", self.id, author.id, :ignored, :declined, :unapproved)
 end
end

What this does, no matter what, is return the very first Transaction in my db. This is not expected behavior. So in console if I go like t = Transaction.last to cache Transaction id #5 (which has an item_id of 7 and a recipient_id of 4), and then call t.item.first_find, I would presumably get Transaction #5. The SQL output for this is query is Transaction Load (1.1ms) SELECT "transactions".* FROM "transactions" WHERE (item_id = 7 AND recipient_id = 4 AND state != 'ignored' OR state != 'declined' OR state != 'unapproved') LIMIT $1 [["LIMIT", 1]].

Which is great! That's what I want from the output. But to my confusion, it returns this:

#<Transaction id: 2, sender_id: 1, recipient_id: 2, item_id: 9 ..... >

Does anyone have any idea why? Thanks!

Edit 1

So I think I've solved it? I've had this problem before where putting too many search params into the where clause messes it up for some reason.

So while this does not work

Transaction.find_by("item_id = ? AND recipient_id = ? AND state != ? OR state != ? OR state != ?", self.id, author.id, :ignored, :declined, :unapproved)

This does

Transaction.where("item_id = ? AND recipient_id = ?", self.id, author.id).where("state != ? OR state != ? OR state != ?", :ignored, :declined, :unapproved).first

I'm not entirely sure why, though. Does anyone know?

Edit 2

The AND operators should be separate from the OR operators.

Upvotes: 2

Views: 391

Answers (2)

Oshan Wisumperuma
Oshan Wisumperuma

Reputation: 1958

answering why. that's how SQL operator precedence works. more explanation is here. so when you break it to another "where" clause that builds a new relation, which is the result of filtering the current relation according to the conditions in the arguments. the source code is here.

let me show other solutions.

1.

    Transaction.where(item_id: self.id, recipient_id: author.id).where.not(state: [:ignored, :declined, :unapproved]).first

2.

recipient_transactions = Transaction.where(item_id: self.id, recipient_id: author.id)
active_transactions = Transaction.where.not(state: [:ignored, :declined, :unapproved])
result = recipient_transactions.merge(active_transactions).first # this buils a single query

Upvotes: 2

widjajayd
widjajayd

Reputation: 6263

I think you should use where clause instead of using find_by,

class Item < ApplicationRecord
  def first_find
    Transaction.where("item_id = ? AND recipient_id = ? AND state != ? OR state != ? OR state != ?", self.id, author.id, :ignored, :declined, :unapproved)
  end
end

this will return ActiveRecord::Relation(record collections) instead of just one record if you using find statement

Upvotes: 1

Related Questions