kmurph73 / ctes_in_my_pg Goto Github PK

NoMethodError (undefined method `name' for ""cte"":Arel::Nodes::SqlLiteral):

Arel is no included as part of activerecord, rather than being a separate gem. So, in order for it to work with this project, we should remove the arel dependency here. I'll put together a PR.

It turns out that ActiveRecord::Relation#build_arel receives one parameter aliases, which receives no parameters before 5.2.0.

Would it be possible to publish this gem to rubygems.org? So that we can Gemfile it directly from there instead of GitHub.

It seems the release of Rails 6.1.4 broke the branch rails_6_1 of the gem.

One error is that build_arel changed its signature from

def build_arel(aliases)

to

def build_arel(aliases = nil)

I fixed this in my fork of the gem but I was still having issues in my test suite, it seems the tests were halting.
Hopefully I'll have time later this week to take a look at this, however I figured I would still open the issue to let you guys know.

Using ActiveRecord 5.2.3, the following code works (with the generated sql included below)

Widget.with(my_cte: "select id from widgets limit 1").joins('join my_cte using (id)').delete_all

DELETE FROM "widgets"
WHERE "widgets"."id" IN ( 
    WITH "my_cte" AS (
            SELECT id
            FROM widgets
            LIMIT 1
    )
    SELECT "widgets"."id"
    FROM "widgets"
    JOIN my_cte USING (id)
)

If I do the same thing without the join, however, it generates the error "relation "my_cte" does not exist" (with the generated sql included below)

Widget.with(my_cte: "select id from widgets limit 1").where("id in (select id from my_cte)").delete_all

DELETE FROM 
"widgets" WHERE 
(id in (select id from my_cte))

The issue seems to have something to do with the following lines in activerecord-5.2.3/lib/active_record/relation.rb#delete_all. It behaves differently if there are joins present or not:

stmt = Arel::DeleteManager.new
stmt.from(table)
if has_join_values? || has_limit_or_offset?
  @klass.connection.join_to_delete(stmt, arel, arel_attribute(primary_key))
else
  stmt.wheres = arel.constraints
end
affected = @klass.connection.delete(stmt, "#{@klass} Destroy")

Using ActiveRecord 6.0.0, the delete_all method has been completely refactored, and neither one of the approaches described above work. Both generate the error "relation "my_cte" does not exist".

Here is the relevant section of activerecord-6.0.0/lib/active_record/relation.rb#delete_all

stmt = Arel::DeleteManager.new
stmt.from(arel.join_sources.empty? ? table : arel.source)
stmt.key = arel_attribute(primary_key)
stmt.take(arel.limit)
stmt.offset(arel.offset)
stmt.order(*arel.orders)
stmt.wheres = arel.constraints

affected = @klass.connection.delete(stmt, "#{@klass} Destroy")

I'm not sure if it makes sense to think of this as a bug in this gem, or a bug in activerecord. I don't totally understand the issue, but it seems like this gem is using features that are supported in arel, but activerecord is doing things in such a way as to break those arel features. Is that right?