A tricky query for finding new forum posts

Question

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.

Does this work? ForumTopic.includes(:form_topic_reads).where('forum_topics.last_post_at < forum_topic_reads.updated_at') — MrYoshiji
this might get the topics, but it would not get me close to getting the forum ids, because forums are a tree and those topics can be several layers below the root. — Almaron
It would be also better to use .joins or .eager_load instead of .includes here. — Almaron

Antarr Byrd Antarr Byrd · Accepted Answer · 2015-08-19T20:14:45

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

A tricky query for finding new forum posts

1 Answers