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:
- given a
$searchText, I want to look for it in allWordBox.menukadfields, allWordBox.transcriptionfields and allTranslation.bodyfields forTranslationentries that verifyTranslation.language == LanguageManager.userLanguage - If there are any match, i want to fetch the associated
CardInfoCompressedobjects 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
}
}