Rails & Postgresql subqueries

I think my weakness is SQL. Seriously. ORM have made me a SQL ignorant. I think the reason this happened is because of all the SQL injection that existed in the early 2000. Crafting SQL queries was feeling dangerous to me so I always tried to do stuff through ActiveRecord, Doctrine, or any other ORM I would have to work with. If something I needed was too complex, well I would either try to find a way through the ORM or use multiple queries and filter stuff in memory.

And this was taxing the database and server. All of this because of FUD.

Today, I want to talk about SQL subqueries and how you can use them when querying stuff from your database.

Subqueries in where()

Let me show you a small example of what I mean

Retrieve every active users that has not posted yet or that their last post is 6 months old.

Basically, there's 3 conditions to be met here.

  1. User is active;
  2. User has not post, or;
  3. User hasn't posted in the last 6 months.

Subqueries are excellent for this kind of problem. You want to test a query for every user that are active. Here's how I do it.

@users = User.where("not exists(select id from posts where posts.author_id = users.id and posts.created_at < ?)", 6.months.ago)

The subquery returns a list of ids. From there, the function not exists will return a boolean. If it returns false, the user won't be fetched. Another thing you might notice is how I use posts.author_id = users.id. This is one of the reason why I love doing this with SQL rather than filtering a set in ruby. Because (not) exists requires a boolean, you may only select an indexed key. This will make your query an order of magnitude faster than selecting the whole row.

If this query is something your use in more than one place, you can refactor your code and use a scope like you would normally do.

class User < ActiveRecord::Base
  scope :posted_since, lambda { |date| where("not exists(select id from posts where posts.author_id = users.id and posts.created_at < ?)", date) }

# In controller
@ users = User.posted_since(6.months.ago)

Have a good week!

You can also discuss this on HN here.

Get more ideas like this to your inbox

You will never receive spam, ever.