Many to many pivot with additional variables


I’m trying to create a many to many relationship with additional variables but can not find any examples.

I have User and Group objects and want to create if a user has either view or write privileges for groups.

here is my pivot class definition:

import FluentPostgreSQL
import Foundation

final class UserGroupPivot: PostgreSQLUUIDPivot {
var id: UUID?
var canRead: Bool
var canWrite: Bool

var userID: User.ID
var groupID: Group.ID

typealias Left = User
typealias Right = Group

static let leftIDKey: LeftIDKey = \.userID
static let rightIDKey: RightIDKey = \.groupID

init (_ user: User, _ group: Group) throws {
  self .userID = try user.requireID()
  self .groupID = try group.requireID()

  self .canRead = false
  self .canWrite = false

init (_ user: User, _ group: Group, canRead: Bool, canWrite: Bool) throws {
  self .userID = try user.requireID()
  self .groupID = try group.requireID()

  self .canRead = canRead
  self .canWrite = canWrite


extension UserGroupPivot: Migration {}
extension UserGroupPivot: ModifiablePivot {}

I can see in the tables that the extra variables are created, but I don’t know how to populate these data.

How can I do a CRUD operation also how to know if a user can write to a group.

Thanks in advance

Will we be able to do this in Vapor 4?


@ramelan hey apologies for the delay! I’m not sure what the state of Vapor 4 Fluent is but it should definitely be possible. However you can do it in Vapor 3 as well, you just may need to write some queries yourself.

So to start with you can’t use a ModifiablePivot, only a Pivot - that means you can’t use the attach function but you can create the pivots manually and just save them (since they’re just models). Since you don’t need ModifiablePivot you can remove the init (_ user: User, _ group: Group) initialiser and that gives you the option to create the pivots however you like. Does that make sense?

For querying, things get a bit more complicated. It may be possible to specify more filter options user.groups.filter(...).query(on: req) but I’m not sure that will work. If you look into the implementation of siblings() you can just copy that and add your extra filter options there. Hope that helps!

Hi @0xtim,

Thank you for your help. Apologize for the late reply too as I’m traveling now.

I’ll try to implement your suggestion and let you know how it goes.

Thanks again

1 Like