Using Dataset#with (CTE) with deletion query

[v-kolesnikov]

Hi!

I'm working around of a custom soft-deletion mechanics in my Sequel-based application. Originally my solution was written as plain SQL and successfully tested - it works as expected. Then I tried to port it to Sequel DSL.

SQL solution: delete a record by ID and insert into another table (deleted_records) deleted ID, table name and row data encoded as json. Code:

WITH deleted AS (
    DELETE FROM table1
    WHERE id = 1
    RETURNING *
)
INSERT INTO deleted_records
		(original_table, original_id, data)
SELECT 'table1', id, to_jsonb(deleted.*)
FROM deleted
RETURNING *;

I could not get exactly the same but achieved 'almost' the same:

class Model < Sequel::Model
  def self.soft_delete(id)
    dataset.returning.where(id: id).delete do |deleted|
      db[:deleted_records].insert(
        [:original_table, :original_id, :data],
        [table_name.to_s, deleted[:id], Sequel.pg_json(deleted)]
      )
    end
  end
end

The difference here as I see is that Sequel reads deleted row (app -> db -> app roundtrip) instead of do all in a single database request.
Is there a way to reduce fetching the data of deleted record and perform an insertion during the deletion?

I've played with Dataset#with method but this wasn't successful.

Thanks!

[jeremyevans]

DB[:deleted_records].
  with(:deleted,
    DB[:table1].
    returning.
    where(:id=>1).
    with_sql(:delete_sql)
  ).
  returning.
  insert(
  [:original_table, :original_id, :data],
  DB[:deleted].
    select{['table1', :id, to_json(deleted.*)]}.
    returning()
)

WITH "deleted" AS (
  DELETE FROM "table1"
  WHERE ("id" = 1)
  RETURNING *
)
INSERT INTO "deleted_records"
            ("original_table", "original_id", "data")
SELECT 'table1', "id", to_json("deleted".*)
FROM "deleted"
RETURNING *

[v-kolesnikov]

Thanks!
I was so close in my trying with #with. The only thing I didn't know is the trick with .with_sql(:delete_sql), the rest was completely the same.

You are awesome!
Best!