find and fix corrupted children counts

[acolchagoff]

We have a rather large database, and one of our clients wrote in about a bug that we determined to be a result of children_count being wrong for many many users ( we aren't really sure how many) Is there something I can do to regenerate all children counts in our database?

[ptorrsmith]

Something like this ? We didn't implement it as not yet had the need, so can't guarantee it's solid. (inspired by Ancestry::ClassMethods.rebuild_depth_cache!)

    # Rebuild children cache if it got corrupted or if counter caching was just turned on
    def rebuild_children_count!
      raise Ancestry::AncestryException.new(I18n.t("ancestry.cannot_rebuild_children_count")) unless respond_to? :counter_cache_column
  
      self.ancestry_base_class.transaction do
        unscoped_where do |scope|
          scope.find_each do |node|
            node.update_attribute counter_cache_column, node.children.count
          end
        end
      end
    end

[kbrock]

The best way to do this is probably via a single sql query

This is probably not valid sql, but will hopefully get you close:

Direct descendents:

UPDATE #{table_name}
SET child_count = (
  select count(*)
  from table_name children
  where children.ancestry =
    case when #{table_name}.ancestry is null then #{table_name}.id::varchar
    else #{table_name}.ancestry || '/' || #{table_name}.id::varchar
    end
)

Probably wouldn't be that hard to get into a method.
String concatenation is mostly consistent across databases, mysql or sqlite may use + instead.

For all child decedents, we'd need to include both the ancestry || '/' || id and ancestry || '/' || id || '/%'

[kbrock]

@denodster I'm digging into what may be causing this.

So that I may mimic your environment as closely as possible:

• What database are you using?
• Have you overridden any callbacks like the destroy or update_descendants?
• are you using update_strategy = :sql (I'm worried this may be the culprit)
• are your trees very deep or relatively shallow?

Any anything else you can think of that may help reproduce would be great.

I'll also be adding:

• adding rebuild_counter_cache! and rebuild_counter_cache_sql!
• adding rebuild_depth_cache_sql!

@ptorrsmith If you could share similar information, that would be great, too. (so I can take that into account when looking into better support for dependant_count_cache.)

[kbrock]

@denodster would you possibly be running mysql?
I noticed locally that the touch tests seem to behaving differently on mysql than postgres. Wonder if the counters have a similar problem

[kbrock]

please check out rebuild_depth_cache! and rebuild_counter_cache! / rebuild_counter_cache_sql!

ref: #654 and #663