"I really don’t know enough about MySQL/PostgreSQL"

"SQL is complex"

ActiveRecord will take you great lengths, get your simple CRUD app out there, all by itself without writing a single bit of SQL. If you are a beginner, SQL may seem or sound scary and very low-level and unnecessary. But is it?

Grasping SQL will not only make you better understand how ActiveRecord works under the hood, but also allow you to nail some of the non-trivial requirements that you might have. In fact, AR provides some wrappers around more advanced SQL concepts.

There is a great, easy-to-grasp, interactive, step-by-step SQL tutorial called SQLBolt that I couldn’t recommend more.

It covers simple querying, which you are already familiar with, one way or another, and it also covers aggregates, joins, and schemas.

SQLBolt’s text-plus-interactive approach definitely makes for a seamless way to practice something you’ve just read about.

However, I personally would want something more than that to understand JOINs — something more visual. There are two really outstanding visualizations of these: Jeff Atwood’s article and Visual JOIN of Patrik Spathon.

Understanding SQL and database concepts, like joins, can actually make your life easier and your applications faster.

For one, if you wanted to list users with the maximum and minimum rating any of their posts has received, a very naive approach would be to iterate over all users and count their posts in Ruby.

with_counts = User.all.map { |user|
  [user, user.posts.max_by(&:rating), user.posts.min_by(&:rating)]
}

On large datasets, that is going to be really slow — because of loading all the user rows from the DB into Rails, and the looping over these, and getting min/max rating of each post... Ruby is not really well-suited for this.

Databases are more than a dumb datastore. They have enormous capabilities when it comes to working with data. With joins and aggregates, this calculation can be performed directly in the database.

We are going to join users with posts, then group resulting rows by user, and select the user details and the min/max post ratings for that user. The raw SQL query for that would be:

SELECT users.id, users.name, max(posts.rating) AS max_rating, min(posts.rating) AS min_rating
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id

When it comes to more complex queries, ActiveRecord provides a few helpful methods to customize queries that we can use in this case

• #joins, to join users with posts (note that we are going to use left join in order to include users with no posts in the result)
• #group, to GROUP BY
• #select, to select the maximum and minimum post ratings

Utilizing these, it gives:

Users
  .joins("LEFT JOIN posts ON posts.user_id = users.id")
  .group("users.id")
  .select("users.id, users.name, max(posts.rating) AS max_rating, min(posts.rating) AS min_rating")