Filtering a HasManyThrough relationship on the intermediary table

[tcwalther]

Let's assume the following tables:

struct Country: TableRecord {
    static let passports = hasMany(Passport.self)
    static let citizens = hasMany(Citizen.self, through: passports, using: Passport.citizen)
    ...
}

struct Passport: TableRecord {
    static let country = belongsTo(Country.self)
    static let citizen = belongsTo(Citizen.self)
    var valid: Bool
}
 
struct Citizen: TableRecord {
    static let passports = hasMany(Passport.self)
    static let countries = hasMany(Country.self, through: passports, using: Passport.country)
    ...
}

I'd like to fetch all citizens of a country with a valid passport. Normally I'd write countryRequest.including(all: Country.citizens), but then I can't filter on Passport. How do I do that?

[groue]

Hello @tcwalther,

You know that associations, like requests, can be filtered:

let request = countryRequest
    .filter(...) // filtered request
    .including(all: Country.citizens.filter(...)) // filtered association

Your solution is thus to build the HasManyThrough association from a filtered intermediary association:

extension Country {
    static let validPassports = passports.filter(Column("isValid"))
    static let validCitizens = hasMany(Citizen.self, through: validPassports, using: Passport.citizen)
}

let request = countryRequest.including(all: Country.validCitizens)

Also possible:

extension Country {
    static func passports(valid: Bool) -> HasManyAssociation<Self, Passport> {
        hasMany(Passport.self).filter(Column("isValid") == valid)
    }

    static func citizens(valid: Bool) -> HasManyThroughAssociation<Self, Citizen> {
        hasMany(Citizen.self, through: passports(valid: valid), using: Passport.citizen)
    }
}

let request = countryRequest.including(all: Country.citizens(valid: true))

One last word of caution: take care whenever you want to build requests that involve several kinds of passports and citizens. For example:

let request = countryRequest
    .including(all: Country.citizens(valid: true))
    .including(all: Country.citizens(valid: false))

It is important that distinct associations use distinct association keys. Otherwise GRDB will merge all filters together, and produce requests that may not match your expectation (see this documentation chapter).

In the above request, we need distinct association keys for valid and invalid passports, as well as for valid and invalid citizens. Here is a way to build associations with those distinct keys:

extension Country {
    static func passports(valid: Bool) -> HasManyAssociation<Self, Passport> {
        hasMany(Passport.self)
            .filter(Column("isValid") == valid)
            .forKey((valid ? "valid" : "invalid") + "Passports")
    }

    static func citizens(valid: Bool) -> HasManyThroughAssociation<Self, Citizen> {
        hasMany(Citizen.self, through: passports(valid: valid), using: Passport.citizen)
            .forKey((valid ? "valid" : "invalid") + "Citizens")
    }
}

struct CountryInfo: FetchableRecord, Decodable {
    var country: Country
    var validCitizens: [Citizen]
    var invalidCitizens: [Citizen]
}

let countryInfos = try countryRequest
    .including(all: Country.citizens(valid: true))
    .including(all: Country.citizens(valid: false))
    .asRequest(of: CountryInfo.self)
    .fetchAll(db)

Note that the keys (validPassports, validCitizens, etc.) not only are distinct, but they also match the names of CountryInfo properties. This can guide you when you are wondering which key an association should get. Just imagine you are fetching this association, and which property name you'd like to see in the decoded record.

All in all, I agree this is not the simplest facet of "through" associations 😬.

And let's all be nice with "invalid citizens" 😉

[tcwalther]

Thank you so much, @groue. Your explanation is, as always, concise and fantastic. This solves my question.

This could be something worth including in the documentation - there is an extensive section for filtering associations, but this little part is not covered.

Thanks so much for GRDB. It is hands down one of the best database libraries I've ever used.

[groue]

I fully agree that this is not sufficiently documented. It just happens that this is a real writer challenge. The topic is complicated, and I'm not even sure people would understand the relevance of this extra chapter when they read it (because they haven't met the need yet), or that people would find the extra chapter when they need it (how to search the content of the web page?)

This question comes once in a while from a user like you. Since I'm sure of your full interest, I know that the answer will be efficient. This has helped me stay put in my lazy resting point 😅. One day I'll find the energy to write the needed documentation chapter 🙂