Recursive + 7.1

[nhorton]

Hi,
First off - thanks a ton for writing this and fighting to get most of it into ActiveRecord!

The "issue" in this report is that there is not a roadmap-y mention in the README on how this is going to play with 7.1. When 7.1. releases, is it expected that this gem will work, or do we need to pull the gem out when it releases?

I am particularly interested as we need recursive CTEs, and I was hoping we could use this gem and that post-7.1, it would remain with just the recursive stuff added to base activerecord.

Thanks again for the great work here!

[vlado]

Very good question @nhorton. I didn't try it with edge rails but I suppose it would override original method and everything would work. Will need to test it and add note to Readme when I catch some time. Feel free to do it and open PR if you gonna have time before me :)

Also, recursive support was dropped from rails/rails#37944 case we couldn't agree on the right API for it. I never needed / used recursive CTEs in "real life" so I would be interested if you can provide some examples.

[lorint]

I use recursion in The Brick to rapidly calculate inherited permissions. (This part of the gem hasn't been publicly released yet -- it's currently being tested by a limited number of users.) Here's the monkey patch I currently use on Rails 7.1 in order to add this necessary recursive support:

module ActiveRecord
  module QueryMethods
    def with_recursive(as, anchor, recursive)
      @is_recursive = true
      common_table = Arel.sql(anchor.arel.union(:all, recursive.arel).to_sql)
      from(as).with(as => common_table)
    end

    private
      def build_with(arel)
        return if with_values.empty?

        with_statements = with_values.map do |with_value|
          raise ArgumentError, "Unsupported argument type: #{with_value} #{with_value.class}" unless with_value.is_a?(Hash)

          build_with_value_from_hash(with_value)
        end

        # Was:  arel.with(with_statements)
        @is_recursive ? arel.with(:recursive, with_statements) : arel.with(with_statements)
      end
  end
end

With that in place, you can run a recursive CTE by providing a name for the common table and then two additional parts -- an anchor query and a recursive query, like this:

employees = Employee.all.with_recursive(
  'h', # Alias for common table
  # Anchor query
  Employee.where(reports_to_id: nil).select(:id, :reports_to_id, '1 AS level'),
  # Recursive query
  Employee.joins('INNER JOIN h ON employees.reports_to_id = h.id').select(:id, :reports_to_id, 'level + 1')
)

[nhorton]

Thanks to both of you. I will open a README PR if I get a chance to run against edge.

@vlado - my recursive use case is that we have a directed acyclic graph that we create in our database and need to send it to serialize it with some requests, so populating the graph in one query is really valuable.

@lorint - I have looked at The Brick a couple times and love how you think. We should chat sometime - much of what my company does (unsupervised.com) is AI data analytics and we do some interesting stuff with exploring schemas too.

The Brick is cool work.

[lorint]

We should chat sometime - much of what my company does is AI data analytics ...

Sounds cool! Have filled out the "let's nerd out together" contact form on your site -- I'm originally from the US but now living in UK. Feel free to email or call at your convenience -- I'm usually up until around 4pm Pacific time.

[nhorton]

Sweet! I am on vacation this week but will ping when I am back!

[ClearlyClaire]

We would also significantly benefit from being able to write recursive CTEs in ActiveRecord, so we are interested in having this feature supported in a maintained gem, or even better, in ActiveRecord itself.

@vlado @lorint is either of you interested in working to get this in ActiveRecord?

@lorint your monkey-patch has been a great starting point for us! I just wanted to check whether you're fine with us using it under MIT or AGPLv3 license? Also, I did run into an issue with it losing parameter binds information, which I solved by not using to_sql:

module ActiveRecord
  module QueryMethods
    def with_recursive(as, anchor, recursive)
      @is_recursive = true
      common_table = anchor.arel.union(:all, recursive.arel)
      from(as).with(as => common_table)
    end

    private

    def build_with(arel)
      return if with_values.empty?

      with_statements = with_values.map do |with_value|
        raise ArgumentError, "Unsupported argument type: #{with_value} #{with_value.class}" unless with_value.is_a?(Hash)

        build_with_value_from_hash(with_value)
      end

      # Was:  arel.with(with_statements)
      @is_recursive ? arel.with(:recursive, with_statements) : arel.with(with_statements)
    end

    def build_with_value_from_hash(hash)
      hash.map do |name, value|
        expression =
          case value
          when Arel::Nodes::SqlLiteral then Arel::Nodes::Grouping.new(value)
          when ActiveRecord::Relation then value.arel
          # Was:  when Arel::SelectManager then value
          when Arel::SelectManager, Arel::Nodes::UnionAll then value
          else
            raise ArgumentError, "Unsupported argument type: `#{value}` #{value.class}"
          end
        Arel::Nodes::TableAlias.new(expression, name)
      end
    end
  end
end

[vlado]

@ClearlyClaire I'm interested in adding this to ActiveRecord, I have it on my radar but it is hard to find time :)

It will be easiest I think if added to the gem first so I will prioritise that.

[ClearlyClaire]

I ended up giving a try to having the feature in ActiveRecord itself: rails/rails#51601

[lorint]

your monkey-patch has been a great starting point for us! I just wanted to check whether you're fine with us using it under MIT or AGPLv3 license?

You bet -- and I see that this has progressed quite a bit from a week ago!

Grateful to see the PR for recursive CTE stuff to make it into Rails.