Foreign key that references a column that is already part of another foreign key generates invalid code

[micampe]

When you have a foreign key that references a column that is already part of a another fk the code generated to lookup the related records (parent → children and child → parent in this case) is invalid.

create table grandparent (
    id integer primary key,
    name text
);

create table parent (
    id integer primary key,
    name text,

    foreign key (id) references grandparent(id)
);

create table child (
    id integer primary key,
    name text,

    foreign key (id) references parent(id)
);

attaching complete sample project but the SQL should be all you need to reproduce.

ForeignKeys.zip

[helje5]

This fails:

public extension SQLRecordFetchOperations
  where T == Parent, Ops: SQLDatabaseFetchOperations, Ops.RecordTypes == ForeignKeys.RecordTypes
{
  func find(`for` record: Child) throws -> Parent?
  {
    let x : SQLRecordFetchOperations<Ops, Child> = operations[dynamicMember: \.children]
    return try x.findTarget(for: \.id, in: record /* Child */) // No exact matches in call to instance method 'findTarget'
  }
}

Maybe because of ambiguous overloads?

public extension SQLRecordFetchOperations
  where T == Parent, Ops: SQLDatabaseFetchOperations & SQLDatabaseAsyncOperations, Ops.RecordTypes == ForeignKeys.RecordTypes
{
  func find(`for` record: Child) async throws -> Parent?
  {
    try await operations[dynamicMember: \.children].findTarget(for: \.id, in: record)
  }
}

[helje5]

Hm, no, this doesn't match is not found:

public extension SQLRecordFetchOperations
                   where Ops: SQLDatabaseAsyncOperations
  func findTarget<FK>(for foreignKey: KeyPath<T.Schema, FK>, in record: T)
         async throws -> FK.Destination?
         where FK: SQLForeignKeyColumn, FK.T == T,
               FK.Value == FK.DestinationColumn.Value

[helje5]

Just collecting data, Child (btw the pkey is optional / NULL):

public struct Child : Identifiable, SQLKeyedTableRecord, Codable, Sendable {
  public static let schema = Schema()
  public var id : Int?
  public var name : String?
  public init(id: Int? = nil, name: String? = nil)
}
public struct Parent : Identifiable, SQLKeyedTableRecord, Codable, Sendable {
  public static let schema = Schema()
  public var id : Int?
  public var name : String?
  public init(id: Int? = nil, name: String? = nil)
}
public struct Grandparent : Identifiable, SQLKeyedTableRecord, Codable, Sendable {
  public static let schema = Schema()
  public var id : Int?
  public var name : String?
  public init(id: Int? = nil, name: String? = nil)
}

public extension Grandparent {
  struct Schema : SQLKeyedTableSchema, SQLSwiftMatchableSchema, SQLCreatableSchema {
    
    public typealias PropertyIndices = ( idx_id: Int32, idx_name: Int32 )
    public typealias RecordType = Grandparent
    public typealias MatchClosureType = ( Grandparent ) -> Bool
    
    public static let externalName = "grandparent"
    
    public static let columnCount : Int32 = 2
    
    public static let primaryKeyColumn = MappedColumn<Grandparent, Int?>(
      externalName: "id",
      defaultValue: nil,
      keyPath: \Grandparent.id
    )
...
    public let id = MappedColumn<Grandparent, Int?>(
      externalName: "id",
      defaultValue: nil,
      keyPath: \Grandparent.id
    )
    public let name = MappedColumn<Grandparent, String?>(
      externalName: "name",
      defaultValue: nil,
      keyPath: \Grandparent.name
    )