virtual depth_cache

[mitsuru]

I noticed a problem updating the depth_cache in the big tree.
Since it is a sequential update, the updates are issued in a large number of UPDATE statements.
I have solved this problem with generated columns. (It depends on MySQL, but Postgres may be able to handle it as well as Rails7)
rails/rails#41856

    change_table :table do |t|
      t.virtual :ancestry_depth, type: :integer, as: "LENGTH(ancestry) - LENGTH(REPLACE(ancestry, '/', '')) - 1", stored: true
      t.index :ancestry_depth
    end

This example depends on ancestry_format: :materialized_path2.

This solution has the problem that existing scopes cannot be used.

ancestry/lib/ancestry/has_ancestry.rb

Lines 98 to 102 in 8f06902

	scope :before_depth, lambda { |depth| where("#{depth_cache_column} < ?", depth) }
	scope :to_depth, lambda { |depth| where("#{depth_cache_column} <= ?", depth) }
	scope :at_depth, lambda { |depth| where("#{depth_cache_column} = ?", depth) }
	scope :from_depth, lambda { |depth| where("#{depth_cache_column} >= ?", depth) }
	scope :after_depth, lambda { |depth| where("#{depth_cache_column} > ?", depth) }

[kbrock]

Looks like rails 7 supports generated columns for both mysql and postgres.
This looks great. I did not know rails supported this feature.

In the current master (will be the 5.0 branch), I have implemented ancestry_depth_sql. (the sql should look familiar)

Currently it supports updating all depth_cache values in a single query. build_depth_cache_sql! #654 - I probably should have just updated build_depth_cache to use the sql version but I took it slowly.

Let me know if this helps with your problem or if you have suggestions to make it better

[kbrock]

Had a few ideas around this and put together a PR to share.
Let me know if you still use virtual columns?

[kbrock]

@mitsuru Did you have a chance to look at #670 ?

[kbrock]

Also, there is the ability to update all depth values using a single query. Not sure if this would work for you.