How to Build OR Queries With Active Record
Tags: rails, ruby, programming, webdev
Published: 2021-10-29 14:30:02

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.

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

This will generate SQL the looks something like this:

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

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

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

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

comment_ids = [] # append selected comment ids
Comment.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:

```ruby
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.

```ruby
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:

```sql
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:

```ruby
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:**

```ruby
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:**

```ruby
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.