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) https://github.com/rails/rails/pull/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. https://github.com/stefankroes/ancestry/blob/8f06902839579917995d8c2418dd558c1c1f17af/lib/ancestry/has_ancestry.rb#L98-L102

[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! https://github.com/stefankroes/ancestry/pull/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.