Refresh database after modifications via SQL


#1

hello

according to e.g. Best way to combine/merge categories when importing topics?
I have moved several thousand topics to other categories. Some of the left-over-categories were even empty then and could not be deleted.

Now I could delete them by manually setting categories.topic_count to 0, since discouse says that categories that are non-empty can not be deleted because it did not refresh that value when I emptied the category by moving all topics to another.

Using the method mentioned in the link above, the counts are not being updated nevertheless.
Somewhere I read to wait until some job does that. Is this true?
If so, how can one issue that job manually?

regards


#2

any ideas about this? the topics count on the main page appears broken when moving topics like given in the link, some show negative numbers on the homepage when threads were moved/deleted from within the forum.

This happenes when e.g. 10 topics are moved/deleted, then it counts 0 - 12 = 12, instead of (the real count 100) like 100 - 12 = 88 …because there was no update on categories.topic_count.

I could edit the count manually, with figuring out the topic count via SQL; I’ve done this so far:

SELECT c.name, COUNT(*), category_id FROM topics t, categories c
WHERE t.category_id = c.id
 GROUP BY category_id, c.name
 HAVING COUNT(*) > 1 ORDER BY COUNT(*);

which counts natively. So the question is would I break something with inserting these numbers into topic_count and is that topic_count given at some other table as well, where it also would need an update?

I guess there is no job that updates this in the background, since the numbers are broken for almost a day by now.


(Jay Pfaffman) #3

Caveat: I do not know, but since you’re comfortable fussing with the raw database, this code from discourse/scripts/import_scripts/base.rb might provide clues:

  def update_category_featured_topics
    puts "", "updating featured topics in categories"

    total_count = Category.count
    progress_count = 0

    Category.find_each do |category|
      CategoryFeaturedTopic.feature_topics_for(category)
      progress_count += 1
      print_status(progress_count, total_count)
    end
  end

  def update_topic_count_replies
    puts "", "updating user topic reply counts"

    total_count = User.real.count
    progress_count = 0

    User.real.find_each do |u|
      u.user_stat.update_topic_reply_count
      u.user_stat.save!
      progress_count += 1
      print_status(progress_count, total_count)
    end
  end


#4

Thank you for your hint, it encouraged me to egrep through the files, I found this in app/models/category.rb while searching for both SET and topic_count:

    def self.update_stats
      topics_with_post_count = Topic
                              .select("topics.category_id, COUNT(*) topic_count, SUM(topics.posts_count) post_count")
                              .where("topics.id NOT IN (select cc.topic_id from categories cc WHERE topic_id IS NOT NULL)")
                              .group("topics.category_id")
                              .visible.to_sql

    Category.exec_sql <<-SQL
    UPDATE categories c
       SET topic_count = x.topic_count,
           post_count = x.post_count
      FROM (#{topics_with_post_count}) x
     WHERE x.category_id = c.id
       AND (c.topic_count <> x.topic_count OR c.post_count <> x.post_count)
SQL

    # Yes, there are a lot of queries happening below.
    # Performing a lot of queries is actually faster than using one big update
    # statement with sub-selects on large databases with many categories,
    # topics, and posts.
    #
    # The old method with the one query is here:
    # https://github.com/discourse/discourse/blob/5f34a621b5416a53a2e79a145e927fca7d5471e8/app/models/category.rb
    #
    # If you refactor this, test performance on a large database.

    Category.all.each do |c|
      topics = c.topics.visible
      topics = topics.where(['topics.id <> ?', c.topic_id]) if c.topic_id
      c.topics_year  = topics.created_since(1.year.ago).count
      c.topics_month = topics.created_since(1.month.ago).count
      c.topics_week  = topics.created_since(1.week.ago).count
      c.topics_day   = topics.created_since(1.day.ago).count

      posts = c.visible_posts
      c.posts_year  = posts.created_since(1.year.ago).count
      c.posts_month = posts.created_since(1.month.ago).count
      c.posts_week  = posts.created_since(1.week.ago).count
      c.posts_day   = posts.created_since(1.day.ago).count

      c.save if c.changed?
    end

executing this in rails c did the job, the counts match right now - maybe a dev could sum this up to add it to the thread given in the first post. Thanks a lot! :slight_smile:


(Andrius) #5

how to run this in rails c ?

I am in console, pasted this code but it throws an error.

[7] pry(main)*   .select("topics.category_id, COUNT(*) topic_count, SUM(topics.posts_count) post_count")                              count")
sh: 1: Syntax error: word unexpected (expecting ")")

(Kane York) #6

You need to move it to all be on one line, because the interactive ruby doesn’t do lookahead for line-ends if it looks like the line is valid.


(Andrius) #7

can i somehow put this into the file and run with rails c?