Guidance on creating temporary views

[Pomanks]

Hi!

I'm trying to implement temporary views, following this article.

My current view's definition is based on this model:

@Table("singleReference")
public struct SingleReference: Codable, Hashable, Sendable {
    public var item: Item
    public var plant: Plant
    public var category: Category?
    public var scientificClassification: ScientificClassification
    public var family: Family?

Every property inside this model are real tables.

I'm defining my temporary view inside my database's configuration like so:

…
try SingleReference.createTemporaryView(
    as: Item
        .group(by: \.id)
        .join(Plant.all) {
            $0.plantID.eq($1.id)
        }
        .leftJoin(Category.all) {
            $1.categoryID.eq($2.id)
        }
        .join(ScientificClassification.all) {
            $3.plantID.eq($1.id)
        }
        .leftJoin(Family.all) {
            $3.familyID.eq($4.id)
        }
        .select { item, plant, category, scientificClassification, family in
            SingleReference.Columns(
                item: item,
                plant: plant,
                category: category,
                scientificClassification: scientificClassification,
                family: family
            )
        }
)
.execute(db)
…

When running my app, I an error starting with "no such table: singleReference".

My question is: am I able to "wrap" tables inside a database view, or can I only do so with simple properties (String, JSON objects, …)?
Or am I just overlooking something completely obvious from an external POV? 😅

[mbrandonw]

Hi @Pomanks, where are you installing the database view exactly? We don't go into detail in that article, but it should be installed in the prepareDatabase closure of your DB config because it needs to be installed in every connection (single writer and multiple readers).

My question is: am I able to "wrap" tables inside a database view, or can I only do so with simple properties (String, JSON objects, …)?

You can do any kind of select statement you want for a database view. Can be really wild stuff, there's no limits.

[Pomanks]

Ah! My bad then it was not installed in the prepareDatabase at all…! I wrongly assumed it should be installed like a trigger and implemented the call around the same place. Thanks for clarifying this part 😄

I'm glad anything could be done with database views, this should greatly simplify my current implementation then.

[mbrandonw]

I wrongly assumed it should be installed like a trigger and implemented the call around the same place.

Yeah, triggers only need to be installed in database.write because triggers are only useful in write transactions (and there's only one writer). But views are useful in reads and writes, and so need to be installed in every database connection.

Glad you got it working!

[Pomanks]

I better see the nuance now, thanks a lot for taking the time to clarify and explain this!