max Posts - Answer Overflow

max

•Created by max on 2/20/2025 in #help-and-questions

Parameterized Views for postgres 🦄

This is the continuation but unrelated to this thread (not necessary to read) Simplified Schema:

previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}

previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}

Parameterized View We know this is a bit hacky but we don't see any otter viable cleaner solution that would provide the same benefits/power. If we request a person and include their working time, we have to specify for which range want the time calculated. Like for the length of March we need to know how much that person has worked and should work. So far we have done this with queryRawTyped but its inconvenient to manually having to merge it with persons afterwards and if we would include the person in the underlying select in the first place, we DO have the data together, but we don't have the flexibility to include other relations on the request dynamically and take full advantage of the entire PrismaArgs. Inside the SQL statements where we create the view we retrieve the variables like so:

WHERE date > current_setting(var.date_range_start)

WHERE date > current_setting(var.date_range_start)

We set this via prisma.$extends(withVariables(dateRange)).person.findMany({include: {workingTime: true}) And the extension does something like this:

return prisma.$transaction([
    prisma.$executeRaw`SELECT set_config('var.date_range_start', dateRange.start)`,
    query(args),
])

return prisma.$transaction([
    prisma.$executeRaw`SELECT set_config('var.date_range_start', dateRange.start)`,
    query(args),
])

In an ideal world we could pass the date-range from where we include, not in the super weird de-coupled way above. Any feedback on this would be appreciated. We're not sure if we're on the right track with this approach. Once We find a smoother way to input the parameters we might be set up for some very powerful Database stuff fully type safe through the ORM

7 replies

PPrisma

•Created by max on 2/20/2025 in #help-and-questions

Is joining views into models a recommended pattern? 🦄

We're a bit on the edge with preview features but we just discovered today how convenient it is to create Views and be able to reference and include them from other Models. Underlying we're using Postgres for context. Simplified Schema:

previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}

previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}

Working time represents the amount of hours the employee has worked and should work. We actually have it as a parameterized view that can react on input via client-extentions but more on that in another thread There is much more going on in our schema but basically WorkingTime is a fairly complex View that Joins over many tables, It in itself is very performant. Is it a good practice to use this setup a lot wherever Views are the most fitting solution? Or are there downsides to this that we've not yet noticed? Its pretty sexy because we can include this from anywhere fully type-safe and don't have to use queryRawTyped to fetch WorkingTime separately and then always manually merge persons and their corresponding working time afterwards. Is this a good approach or are there downsides? This Question continues in another thread due to the question being technically unrelated and the character limit

3 replies

PPrisma

•Created by max on 11/7/2024 in #help-and-questions

Soft delete & include

I recently impemented soft-delete with prisma ClientExtentions and it works wonderfully, but: When handling relational joins I cannot simply add a {where: {isDeleted: false}} to every include statement with a deep patch function, cause to-one relations dont have where. Is there a generic way to add a where statement to every include?

1 replies

Gaming

Programming