When performing a delete query on a table using a join to filter based on other tables' values, the resulting query doesn't include the join operation.
The query fails as the db can't find the joined tables' columns.
Doing the same query with a SELECT (all()) instead of DELETE (delete()) works as expected.
Reproduced on PostgreSQL and SQLite, but other drivers could be affected too.
[ codes.vapor.application ] [ DEBUG ] query delete user_task filters=[task[project_id] = C01D220F-42FD-437A-8C31-4D7EE47B781E] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (FluentKit/QueryBuilder.swift:293)
[ codes.vapor.application ] [ DEBUG ] DELETE FROM "user_task" WHERE "task"."project_id" = ? ["C01D220F-42FD-437A-8C31-4D7EE47B781E"] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (SQLiteNIO/SQLiteConnection.swift:161)
2022-09-22 17:13:35.708834+0200 Run[73062:11117473] [logging] no such column: task.project_id in "DELETE FROM "user_task" WHERE "task"."project_id" = ?"
[ codes.vapor.application ] [ WARNING ] error: no such column: task.project_id [request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (Vapor/Middleware/ErrorMiddleware.swift:42)
### Expected behavior
The join operation should be included in the final query like it is done in a normal SELECT query.
Here's a SELECT query and resulting logs using the same filters on SQLite:
```swift
try await UserTaskPivot.query(on: req.db)
.join(parent: \.$task)
.filter(Task.self, \Task.$project.$id == project.requireID())
.all()
Logs:
[ codes.vapor.application ] [ DEBUG ] query read user_task filters=[task[project_id] = C01D220F-42FD-437A-8C31-4D7EE47B781E] [database-id: raw, request-id: 55BE2766-3393-4FE5-855B-BC87F0FF30FD] (FluentKit/QueryBuilder.swift:293)
[ codes.vapor.application ] [ DEBUG ] SELECT "user_task"."id" AS "user_task_id", "user_task"."created_at" AS "user_task_created_at", "user_task"."user_id" AS "user_task_user_id", "user_task"."task_id" AS "user_task_task_id", "task"."id" AS "task_id", "task"."created_at" AS "task_created_at", "task"."updated_at" AS "task_updated_at", "task"."name" AS "task_name", "task"."project_id" AS "task_project_id", "task"."status" AS "task_status", "task"."start_date" AS "task_start_date", "task"."due_date" AS "task_due_date", "task"."content" AS "task_content" FROM "user_task" INNER JOIN "task" ON "user_task"."task_id" = "task"."id" WHERE "task"."project_id" = ? ["C01D220F-42FD-437A-8C31-4D7EE47B781E"] [database-id: raw, request-id: 55BE2766-3393-4FE5-855B-BC87F0FF30FD] (SQLiteNIO/SQLiteConnection.swift:161)
Describe the bug
When performing a delete query on a table using a join to filter based on other tables' values, the resulting query doesn't include the join operation. The query fails as the db can't find the joined tables' columns.
Doing the same query with a
SELECT
(all()
) instead ofDELETE
(delete()
) works as expected.Reproduced on PostgreSQL and SQLite, but other drivers could be affected too.
To Reproduce
Steps to reproduce the behavior:
// or
try await UserTaskPivot.query(on: req.db) .join(parent: .$task) .filter(Task.self, \Task.$project.$id == project.requireID()) .delete()
[ codes.vapor.application ] [ DEBUG ] query delete user_task filters=[task[project_id] = 29498586-42B3-4D92-9D9D-E1D3E1EEAFE4] [database-id: raw, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (FluentKit/QueryBuilder.swift:293) [ codes.vapor.application ] [ DEBUG ] DELETE FROM "user_task" WHERE "task"."project_id" = $1 [29498586-42B3-4D92-9D9D-E1D3E1EEAFE4] [database-id: raw, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (FluentPostgresDriver/FluentPostgresDatabase.swift:29) [ codes.vapor.application ] [ TRACE ] Run action [database-id: raw, psql_connection_action: sendParseDescribeBindExecuteSync(PostgresNIO.PostgresQuery(sql: "DELETE FROM \"user_task\" WHERE \"task\".\"project_id\" = $1", binds: PostgresNIO.PostgresBindings(metadata: [PostgresNIO.PostgresBindings.Metadata(dataType: UUID, format: binary)], bytes: ByteBuffer { readerIndex: 0, writerIndex: 20, readableBytes: 20, capacity: 128, storageCapacity: 128, slice: _ByteBufferSlice { 0..<128 }, storage: 0x0000600002c24000 (128 bytes) }))), psql_connection_id: 3, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:217) [ codes.vapor.application ] [ TRACE ] Backend message received [database-id: raw, psql_connection_id: 3, psql_message: .error(PostgresNIO.PostgresBackendMessage.ErrorResponse(fields: [Routine: "errorMissingRTE", Code: "42P01", Message: "missing FROM-clause entry for table \"task\"", Line: "3553", Position: "31", File: "parse_relation.c", Severity: "ERROR", Localized Severity: "ERROR"])), request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:103) [ codes.vapor.application ] [ TRACE ] Run action [database-id: raw, psql_connection_action: failQuery(PostgresNIO.ExtendedQueryContext, with: PostgresNIO.PSQLError(base: PostgresNIO.PSQLError.Base.server(PostgresNIO.PostgresBackendMessage.ErrorResponse(fields: [Routine: "errorMissingRTE", Code: "42P01", Message: "missing FROM-clause entry for table \"task\"", Line: "3553", Position: "31", File: "parse_relation.c", Severity: "ERROR", Localized Severity: "ERROR"]))), cleanupContext: nil), psql_connection_id: 3, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:217) ... [ codes.vapor.application ] [ WARNING ] server: missing FROM-clause entry for table "task" (errorMissingRTE) [request-id: 89E7A41C-77AD-49AE-808D-E7B54491D626] (Vapor/Middleware/ErrorMiddleware.swift:42)
[ codes.vapor.application ] [ DEBUG ] query delete user_task filters=[task[project_id] = C01D220F-42FD-437A-8C31-4D7EE47B781E] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (FluentKit/QueryBuilder.swift:293) [ codes.vapor.application ] [ DEBUG ] DELETE FROM "user_task" WHERE "task"."project_id" = ? ["C01D220F-42FD-437A-8C31-4D7EE47B781E"] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (SQLiteNIO/SQLiteConnection.swift:161) 2022-09-22 17:13:35.708834+0200 Run[73062:11117473] [logging] no such column: task.project_id in "DELETE FROM "user_task" WHERE "task"."project_id" = ?" [ codes.vapor.application ] [ WARNING ] error: no such column: task.project_id [request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (Vapor/Middleware/ErrorMiddleware.swift:42)
Logs:
The query is built correctly.
Environment
EDIT: seems the same as https://github.com/vapor/fluent-kit/issues/506