conditional active record query with rails

Question

By default :completed_at is nil. If I use the complete method then completed_at turns into Time.now. I checked it and it worked fine. In despite of this my query is not working in the case of the incoming/outgoing tasks. What is really weird that the query works perfectly with the completed tasks. So "where" query doesn't work, but "where.not" does. Can you help me out with this?

def incoming_tasks
  @user =current_user
  @executed_tasks = @user.executed_tasks.where("completed_at = ?", 'nil').order("created_at DESC")
end

def outgoing_tasks
  @user = current_user
  @assigned_tasks = @user.assigned_tasks.where("completed_at = ?", 'nil').order("created_at DESC")
end

def completed_tasks
  @user = current_user
  @assigned_tasks = @user.assigned_tasks.where.not("completed_at = ?", 'nil').order("completed_at DESC")
  @executed_tasks = @user.executed_tasks.where.not("completed_at = ?", 'nil').order("completed_at DESC")
end

Here is how I made the completed_at attribute having a date:

def complete
  @task = Task.find(params[:id])
  @task.update_attribute(:completed_at, Time.now)
  redirect_to completed_tasks_user_tasks_path(current_user)
end

Answer 1

As commented above, you need to generate SQL with the IS keyword to determine if a column is NULL. Rather than wrangle this yourself, you can pass a hash to where and let ActiveRecord handle the subtleties; i.e:

def incoming_tasks
  @user =current_user
  @executed_tasks = @user.executed_tasks.where(completed_at: nil).order("created_at DESC")
end

def outgoing_tasks
  @user = current_user
  @assigned_tasks = @user.assigned_tasks.where(completed_at: nil).order("created_at DESC")
end

def completed_tasks
  @user = current_user
  @assigned_tasks = @user.assigned_tasks.where.not(completed_at: nil).order("completed_at DESC")
  @executed_tasks = @user.executed_tasks.where.not(completed_at: nil).order("completed_at DESC")
end

As an aside, this would be a great place to use a scope.

Edit for detail:

(Caveat: SQL is a spec, not an implementation, and the SQL generated by rails depends on the database adapter. The following may vary depending on your choice of database.)

Ok, a few things:

1. You're using where("completed_at = ?", 'nil'), which is actually looking for records where completed_at is the string "nil". This is almost certainly not what you want, and has other unintended consequences.

2. SQL has a very particular way of handling NULL values when it comes to matching rows. When using the = operator in a WHERE clause, it will ignore rows with NULL values. You must use one of the IS NULL or IS NOT NULL operators to reason about NULL values.

Consider the table:

| id | completed_at               |

| 1  | 2015-07-11 23:23:19.259576 |
| 2  | NULL                       |

With the following queries:

Rails                                | SQL                                | Result

where("completed_at = ?", 'nil')     | WHERE (completed_at = 'nil')       | nothing, since completed_at isn't even a string
where.not("completed_at = ?", 'nil') | WHERE (NOT (completed_at = 'nil')) | only row 1, since = can't match a NULL value, even when negated
where("completed_at = ?", nil)       | WHERE (completed_at = NULL)        | nothing, you can't test for NULL with the = operator
where.not("completed_at = ?", nil)   | WHERE (NOT (completed_at = NULL))  | nothing, you can't test for not NULL with = either
where(completed_at: nil)             | WHERE completed_at IS NULL         | row 2 only
where.not(completed_at: nil)         | WHERE (completed_at IS NOT NULL)   | row 1 only

Of course, where(completed_at: nil) is just shorthand for where('completed_at IS NULL'), and the same for its converse, but the hash format is more idiomatic and independent of the database adapter.

The magic part of all this is that ActiveRecord is able to figure out if it should use = or IS NULL by looking at the hash you pass to where. If you pass a fragment, it has no idea, so it applies it blindly.