Rails find :conditions

Question

I have a Reservation model that I'm searching for with three fields. The container_id must always be self.id but as confirmed and auto_confirmed only one needs to be true. I have the following but it doesn't perform what I need:

Reservation.find(:all, 
:conditions => ['container_id = ? AND confirmed = ? OR auto_confirm = ?', 
self.id, true, true,])

How should I change this?

Answer 1

I'm not sure I'm getting your problem, but from what I understand this would work:

Reservation.find(:all, 
:conditions => ['container_id = ? AND (confirmed = ? OR auto_confirm = ?)', 
self.id, true, true,])

Answer 2

As per your question confirmed and auto_confirmed only one needs to be true. So use following

Reservation.find(:all, 
                 :conditions => ['container_id = :container AND 
                  ( (confirmed = :flag and auto_confirm != :flag) ||
                    (confirmed != :flag and auto_confirm = :flag))', 
                    {:container=> self.id, :flag=>true}]
               )

Answer 3

I think something like this:

Reservation.find(:all, 
:conditions => ['container_id = ? AND ((confirmed != true AND auto_confirm = true) OR (confirmed = true AND auto_confirm != true))', 
self.id])

Answer 4

I'm not sure if this is database agnostic, but you could try

Reservation.find(:all, 
:conditions => ['container_id = ? AND confirmed = ? **XOR** auto_confirm = ?', 
self.id, true, true,])

Answer 5

What you are saying is not true - a query like

  SELECT * FROM foos WHERE content_id = 345 AND (confirm = 1 OR auto_confirm = 1)

will select the rows where both "confirm" columns are set to 1 (and ActiveRecord creates tinyint columns for booleans and checks against 1 and 0).

If you mean "find all rows matching on the content_id and having EITHER confirmed OR auto_confirmed true but NOT both" then you come down to a query like this

 SELECT * FROM foos WHERE content_id = 345 AND ((confirmed = 1 AND auto_confirm = 0) OR (confirmed = 0 AND auto_confirm = 1))

which you reword in AR terms like this

  Reservation.find(:all, 
    :conditions => [
      'container_id = ? AND ((confirmed = 1 AND auto_confirm != 1) OR (confirmed = 0 AND auto_confirm != 1))', 
      self]
  )

However, judging from the names your fields have you are actually implementing a state machine with separate columns which will bring you pain, so I'd investigate something that would give you progressions of states instead of checking for separate on and off bits.