1

I am trying to design a simple search engine in my translation-dictionary app that uses GRDB to handle my database.
I have 4 models that correspond each to a table in my DB: Card, SubCard, WordBox and Translation.

They are linked together like so:
WordBox --> hasMany --> Translation
SubCard --> hasMany --> WordBox
Card --> hasOne --> WordBox (I call it header in my code)
Card --> hasMany --> SubCard
As you can see, WordBox have polymorphic relationship with Card and SubCard

This schema helps me to represent a word and its related derived words. For example, in French the verb "manger" (to eat). I can create subcards for each of the tenses (there are dozens of tenses in French), and as the conjugation differs for each pronouns, each wordbox would correspond to a pronoun. This way I can nicely organize my data

I can fetch all of a card's data through this query:

struct CardInfo: FetchableRecord, Decodable {
    var card: Card
    var header: WordBoxInfo
    var subcards: [SubCardInfo]
}

let query = Card
    .including(required: Card.header
        .including(required: WordBox.translation)
    )
    .including(all: Card.subcards
        .including(all: SubCard.content
            .including(required: WordBox.translation)
        )
    )
    .asRequest(of: CardInfo.self)

But I prefer to fetch only a lighter version (without subcards) for listing displays, as I do not need to display the details. I do it through this query:

struct CardInfoCompressed: FetchableRecord, Decodable {
    var card: Card
    var header: WordBoxInfo
}

let query = Card
    .including(required: Card.header
        .including(required: WordBox.translation)
    )
    .asRequest(of: CardInfoCompressed.self)

Question
My goal is to build search queries based on these requirements:

  1. given a $searchText, I want to look for it in all WordBox.menukad fields, all WordBox.transcription fields and all Translation.body fields for Translation entries that verify Translation.language == LanguageManager.userLanguage
  2. If there are any match, i want to fetch the associated CardInfoCompressed objects and return them.

So there is another degree of complexity, as a WordBox can be connected to a Card or a SubCard. This way if it matches, I then need to fetch the correct cardID by checking the wordBoxableType field, due to polymorphism. If wordBoxableType == card, then i can just return the wordboxableID, but if wordBoxableType == subcard then i need to return the cardID field of the SubCard for which id == wordboxableID. I hope this is not too intricate...
If the Translation.body matches, then i need to look at its WordBox (hasOne) and here we go as above.

I tried several things by defining filters for both my wordbox and translations requirements, then adding them to my query above but it did not work. I am a bit lost on this as I do not find a lot of documentation for complex queries as such. I could use a raw SQL query but I would prefer to achieve this through GRDB.

Below are my structures:

struct Card: Codable {
    var id: Int64?
    var headerID: Int64?
    var shoresh: String? = nil
    var group: CardGroup? = nil
    var tags: [String] = []
    var type: CardType
    var metadata: [String: String] = [:]
    
    var wrappedShoresh: String {
        return shoresh ?? ""
    }
    
    var displayShoresh: String {
        guard let shoresh = shoresh else { return "" }
        return shoresh.map { String($0) }.joined(separator: " - ")
    }

    var wrappedGroupDescription: String {
        return group?.description ?? ""
    }
    
    var wrappedGroupHebrew: String {
        return group?.hebrew ?? ""
    }
}

extension Card: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "card"
    }
    
    enum Columns: String, ColumnExpression {
        case id, headerID, shoresh, group, tags, type, metadata
    }
    
    static let subcards = hasMany(SubCard.self).forKey("subcards")
    var subcards: QueryInterfaceRequest<SubCard> {
        return request(for: Card.subcards)
    }
    
    static let header = hasOne(WordBox.self, using: ForeignKey(["id"], to: ["headerId"])).forKey("header")
    var header: QueryInterfaceRequest<WordBox> {
        return request(for: Card.header)
            .filter(Column("wordBoxableType") == WordBoxableType.card.rawValue)
    }

    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

struct CardInfo: FetchableRecord, Decodable {
    var card: Card
    var header: WordBoxInfo
    var subcards: [SubCardInfo]
}

struct CardInfoCompressed: FetchableRecord, Decodable {
    var card: Card
    var header: WordBoxInfo
}

struct WordBox: Codable {
    var id: Int64?
    var wordBoxableID: Int64
    var wordBoxableType: WordBoxableType
    var pronouns: [Pronoun]
    var menukad: String
    var transcription: String
    
    var wrappedPronounsHebrew: [String] {
        return pronouns.map { $0.description }
    }
    
    var wrappedPronounsTranscription: [String] {
        return pronouns.map { $0.caseName }
    }
}

extension WordBox: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "wordBox"
    }
    
    enum Columns: String, ColumnExpression {
        case id, wordBoxableID, wordBoxableType, pronouns, menukad, transcription
    }
    
    static let translations = hasMany(Translation.self, using: ForeignKey(["wordBoxID"], to: ["id"]))
    var translations: QueryInterfaceRequest<Translation> {
        return request(for: WordBox.translations)
    }
    
    static let translation = hasOne(Translation.self, using: ForeignKey(["wordBoxID"], to: ["id"]))
    var translation: QueryInterfaceRequest<Translation> {
        return request(for: WordBox.translations)
            .filter(Column("language") == LanguageManager.shared.currentLanguage.description)
    }
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
    
    func fetchAssociatedCard(db: Database) throws -> Card? {
        switch wordBoxableType {
        case .card:
            return try Card.fetchOne(db, key: wordBoxableID)
        case .subcard:
            if let subCard: SubCard = try SubCard.fetchOne(db, key: wordBoxableID) {
                return try subCard.fetchCard(db: db)
            }
        }
        return nil
    }
}

struct WordBoxInfo: FetchableRecord, Decodable {
    var pronouns: [String]
    var menukad: String
    var transcription: String
    var translation: Translation
}

enum WordBoxableType: String, Codable {
    case card
    case subcard
}

struct SubCard: Codable {
    var id: Int64?
    var cardID: Int64?
    var title: String
}

extension SubCard: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "subCard"
    }
    
    enum Columns: String, ColumnExpression {
        case id, cardID, title
    }
    
    static let card = belongsTo(Card.self)
    var card: QueryInterfaceRequest<Card> {
        return request(for: SubCard.card)
    }
    
    static let content = hasMany(WordBox.self, using: ForeignKey(["wordBoxableID"], to: ["id"])).forKey("content")
    var content: QueryInterfaceRequest<WordBox> {
        return request(for: SubCard.content)
            .filter(Column("wordBoxableType") == WordBoxableType.subcard.rawValue)
    }
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
    
    func fetchCard(db: Database) throws -> Card? {
        return try card.fetchOne(db)
    }
}

struct SubCardInfo: FetchableRecord, Decodable {
    var title: String
    var content: [WordBoxInfo]
}

struct Translation: Codable {
    var id: Int64?
    var wordBoxID: Int64
    var language: Language
    var body: String
}

extension Translation: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "translation"
    }
    
    enum Columns: String, ColumnExpression {
        case id, wordBoxID, language, text
    }
    
    static let wordBox = belongsTo(WordBox.self)
    var wordBox: QueryInterfaceRequest<WordBox> {
        return request(for: Translation.wordBox)
    }
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}
2

First I want to clarify a point. I want to do this using GRDB requests and not raw SQL because GRDB request directly map and init my entities which is very convenient. I do not know how I would do the same with raw SQL.

Now that being said, I went through the GRDB files SQLInterpolation+QueryInterface.swift and TableRecord+QueryInterfaceRequest.swift but I could not find a solution.

At first to simplify my problem, I tried to make it work with a simplified request.
So just try to fetch the CardInfoCompressed entities that have a header.transcription that starts with the string 'bok'.
I naively tried with this:

let myFilter = WordBox
    .filter(
        Column("transcription").like("bok%")
    ).exists()

let query = Card
    .filter(myFilter)
    .including(required: Card.header
        .including(required: WordBox.translation)
    )
    .asRequest(of: CardInfoCompressed.self)

But this did not work. It returns every cards, undistinctly.
To understand why, we need to look at the request that was produced:

SELECT "card".*, "wordBox".*, "translation".* 
    FROM "card" 
    JOIN "wordBox" ON "wordBox"."id" = "card"."headerId" 
    JOIN "translation" ON "translation"."wordBoxID" = "wordBox"."id" 
    WHERE EXISTS (
        SELECT * FROM "wordBox" 
        WHERE "transcription" LIKE 'bok%'
    )

The problem lies in the WHERE EXISTS clause. It only checks if a wordBox entity that has a transcription that starts with 'bok' exists. It does not take into account any card object, so this clause is verified for any card, and hence, it returns all the cards undistinctly.

The corrected request would be something like:

SELECT "card".*, "wordBox".*, "translation".*
FROM "card"
JOIN "wordBox" ON "wordBox"."id" = "card"."headerId"
JOIN "translation" ON "translation"."wordBoxID" = "wordBox"."id"
WHERE EXISTS (
    SELECT 1 FROM "wordBox" as "wb"
    WHERE "wb"."transcription" LIKE 'bok%'
      AND "wb"."id" = "card"."headerId"
)

Inside the WHERE clause, we take into account that the wordBox that should verify the filter, should also be associated with the selected card.

Is this feasible to do using GRDB ?
I will be looking and will post if I find a solution.