A tricky query for finding new forum posts

So I have these models.

class Forum < ActiveRecord::Base has_ancestry has_many :forum_topics
end
class ForumTopic < ActiveRecord::Base belongs_to :forum has_many :forum_topic_reads # has a :last_post_at date column
end
class ForumTopicRead < ActiveRecord::Base belongs_to :forum_topic belongs_to :user # has a :updated_at date column
end

Very basic setup.

Now what I want to get is an arry of ids of forums that have unread posts sowhere in their subtree. The presence of new posts is decided by the comparescent of forum_topics.last_post_at with forum_topic_reads.updated_at where forum_topics.id = forum_topic_reads.forum_topic_id for a particular user_id or when a ForumTopicRead record is absent for that topic and user.

The problem is - the only way I managed to get it working is by manualy going through every forum and geting its subtree and then getting all the topics for the subtree etc. That results in a ton of similar queries to the database and thus a very slow process.

I believe there should be a way to make it go faster. I just need the ids of the forums that have at least 1 unread topic in their subtrees, don't need the count, don't need the topic ids themselves.

UPDATE

Got a hint from @MrYoshiji

This query:

ForumTopic.joins(:forum_topic_reads).where('forum_topics.last_post_at > forum_topic_reads.updated_at AND forum_topic_reads.user_id = ?', user.id).pluck(:forum_id).uniq

does not work quite well, 'cause it ignores the topics withought appropriate topic_reads (and creating a read for every topic for every user is a bit of an overhead)

UPDATE 2

So I finally came up with a promissing path. If I drop all the reads on a topic when a new post gets added to it (thus updating the :last_post_at field), I'll be able to collect the forum_ids with this query:

"SELECT distinct forum_id FROM `forum_topics` LEFT JOIN forum_topic_reads ON forum_topic_reads.forum_topic_id = forum_topics.id AND forum_topic_reads.user_id = #{user.id} GROUP BY forum_topics.id having count(forum_topic_reads.id) < 1"

Now the only big problem I have is translating this from SQL to ActiveRecord.

1 Answer

 ForumTopic.unscoped.joins(:forum_topic_reads).where('user_id = ?', user[:id]).group(:id).having('forum_topic_reads.count < 1').pluck(:forum_id)