Recently, I was tasked with writing a few more advanced ActiveRecord queries for DailySmarty. Below are the requirements and the implementation. You can find the results on the User page.
Both queries are for tables that have a has_many/belongs_to relationship. To review, a DailySmarty User has many posts.
Example 1: Perform a query that sorts the list of users by which users have published the most posts
For this case I decided to implement a counter cache. A counter cache is a tool in Rails that provides a column that stores a count of how many records belong to a parent table. In this case it will be a column in the users table that keeps track of how many posts that a user has made.
In order to build this out, I start by generating a migration to add the new column to the users table:
And then update the migration file to ensure it has a default of 0, like this:
Then update the app/models/post.rb file to let the application know that we're going to be using a counter cache:
This will automatically update the new posts_count in the User model each time a user creates a post. If you're adding this type of counter cache to a legacy application that already has users with posts, you can run this script to update the cache by taking advantage of the reset_counters method. This script will iterate through all of the users and updating the counter cache to accurately count how many posts they have:
With that in place I was able to create a scope in the User model file to sort by users with the most posts:
Example 2: Perform a query that sorts the list of users by which users have published the most recently
This query was a bit tricker, but actually requires less code. It was trickier because ActiveRecord doesn't offer this type of behavior, so it required more pure SQL knowledge. You can have the users sorted by the users that published a post the most recently with the following query:
Summary
Hopefully this guide illustrates the fact that there are multiple ways to accomplish similar features in Rails development. From implementing a counter cache to writing pure SQL in order to sort by an associated table, Rails offers a wide range of options for building complex queries into your applications.
Additional resources are below:
