How to Build OR Queries With Active Record

There are a couple of ways to recreate the SQL OR operator with Ruby on Rails and Active Record.

Here's how.

Checking a Single Column For Multiple Values

First, if you want to pull records where a certain column can be multiple values you won't need to use the OR SQL operator.

Instead, pass a list of the accepted values to Active Record and it will use the SQL IN operator.

Model.where(column: [1, 2, 3])

This will generate SQL the looks something like this:

SELECT models.* FROM models WHERE (models.column IN (1,2,3))

This pattern is extremely common and very practical. A couple of real examples:

User.where(role: [:admin, :mod])

BlogPost.where(tags: [ruby_tag, rails_tag])

comment_ids = [] # append selected comment ids
Comments.where(id: comment_ids)

Checking Multiple Columns For Multiple Values

If we need to check multiple columns, we can't get away with using the SQL IN operator anymore.

Instead, we need to leverage the full strength and flexibility of OR queries.

Using the OR Operator in Rails 5+

Rails 5 introduced the OR condition in Active Record.

Here's an example:

Post.where(category: "featured").or(Post.where(promoted: true))

Here's how to use it:

We break down our 2 acceptable conditions into individual queries. In this case, we want to fetch all the posts with a category set to "featured" and then also pull all the posts that have their promoted field set to true.

Post.where(category: "featured")
Post.where(promoted: true)

You then call Active Record's .or method on the first query, and pass it the second query as an argument.

Here's the SQL output you would get:

SELECT * FROM posts WHERE (category = 'featured') OR (promoted = true)

That's it! You'll get results the match either condition in your query.

Using the OR Operator in Rails 4 and Below

Earlier versions of Rails don't have support for OR queries in Active Record, but you can still pass raw SQL to your where conditions.

Here's how that would look:

Post.where("category = ? or promoted = ?", "featured", true)

If you're curious about the ? syntax above, this is a technique to protect your database against SQL injection attacks. More on that next.

Preventing SQL Injection Attacks

It would be irresponsible to not give a quick overview of how you might expose yourself to SQL injection attacks.

If you're not familiar, it's a vulnerability where user input is passed directly into your database queries. If you unintentionally allow this, the user can purposely input malicious SQL code into the input. And that SQL will be run on your database.

If we don't substitute variables in our query and instead pass them directly, we might expose our entire database to the user.

It works like this.

When you pass a variable to a where clause directly, it will pass the variable to the database as-is. If the user has malicious intent, they can pass unescaped strings directly to your database and wreak havoc.

Don't do this:

Post.where("category = #{params[:category]}")

You can't guarantee the input you get from params[:category] is safe for your database.

When you pass strings as the second argument in the where method, Active Record does the proper escaping needed to protect your database.

Better example:

Post.where("category = ?", params[:featured])

Final Words

Active Record is super powerful and gives you multiple ways to construct your OR query.

A quick note on the SQL outputs though - what you see here should be similar to what your Rails app generates, but it will depend on the database adapter your app is using. Different databases have subtle differences and unfortunately, they aren't all exactly the same. But this is why we have Active Record! It abstracts away that complexity and gives a simple way to interface with any database.

Originally published at https://testsuite.io