groue
commented
1 year ago Hello @ahartman,
Thank you for the sample code.
It contains one mistake that explains why books are not attached to their author:
try db.create(table: "author", ifNotExists: true) { t in
// 1️⃣ Author primary key is an integer
t.autoIncrementedPrimaryKey("id")
...
}
try db.create(table: "book", ifNotExists: true) { t in
...
// 2️⃣ The foreign key is text
t.column("authorID", .text)
.notNull()
.indexed()
.references("Author", onDelete: .cascade)
}The mismatch between integers and text explains why books are not attached to their author: GRDB can't find the author with id "1" (string) in a list of authors with ids 1, 2, etc (all integers).
This wasn't easy to spot 😅
The recommendation is to fix the database schema, and make sure the foreign key has the same type as its target primary key.
An easy way to do this is to create foreign keys with the belongsTo method. It would have prevented the original mistake in the first place:
try db.create(table: "book", ifNotExists: true) { t in
...
+ t.belongsTo("author", onDelete: .cascade)
+ .notNull()
- t.column("authorID", .text)
- .notNull()
- .indexed()
- .references("Author", onDelete: .cascade)
}
ahartman
What did you do?
I tried the example for including(all:) example from the documentation
What did you expect to happen?
I expected a list of authors with their books
What happened instead?
The list with authors had empty Book arrays for all autrors
Environment
GRDB flavor(s): master branch taken from github GRDB version: 6.20.2 Installation method: as package Xcode version: 14.2 Swift version: 5 Platform(s) running GRDB: macOS 12.7.2, Mac Catalyst macOS version running Xcode: macOS 12.7.2, Mac Catalyst
Demo Project
A demo project is not possible as my project reads events from my Calendar. I include one file DBModel.swift as TXT, the issues sits in the function getAuthorsBooks; that function returns authors but no books. I also have a function getBooksAuthors that does the reverse and fetches books with their associated author; that one works fine.
The db tracing shows a SQL statement, that is obviously wrong, as it does not pick up the primary key from the parent table.
SQL: COMMIT TRANSACTION SQL: PRAGMA query_only = 0 SQL: PRAGMA query_only = 1 SQL: BEGIN DEFERRED TRANSACTION DBModel.txt
SQL: SELECT FROM "author" SQL: SELECT , "authorID" AS "grdb_authorID" FROM "book" WHERE "authorID" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
As far as I can see I copied everything correctly from the examples in the documentation. I an average in Swift and know my way in SQL; getting GRDB to urn is really dense form e, so I hope someone can point out the error that I am missing.
Thanks in advance, ahartman, Belgium DBModel.txt