GRDB: How to save column data from one table to another one?

Related Projects GRDB
,
1

Hi guys!

I will need help with GRDB. I am not too much into it but I have already read documentation but it couldn't help me too much for now.

For an example imagine I have 3 tables, first one: account, second one: documents, third one accountDocument. First table is created to store data for an account such us id, name, location and document_ids. Document_ids is string in sql but in JSON it's array of strings.

Second table is for of course as name says documents. Document table contains id, name, files.

Third table is created as connection between these two tables.

TABLE: accounts

id name document_ids
1 Eldar 122,13,34
2 User 13,28
3 Grdb 10

TABLE: documents

id name
10 FirstDocument
13 Test.pdf
28 Chelsea.jpeg
34 Logo.png
122 Xcode.dmg

TABLE: accountDocument

The id of accountDocument table will be auto generated since I would like to use LEFT JOIN so I can join documents table with accountDocument table.

id account_id document_id
1 1 13
2 1 34
3 1 122
3 2 13
5 2 28
6 3 10

So schema is the next one:
Account table:

        migrator.registerMigration("6") { db in
            try db.create(table: "account") { t in
                t.primaryKey(["id"])
                t.column("id", .integer).notNull()
                t.column("name", .text).notNull()
                t.column("documentIds", .text).notNull()
            }
        }

Document table

        migrator.registerMigration("7") { db in
            try db.create(table: "documents") { t in
                t.primaryKey(["id"])
                t.column("id", .integer).notNull()
                t.column("name", .text).notNull()
            }
        }

And the "pivot" table if I can call it like that would be something like this:

        migrator.registerMigration("8") { db in
            try db.create(table: "accountDocument") { t in
                t.primaryKey(["id"])
                t.column("id", .integer).notNull()
                t.column("accountId", .integer).notNull()
                t.column("documentId", . integer).notNull()
            }
        }

Also I created model for it:

import Foundation
import GRDB

struct AccDocs: Hashable {
    var id: Int?
    var accountId: Int64?
    var documentId: Int64?
}

extension AccDocs: Codable, FetchableRecord, MutablePersistableRecord {
    enum Columns {
        static let id = Column(CodingKeys.id)
        static let infrastructureId = Column(CodingKeys.accountId)
        static let documentsIds = Column(CodingKeys.documentId)
    }
}

As an output of JSON documentIds (table: account, column: documentIds) is like this:

{ 
 "document_ids": ["13","34","122"]
}

I tried to print the row and get this one:

[documentids:"[\"98\",\"545\"]"]

So my question would be:

- How can I save data from table account with column documentIds to new table column e.g. accountDocument[documentId]? I was thinking to separate array with (",") but output is a pretty non readable. So after saving query will be so simple to get account documents but this part for me is "harder" one.

Thanks a lot.

2

Hello @31d4r, your question is very wide, and I won't be able to give a ready-made solution.

My recommendation is to clear up your mind about the database schema, and how it helps implementing one-to-many and many-to-many relationships between database tables. I do recommend using the bold words in Google searches.

With a one-to-many relationship, you'd describe a hierarchical structure, like a file system, where an account contains several documents, and one document can only be found in a single account (I believe this is your goal). But with a many-to-many relationship, you'd describe a complex graph, where documents can be found in several accounts.

In your message, it looks like you are building a many-to-many relationship between accounts and documents with the accountDocument table. And later you describe what looks like a one-to-many relationship with the document_ids array.

This is confusing. For you, but also for the reader ;-) Picking the wrong relationship would engrave this confusion in your app, and lead to bugs.

When the topic is more clear in your mind, you'll better grasp how GRDB Associations can help.

Courage, hang on, Elgar :-)

1 Like