Delete with Join

[leogdion]

Describe the bug

Anytime a join or with are involved in a delete query, I receive the follow error:

[ WARNING ] server: missing FROM-clause entry for table "WorkoutGroup" (errorMissingRTE) [request-id: 7937469E-9905-4490-9C42-82712CBDFE6F]

Here's an example of query which fails:

    let items = WorkoutSchedule.query(on: request.db)
      .join(parent: \.$workoutGroup)
      .filter(\.$id == scheduleID)
      .filter(WorkoutGroup.self, \WorkoutGroup.$id == workoutGroupId)
      .filter(WorkoutGroup.self, \WorkoutGroup.$instructor.$id == userID)
    
    return items.delete().transform(to: HTTPResponseStatus.ok)

Here's the workaround

    let items = WorkoutSchedule.query(on: request.db)
      .join(parent: \.$workoutGroup)
      .filter(\.$id == scheduleID)
      .filter(WorkoutGroup.self, \WorkoutGroup.$id == workoutGroupId)
      .filter(WorkoutGroup.self, \WorkoutGroup.$instructor.$id == userID)         
    
    return items.all().flatMapEach(on: request.eventLoop, {$0.delete(on: request.db)}).transform(to: HTTPResponseStatus.ok)

In both cases WorkoutGroup is a parent of WorkoutSchedule.

To Reproduce

Packages in use:

    .package(url: "https://github.com/vapor/vapor.git", from: "4.5.0"),
    .package(url: "https://github.com/vapor/jwt.git", from: "4.0.0"),
    .package(url: "https://github.com/vapor/fluent.git", from: "4.0.0"),
    .package(url: "https://github.com/vapor/fluent-postgres-driver.git", from: "2.0.0"),

Expected behavior

Shouldn't need to query all to delete

Environment

• Vapor Framework version: 4.5.0
• Vapor Toolbox version: NA
• OS version: macOS 12.3.1 (21E258)

[0xTim]

@leogdion could you enable debug logging and add the generated SQL?

cc @gwynne

[gwynne]

DELETE queries which require joins (also known as multi-table delete) are not currently supported by Fluent (or even by SQLKit), due to the lack of consistent support or syntax between different databases. Reclassifying as a high-priority feature request.

[samdze]

Experienced the same issue. Probably reported in the wrong place but here it is: https://github.com/vapor/fluent/issues/748 I added logs and examples of the corresponding SELECT query in the OP.

Feel free to close it if not useful.