Setting property in pivot table in many to many relation

Question

I have set up a many to many relation Locale <-> Site via a pivot table LocaleSite in Vapor 4. This is working, I can attach Locale to Site and vice versa. In the Pivot table, I added a property .sortOrder:

final class LocaleSite : Model {
    static let schema = "locale+site"
    
    struct FieldKeys {
        static var createdAt: FieldKey { "created_at" }
        static var updatedAt: FieldKey { "updated_at" }
        static var deletedAt: FieldKey { "deleted_at" }
        static var locale: FieldKey { "localeID" }
        static var site: FieldKey { "siteID" }
        static var sortOrder: FieldKey { "sortOrder" }
    }
    
    
    @ID
    var id: UUID?
    
    @Timestamp(key: FieldKeys.createdAt, on: .create)
    var createdAt: Date?

    @Timestamp(key: FieldKeys.updatedAt, on: .update)
    var updatedAt: Date?
    
    @Timestamp(key: FieldKeys.deletedAt, on: .delete)
    var deletedAt: Date?

    @Parent(key: FieldKeys.locale)
    var locale: Locale
    
    @Parent(key: FieldKeys.site)
    var site: Site
    
    @Field(key: FieldKeys.sortOrder)
    var sortOrder: Int

When attaching an array of Locales

for locale in locales {
    let _ = site.$locales.attach(locale, on: database)
}

I would like to set the .sortOrder to a certain value, in the same loop while attaching (maybe via the attach function?), but can not find how in the docs.

(The only workaround at the moment is after attaching is to query my site or locale, and via a relation to the pivot table setting the .sortOrder. Very inefficient...)

Added code sample incorporating some of Calebs answer (See comment)

static func createStdData (site: Site, database : Database) -> EventLoopFuture<Void> {
        return Locale.allLocale(database: database).map { locales in
            var order = 0
            for locale in locales {
                let _ = site.$locales.attach(locale, on: database, edit: { pivot in
                    pivot.sortOrder = order
                })
                order += 1
            }
        }
    }

As soon as the edit: is added I get the following error:

No exact matches in call to instance method 'attach'

Checking the Vapor sources I see that there is one but apparently the syntax may not be completely right...

I also tried:

let _ = site.$locales.attach(locale, on: database, edit: (LocaleSite){ pivot in
                    pivot.sortOrder = order
                })

but the I get :

Type of expression is ambiguous without more context

Answer 1

What value you want to assign the .sortOrder property to will change how you will want to set that value. Based on your question I am going to assume that each pivot should have a .sortOrder that is 1 greater than the last.

To do this, we will first have to get the last pivot saved, before we start the loop. This is fairly straight forward:

let last = LocaleSite.query(on: database).sort(\.$sortOrder, .descending).first()

This query will order the results from highest .sortOrder to lowest, so the first one will have the highest value. We can now map that result. If we got a model back, we will base the new .sortOrder values off of it; otherwise, we'll just start from 0.

let sortOrder = last.map { pivot in
    return pivot?.sortOrder ?? 0
}

Now we will call .flatMap on the result. In the .flatMap closure, we will iterate over the Locale models that you want to insert using Array.map and attach each Locale to the site. Now, the .attach method has a handy closure that you can pass in that you can use to edit the new pivot models before they are inserted into the database. We are going to use that closure to set the pivot's .sortOrder value.

sortOrder.flatMap { order in
    locales.enumerated().map { offset, locale in
        site.$locales.attach(locale, on: database, { pivot in 
            pivot.sortOrder = (order + 1) + offset
        })
    }.flatten(on: database.eventLoop)
}

_{* All code is written off the top of my head and untested, so it might need a touch up to get working properly}