Vapor+Fluent and 2 foreign keys to same parent table

Related Projects Vapor
,
1

I am trying to do 2 joins to the same parent table on two different columns. Is this even possible? What I have:

    func allItemsHandler(_ req: Request) throws -> Future<View> {
        let templateName = "\(_itemsName)All"
        let title = _itemPublicName

        return try MemoEntity.query(on: req)
            .join(field: \Asset.id, to: \.imageID)
            .alsoDecode(Asset.self)
            // This does not work
            // .join(field: \Asset.id, to: \.soundID)
            // .alsoDecode(Asset.self)
            .all()
            .flatMap(to: View.self) { data in
                let items: [AllMemoEntityContext] = data.map { (arg) in
                    let (memoEntity, image) = arg
                    // let ((memoEntity, image), sound) = arg
                    return AllMemoEntityContext(item: memoEntity,
                                                image: image,
                                                sound: nil)
                }
                
                let context = AllMemoEntitiesContext(
                    title: title,
                    items: items)
                return try req.leaf().render(templateName, context)
        }
    }

MemoEntity has two foreign keys to Asset using imageID and soundID columns. The above works fine for getting the image joined in but I cannot get the sound joined in. If I add the commented lines I get

[ ERROR ] SQLiteError.error: ambiguous column name: main.assets.id (SQLiteQuery.swift:91)

Any hints?

2

I'm not sure if you can join two fields to the same column. I've never tried that. However, the error you are getting there seems unrelated. My guess is that Fluent is not using a qualified column name (just column instead of table.column). That is likely already fixed on the development branches (should be going live very soon) but you should create an issue on GitHub - vapor/fluent-sqlite-driver: Fluent driver for SQLite just to be sure.