Grdb.swift: How to represent hasMany <-> belongsToMany relationship?

[bellebethcooper]

Hello,

I'm stuck on figuring out how to implement something specific with GRDB. I'm working on a bookmarking client that has Bookmark records. Each bookmark can have several tags, and tags can belong to more than one bookmark.

I've looked into associations and foreign keys but I can't figure out how to represent this relationship, since tags can belong to many bookmarks, and bookmarks can have many tags. The only idea I have so far is to create a separate table that holds records representing the relationships between tags and bookmarks, so each record holds one bookmark and one of its tags.

Is there anything I'm missing about how else to make this work?

Thanks!

Environment

GRDB flavor(s): GRDB
GRDB version: 3.1
Installation method: CocoaPods
Xcode version: 9.4.1
Swift version: 4.1
Platform(s) running GRDB: iOS
macOS version running Xcode: 10.13.5

[sobri909]

The only idea I have so far is to create a separate table that holds records representing the relationships between tags and bookmarks, so each record holds one bookmark and one of its tags.

Yep, sounds to me like you've got the right answer. A many-to-many table is the usual way to do it.

https://en.wikipedia.org/wiki/Many-to-many_(data_model)

I don't know enough about the new GRDB3 relationship APIs to tell you how to model it in GRDB though. I'm sure @groue knows though 😉

[bellebethcooper]

@sobri909 Thanks very much!

[groue]

Hello both,

@bellebethcooper, @sobri909 is right: a many-to-many relationship between bookmarks and tags needs a middle table (let's call it "tagging"). Depending on the definition of your database tables for bookings and tags, it could look like:

db.create(table: "tagging") { t in
    t.primaryKey(["tagId", "bookmarkId"])
    t.column("tagId", .integer)
        .notNull()
        .references("tag", onDelete: .cascade)
    t.column("bookmarkId", .integer)
        .notNull()
        .references("bookmark", onDelete: .cascade)
        .indexed()
}

Now this answers the question at the database level. But how do we deal with such relationship in Swift?

---

As you have seen, the current associations api does not deal with this kind of relations yet. I do hope that the number of supported relationships extends, but meanwhile we're on a dry diet.

And I'm not sure I can provide good advice, because associations are still in the exploratory phase, and Ì'm not sure how your application needs to use bookmark and tags.

We can do a little exploration together, if you want. Let's say the application needs to find all bookmarks for a given tag (this looks useful at first sight).

---

Let's start from the beginning. There are several SQL requests that produce our results:

-- request 1
SELECT * FROM bookmark
WHERE id IN (SELECT bookmarkId FROM tagging WHERE tagId = 123)

-- request 2
SELECT bookmark.*
FROM bookmark
JOIN tagging ON tagging.bookmarkId = bookmark.id AND tagId = 123

-- request 3
SELECT bookmark.*
FROM tagging
JOIN bookmark ON bookmark.id = tagging.bookmarkId
WHERE tagging.tagId = 123

Fine! Let's pick one:

extension Bookmark {
    static func filter(tagId: Int64) -> SQLRequest<Bookmark> {
        return SQLRequest<Bookmark>("""
            SELECT * FROM bookmark
            WHERE id IN (SELECT bookmarkId FROM tagging WHERE tagId = ?)
            """, arguments: [tagId])
    }
}

// All bookmarks for tag 123
let bookmarks = try dbQueue.read { db in
    try Bookmark.filter(tagId: 123).fetchAll(db)
}

Unfortunately one can't derive new requests from an SQL request, and refine it with extra filtering or ordering:

// nope
Bookmark
    .filter(tagId: 123)
    .filter(host: "github.com")
    .order(Column("createdAt").desc)

For that, we really need SQL generation.

---

(Time passes in a playground, looking for solutions...)

~Despite my attempts, existing associations did not bring any help.~

Edit: I was wrong. Please ignore the remaining of this comment, and jump to the next one.

I have some working code, but it is far from obvious, and it uses experimental apis (apis which may break without notice in a future release, are hardly documented, and were not audited for convenience):

/// An helper type for Bookmark.filter(tagId:).
/// Warning: it uses experimental GRDB apis
fileprivate struct TaggedBookmarkSubquery: SQLCollection {
    var tagId: Int64

    func collectionSQL(_ context: inout SQLGenerationContext) -> String {
        var sql = "SELECT bookmarkId FROM tagging WHERE tagId = "
        sql += tagId.sqlExpression.expressionSQL(&context) // avoid SQL injection
        return sql
    }
}

extension DerivableRequest where RowDecoder == Bookmark {
    /// All bookmark requests can be refined with tag filtering
    ///
    ///     let derivedRequest = request.filter(tagId: ...)
    func filter(tagId: Int64) -> Self {
        let subquery = TaggedBookmarkSubquery(tagId: tagId)
        return filter(subquery.contains(Column("id")))
    }
}

extension Bookmark {
    /// A convenience method
    ///
    ///     let request = Bookmark.filter(tagId: ...)
    static func filter(tagId: Int64) -> QueryInterfaceRequest<Bookmark> {
        return all().filter(tagId: tagId)
    }
}

/// It works!
let bookmarks = try dbQueue.read { db in
    try Bookmark
        .filter(...)
        .filter(tagId: 123)
        .order(...)
        .fetchAll(db)
}

OK, I stop the exploration here.

Thanks, @bellebethcooper, your question made it clear that extra work has to be done in order to make life easier for all GRDB users who meet many-to-many relationships.

If you have any other question, please ask!

[groue]

Despite my attempts, existing associations did not bring any help.

Oops, I was wrong. Sorry I have to invent the whole story with you, because I don't meet many-to-many associations that often. I haven't met any since associations were introduced.

Here is a better chapter, without any experimental api.

We're still looking after a request for all bookmarks attached to a given tag. We want to be able to further refine this request with more filtering or ordering.

First declare the Bookmark.taggings association (it is the minimal setup for our purpose):

extension Bookmark {
    static let taggings = hasMany(Tagging.self)
}

I also declare Tagging colums, because we are going to need them. A recommended way to do this is a Columns enum:

extension Tagging {
    enum Columns: String, ColumnExpression {
        case tagId, bookmarkId
    }
}

Now we can define the filter(tagId:) request refining method:

extension QueryInterfaceRequest where RowDecoder == Bookmark {
    /// All bookmark requests can be refined with tag filtering
    ///
    ///     let derivedRequest = request.filter(tagId: ...)
    func filter(tagId: Int64) -> QueryInterfaceRequest<Bookmark> {
        // We require that a bookmark can be joined to a tagging
        // that has the requested tagId:
        let tagging = Bookmark.taggings.filter(Tagging.Columns.tagId == tagId)
        return joining(required: tagging)
    }
}

See joining methods for more information.

We also add the convenience Bookmark.filter(tagId:) method:

extension Bookmark {
    /// A convenience method
    ///
    ///     let request = Bookmark.filter(tagId: ...)
    static func filter(tagId: Int64) -> QueryInterfaceRequest<Bookmark> {
        return all().filter(tagId: tagId)
    }
}

Why not a convenience tag.bookmarks property as well?

extension Tag {
    /// A convenience property
    ///
    ///     let tag: Tag = ...
    ///     let bookmarks = try tag.bookmarks.filter(...).order(...).fetchAll(db)
    var bookmarks: QueryInterfaceRequest<Bookmark> {
        if let id = id {
            return Bookmark.filter(tagId: id)
        } else {
            return Bookmark.none()
        }
    }
}

And there we go:

// All bookmarks for tag 123
let bookmarks = try dbQueue.read { db -> [Bookmark] in
    if let tag = try Tag.fetchOne(db, key: 123) {
        return try tag.bookmarks.fetchAll(db)
    } else {
        return []
    }
}

[bellebethcooper]

@groue Thanks very much for the quick and thorough response! It looks like this approach should work for me, as my needs are pretty similar (finding all tags for a single bookmark, in order to display tags in the list of bookmarks UI). I really appreciate your help!

[groue]

Note to myself: check if #490 is able to improve the landscape here, since "HasManyThrough" is mostly about many-to-many relationships.

[simensol]

Is a middle table the best approach for many-to-many relationships in the current version (v4.14.0), @groue?

[groue]

Hello @simensol. Pivot tables are the correct way to implement many-to-many relationships in relational databases in general, yes. GRDB supports them since GRDB 4, shipped on May 2019. This whole conversation was written before, and may create confusion.

See https://github.com/groue/GRDB.swift/issues/711#issuecomment-592386811, and HasManyThrough.

[simensol]

Thanks, @groue!