luckyframework / luckyframework/avram

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

Open
#831 4 comments 1 reaction 0 assignees View on GitHub
Dominant language
Crystal
Stars
183
Forks
67
PR merge metrics
No merged PRs in 30d

Description

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:

```sql
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:

```crystal
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:

```crystal
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.

Contributor guide

Open the contributing guide

Research direction

Start at the query object's `.delete` entry point and trace how `.order`, `.offset`, and `.limit` affect the generated DELETE SQL. Compare that flow with the nested SELECT example; done means those clauses produce a valid nested delete while direct deletes without them continue to work.

Written by the indexing model from the issue text.

Assessment

Tech stack
crystal, postgresql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.