Deleting a Vapor Fluent Pivot

Question

I'm using Vapor to write a server side Swift API, with Fluent to access my Postgres database. It's working well, but I have a couple of questions about Pivots.

I have a Feed model and an Article model. The Feed contains many Articles, and an Article can appear in many Feeds. This is a Sibling relationship as defined by Fluent and I have a Pivot that works nicely for adding Articles to Feeds. However, it is currently possible to add the same Article to the same Feed more than once, since the primary key on the Feed_Article Pivot table is it's own unique id field.

I have two questions:

1. How can I identify if a Pivot already exists?

   • Do I need to get the Feed's sibling Articles and go through them to see if the Article's id is already in there? Seems a little painful.

2. How can I delete a Pivot relationship?

   • Ie. How can I remove an Article from a Feed?
   • At the moment the only way I can do it is by writing the raw SQL to delete from Feed_Article, but that seems wrong.

I'm using the VaporPostgreSQL driver, so I'm not sure that all of the Fluent implementations are available to me (eg. I can't use UUID ids as they are not part of this driver as yet.)

Thanks all

--TJ

Additional:

I've ended up creating a Pivot extension. It works, but it feels like there should be a nicer way to do this. Anyway, sharing in case it helps others.

extension Pivot {

static func remove(leftId: Node?, rightId: Node?) throws {
    /// Get the database driver
    guard let db = drop.database?.driver as? PostgreSQLDriver else {
        Logger.error("Failed to get database")
        return
    }

    /// Check that we have valid id's
    guard let leftId = leftId?.int, let rightId = rightId?.int else {
        Logger.error("Invalid id's")
        return
    }

    /// Delete the rows
    let sql = "DELETE FROM \(name) WHERE \(left.name)_\(left.idKey) = \(leftId) AND \(right.name)_\(right.idKey) = \(rightId)"
    Logger.debug("SQL: \(sql)")
    try db.raw(sql)
}

}

Answer 1

You should be able to query, delete, etc pivots like any other Fluent object.

For example:

try Pivot<User, Pet>.query().fiter("user_id", x).delete()

In Fluent 2 (2.0.0-beta.1 at the time of writing) there are convenience methods on the Siblings relation for doing this.

For example:

try user.pets.add(newPet)