How to Add Conditional Where Clauses in Rails

How can you do a conditional where clause using AREL

It is possible to chain where statements

residentials = Residential.where(:is_active => true)
residentials = residentials.where(:other_thing => true) if param_was_passed

This should work.

Make sure this is not the last line in a function call; repeat the residentials variable as the last line in that case. (As per @digger69 comment)

How to add conditional where clauses in rails

You can apply multiple where calls to a query so you can build your base query:

query = User.joins(...)
            .group(...)
            .select(...)
            .where('users.id = :user_id', :user_id => self.id)

and then add another where call depending on your date interval:

if(begin_date && end_date)
  query = query.where(:created_at => begin_date .. end_date)
  # or where('created_at between :begin_date and :end_date', :begin_date => begin_date, :end_date => end_date)
elsif(begin_date)
  query = query.where('created_at >= :begin_date', :begin_date => begin_date)
elsif(end_date)
  query = query.where('created_at <= :end_date', :end_date => end_date)
end

Each where call adds another piece to your overall WHERE clause using AND so something like:

q = M.where(a).where(b).where(c)

is the same as saying WHERE a AND b AND c.

Conditionally chaining where clauses in Rails ActiveRecord queries

Queries aren't strings; they're query objects. So you want something like

query = YourModel.scoped # Rails 3; in Rails 4, use .all
if params.has_key?('size')
  query = query.where(size: params['size'])
end

etc.

Can you add clauses in a where block conditionally when using Squeel?

So, this isn't the prettiest thing ever, but it does what you're after.

def self.by_any(sin, name, dob)
  where do
    [
      sin.presence && clients.sin == "#{sin}",
      name.presence && clients.name =~ "%#{name}",
      dob.presence && clients.dob == "#{dob}"
    ].compact.reduce(:|)
    # compact to remove the nils, reduce to combine the cases with |
  end
end

Basically, [a, b, c].reduce(:f) returns (a.f(b)).f(c). In this case f, the method invoked, is the pipe, so we get (a.|(b)).|(c) which, in less confusing notation, is (a | b) | c.

It works because, in Squeel, the predicate operators (==, =~, and so on) return a Predicate node, so we can construct them independently before joining them with |.

In the case where all three are nil, it returns all records.

How to combine two conditions in a where clause?

solution 1: ( I prefer , more easy to understand)

Just write it like raw SQL:

Comment.where(
   "(user_id > ? AND user_id < ? ) OR (created_at > ? AND created_at < ?)",
   100, 200, "2022-06-01", "2022-06-05")

solution 2:

Comment.
  where(:created_at => time_range).
  where("user_id is not in (?)",[user_ids])

which will produce SQL like : select ... where ... AND ...

conditional active record query with rails

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.

---

Related Topics