SQL WHERE column = everything (with Active Record)

Question

In SQL you can create a dynamic SQL statement when the list of conditions (WHERE) is not known in advance, but is built at code execution. So sometimes I would like a subset of records (like ids matching an array), but in other cases I don't want the condition to be applied (hence return the whole list).

In pure SQL there is this solution:Stackoverflow: SQL WHERE column = everything

SELECT * FROM table WHERE id = id

More here: Stackoverflow: Why would someone use WHERE 1=1 AND in a SQL clause?

How would I do this using the DSL from Active Record?

Think you might have hit "Ask Question" instead of "Post Answer" — Caius Jard, Jun 22 '21 at 13:27

score 2 · Answer 1 · answered Jun 22 '21 at 14:04

2

Chaining where clauses in Rails is quite easy

dataset = User.all
dataset = dataset.where(name: "John") if filter_name?
dataset = dataset.where("age > ?", 20) if filter_age?
dataset

answered Jun 22 '21 at 14:04

Eyeslandic

14,553
13
41
54

engineersmnky · Accepted Answer · 2021-06-22T16:03:22.940

Both of these will work: (as will many more)

.where(MyModel.arel_attribut(:id).eq(MyModel.arel_attribute(:id)))
# OR 
.where(Arel.sql("1=1"))

This "always true" type conditioning was generally used for query assembly in Stored Procedures or in String concatenation where you would always have a WHERE clause but then may not have any conditions e.g.

SET @sqlString = 'SELECT * FROM table WHERE 1=1'
IF (@var IS NOT NULL) 
  SET @sqlString = @sqlString + ' AND "column" LIKE ''%' + @var + '%'' '

EXEC (@sqlString) 

# OR

sqlString = "SELECT * FROM table WHERE 1=1" 
if someCondition 
  sqlString = sqlString + " AND column = " + value 
end if

but all of this seems antiquated (especially in regards to Rails).

ActiveRecord avoids all of this by using a library called Arel which assembles the SQL just prior to execution.

ActiveRecord provides a convenience method (#all) to generate a similar concept of select everything. You can just call MyModel.all which will return a chainable ActiveRecord::Relation you can then apply conditions, or not, you can add ORDER BY, GROUP BY, LIMIT, HAVING, etc. statements, it is up to you in code and at runtime.

You can utilize this chainability to "assemble" these conditions as you see fit e.g.

q = MyModel.all 
q.where!(name: "engineersmnky") if find_my_name?

These calls do not even have to make "logical" sense in SQL statement order because Arel knows the order in which to assemble these statements in order to form a coherent query. e.g.

MyModel.having("COUNT(column) > 1")
  .order(:name)
  .group(:name)
  .select("name, COUNT(column) AS counter")
  .where(x: 12)
  .limit(2)

This is completely jumbled from a SQL standpoint but when the actual query is assembled it will be ordered correctly.

SQL WHERE column = everything (with Active Record)

2 Answers2