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.

3

1 Answer

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

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.