Make the .delete method smarter by building a nested query in the background

[wout]

Right now, calling .delete on a query object only works if there is no order, limit and/or offset clause. If there is one of those, a runtime error similar to the following will be raised:

Error message syntax error at or near "ORDER". Query DELETE FROM table
WHERE table.column = $1 ORDER BY table.other_column DESC.

To be able to use those clauses, the query should be nested. In my particular situation, that's:

DELETE FROM page_versions
WHERE id IN
  (SELECT id
   FROM page_versions
   WHERE page_id = 6
   ORDER BY id DESC
   OFFSET 3)

The closest I got so far is:

ids = PageVersionQuery.new
  .page_id.eq(page_id.value.not_nil!)
  .number.desc_order
  .offset(3)
  .map(&.id)
PageVersionQuery.new
  .id.in(ids)
  .delete

But more intuitive (and more efficient) would be:

PageVersionQuery.new
  .page_id.eq(page_id.value.not_nil!)
  .number.desc_order
  .offset(3)
  .delete

It would be great to have the .delete method produce a nested query in the background if either one of .order, .offset or .limit are called as well.

[grepsedawk]

Wonder if we could just always delete in a subquery. That's a similar pattern to #select_count https://github.com/luckyframework/avram/blob/ac949e463af90fd516e845e75744117c72ad4435/src/avram/queryable.cr#L236

Automatically re-writing it to an WHERE EXISTS could be better, but potentially more work too

[wout]

Yeah, it would make more sense to always run it in a subquery.

Not sure what you mean with the WHERE EXISTS. Is that in case there is no ORDER BY, OFFSET or LIMIT clause present?

[grepsedawk]

Often in PG, using EXISTS can often be more performant in the way that it acts a little bit more like a join than a IN does

Not sure if this works verbatim, but it would look something like:

DELETE FROM 
  page_versions 
WHERE 
  EXISTS (
    SELECT 
      1 
    FROM 
      page_versions temp 
    WHERE 
      page_id = 6 
      AND page_versions.id = temp.id 
    ORDER BY 
      id DESC OFFSET 3
  )

[wout]

Ah, I didn't know that! Thanks for explaining.