Searching for items with tags

[ethankay]

I have the following model:

migrator.registerMigration("createInitialTables") { db in
  try db.create(table: "word", ifNotExists: true) { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
  }
  
  try db.create(table: "tag", ifNotExists: true) { t in
    t.column("name", .text).notNull().unique(onConflict: .replace)
    t.column("mappedWordsCount", .integer).notNull()
  }
  
  try db.create(table: "tagToWord", ifNotExists: true) { t in
    t.column("tag", .integer).notNull()
    t.column("word_id", .integer).notNull()
  }
}

In this example there are words, each of which can have any number of tags on them, which for now are stored in a separate table so they can be used for joins.

My goal is to be able to construct a filter that returns the list of words that have every tag in some set of tags, for example:

func getWords(withTags: [String]): [Word] {
   // ....
}

The problem I'm facing is that I only know how to construct the query such that it returns if a word has any one of the requested tags (e.g. if I ask for words with tags "tag1, tag2, tag3" I would get back a word even if it didn't have "tag3" so long as it has either "tag1" or "tag2"). What I have right now:

struct TagToWord: Hashable {
  var tag: String
  var word_id: Int64
}

extension TagToWord: Codable, MutablePersistableRecord {  
  enum Columns {
    static let tag = Column(CodingKeys.tag)
    static let word_id = Column(CodingKeys.word_id)
  }
}

struct Word: Identifiable, Hashable {
  var name: String
  var id: Int64?
}

extension Word: Codable, FetchableRecord, MutablePersistableRecord {
  static let tags = hasMany(TagToWord.self, using: ForeignKey(["word_id"]))

  enum Columns {
    static let id = Column(CodingKeys.id)
    static let name = Column(CodingKeys.name)
  }
  
  mutating func didInsert(with rowID: Int64, for column: String?) {
    id = rowID
  }
}

extension DerivableRequest where RowDecoder == Word {
  func withTags(_ tags: [String]) -> Self {
    joining(required: Word.tags.filter(tags.contains(TagToWord.Columns.tag))).distinct()
  }
}

I would think it should be something like the following, though this of course doesn't work:

func withTags(_ tags: [String]) -> Self {
  joining(required: Word.tags.filter(tags.every(TagToWord.Columns.tag))).distinct()
}

Is there anything I can change to get this desired behavior (data model, query, etc.)? Thanks in advance!

[groue]

Hello @ethankay,

In order to solve this puzzle, you can take inspiration from the joining(required:) example:

// Fetch books by French authors
let request = Book.joining(required: Book.author.filter(Column("country") == "France"))

In your case, you want to fetch words that are joined to tag "foo", but also to tag "bar", and to tag "baz", etc:

// Fetch words with tags "foo", "bar", and "bar"
let request = Word
    .joining(required: ... "foo")
    .joining(required: ... "bar")
    .joining(required: ... "baz")

This gives:

extension Word {
    // Association to the tag named `tag`
    static func tag(named tag: String) -> HasOneAssociation<Word, TagToWord> {
        // Prefer hasOne to hasMany, since there exists at most one row
        // in the tagToWord table for a given tag.
        hasOne(TagToWord.self, using: ForeignKey(["word_id"]))
            .filter(TagToWord.Columns.tag == tag)
            // Use one distinct association key per tag, so that we can use several
            // of those associations in a single request without any conflict due to
            // https://github.com/groue/GRDB.swift/blob/master/Documentation/AssociationsBasics.md#refining-association-requests
            .forKey("tagToWord_\(tag)")
    }
}

extension DerivableRequest where RowDecoder == Word {
    /// Returns a request for words tagged with all tags in `tags`
    func tagged(_ tags: Set<String>) -> Self {
        var request = self
        for tag in tags {
            request = request.joining(required: Word.tag(named: tag))
        }
        return request.distinct()
    }
}

Possible variant

extension DerivableRequest where RowDecoder == Word {
    /// Returns a request for words tagged with the given tag
    func tagged(_ tag: String) -> Self {
        joining(required: Word.tag(named: tag))
    }

    /// Returns a request for words tagged with all tags in `tags`
    func tagged(_ tags: Set<String>) -> Self {
        tags
            .reduce(self, { (request, tag) in request.tagged(tag) })
            .distinct()
    }
}

Usage:

try dbQueue.write { db in
    try db.execute(sql: """
        INSERT INTO word (id, name) VALUES (1, 'A');
        INSERT INTO tagToWord (word_id, tag) VALUES (1, 'foo');
        INSERT INTO tagToWord (word_id, tag) VALUES (1, 'bar');

        INSERT INTO word (id, name) VALUES (2, 'B');
        INSERT INTO tagToWord (word_id, tag) VALUES (2, 'foo');
        INSERT INTO tagToWord (word_id, tag) VALUES (2, 'bar');
        INSERT INTO tagToWord (word_id, tag) VALUES (2, 'baz');

        INSERT INTO word (id, name) VALUES (3, 'C');
        INSERT INTO tagToWord (word_id, tag) VALUES (3, 'foo');
        INSERT INTO tagToWord (word_id, tag) VALUES (3, 'baz');
        """)

    func test(_ db: Database, tags: Set<String>) throws {
        print("Words for tags \(tags):")
        let words = try Word.all().tagged(tags).fetchAll(db)
        print(words.map(\.name).joined(separator: ", "))
    }

    try test(db, tags: ["foo"])               // A, B, C
    try test(db, tags: ["bar"])               // A, B
    try test(db, tags: ["foo", "bar"])        // A, B
    try test(db, tags: ["foo", "bar", "baz"]) // B
}

[ethankay]

@groue Thanks again for the help above. I have a follow up on this question. Please let me know if you'd prefer it as a separate Q&A. I'm starting it here as it shares the same schema.

How would I express the query for finding all words without a certain tag (or tags) (see here for raw sql inspiration)?

I believe as such the goal is to create the grdb equivalent of the below (which when executed directly produces the correct results, namely, all Words that don't have the tag "some_tag"):
  

SELECT Words.* FROM Words 
LEFT JOIN TagToWord ON TagToWord.wordId = Words.id 
WHERE TagToWord.tag != "some_tag" OR TagToWord.tag is null

My two best guesses for appropriate GRDB style queries are below:

extension Word {
    static func notTag(named tag: String) -> HasOneAssociation<Word, TagToWord> {
        hasOne(TagToWord.self, using: ForeignKey(["word_id"]))
             // Option 1: .filter(TagToWord.Columns.tag != tag || TagToWord.Columns.tag == nil)
            .forKey("tagToWord_\(tag)")
    }
}

extension DerivableRequest where RowDecoder == Word {
    func withoutTag(tag: String) -> Self {
        including(optional: Word.notTag(named: tag))
             // Option 2: .filter(TagToWord.Columns.tag != tag || TagToWord.Columns.tag == nil)
    }
}

Option 1 doesn't work as it just adds an AND clause to the JOIN ... ON instead of a WHERE, and Option 2 doesn't work as it adds the WHERE on the Word table (e.g. Word.TagToWord.tag instead of TagToWord.tag).

Any thoughts are much appreciated.

[groue]

@groue Thanks again for the help above. I have a follow up on this question. Please let me know if you'd prefer it as a separate Q&A. I'm starting it here as it shares the same schema.

Since you're asking, then yes it is better when each discussion or issue has one single focus.

The reasons for this is that our conversation is public, and you're enjoying it for free. The reason why it is public and free is that it is meant to help other users as well. And other users can be helped if and only if they can find this conversation by searching (on Google or Github search pages). Such search is much more efficient when discussions/issues have a clear title and a single focus.

[groue]

In order to add a WHERE clause to the Words table that involves the TagToWord associated table, you'll need a TableAlias.

[groue]

Since you're asking, then yes it is better when each discussion or issue has one single focus.

This was a general answer. In our specific case, the discussion title is "Searching for items with tags". In this specific context, "Searching for items without specific tags" is a natural extension, so your follow-up question is quite ok.

[ethankay]

TableAliases solved it. Thanks again!