Avoiding the n+1 problem with complex queries

Sorry for the huge post, everyone. I'm working on a simple message board app as a starter project to learn Rails. Watching all those queries scroll by in the server logs has got me trying to refactor all my queries to be more efficient. I'm pretty new to Rails but not to web development or databases. The relevant models are like so:

class User < ApplicationRecord has_many :topics has_many :posts has_many :favorite_topics class Topic < ApplicationRecord belongs_to :user has_many :posts has_many :favorite_topics class Post < ApplicationRecord belongs_to :user belongs_to :topic class FavoriteTopic < ApplicationRecord belongs_to :user belongs_to :topic Users can mark topics as "favorite", which creates a new FavoriteTopic, with a foreign key to the user and a foreign key to the topic, easy enough. The problem is that on the front page, I want the following things: * Post count for each thread * Username of the last poster * Topics should sort by the time the last child post was created * There should be a flag if the current user has marked that topic favorite, ideally just a boolean attribute of the thread itself * Grab all of that information in with the same (low) number of queries no matter the size of the dataset * Query should return as an ActiveRelation with a view to eventually paginating with will_paginate. I wrote a monstrous SQL query that accomplished all of this except the last one because I just dumped it in to find_by_sql which returns an array, rather than an ActiveRelation. The best I've managed to do in ActiveRecord is the following: = Topic.includes(:user) .includes(:posts) .select("topics.*, COUNT(posts.id) AS post_count, MAX(posts.created_at) AS last_post_time) .joins("INNER JOIN ) .group("posts.topic_id") Which obviously doesn't even touch FavoriteTopics. The SQL query I wrote: SELECT topics.*, p.*, CASE WHEN f.user_id IS NULL THEN 0 ELSE 1 END AS favorite FROM topics INNER JOIN ( SELECT posts.topic_id, COUNT(posts.id) AS post_count, MAX(posts.created_at) AS last_post_time, users.username as last_poster FROM posts INNER JOIN users ON users.id = posts.user_id GROUP BY posts.topic_id ) AS p ON topics.id = p.topic_id LEFT OUTER JOIN ( SELECT * FROM favorite_topics WHERE favorite_topics.user_id = 1 ) as f ON f.topic_id = topics.id ORDER BY p.last_post_time I have no clue how to even go about implementing something like that with ActiveRecord. Joining to select statements? Joins within joins? Passing a where clause into a join to a select statement? I can't even figure out how to get the name of the last person to post in the topic. Is this possible at all? Do I need to roll my own pagination and include limits and offsets in the query itself? Is there a better set of models and relationships to deal with this? Everyone says to try to avoid find_by_sql if at all possible and every step of this seems like a really common situation so I feel like I'm just missing something really badly. Looking at that query though, maybe it just is this hard. Is it worth it to give up on efficiency so that I'm not tearing my hair out? Thanks in advance.

Leave a Comment