luckyframework / luckyframework/avram
Make the .delete method smarter by building a nested query in the background
- 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
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