I have a database with multiple tables that frequently need to be queried with LEFT JOIN so that results contain aggregated data from other tables. Snippet from my Prisma schema:
model posts {
id Int @id @unique @default(autoincrement())
user_id Int
movie_id Int @unique
title String @db.Text
description String? @db.Text
tags Json?
created_at DateTime @default(now()) @db.DateTime(0)
image String? @default("https://picsum.photos/400/600/?blur=10") @db.VarChar(256)
year Int
submitted_by String @db.Text
tmdb_rating Decimal? @default(0.0) @db.Decimal(3, 1)
tmdb_rating_count Int? @default(0)
}
model ratings {
id Int @unique @default(autoincrement()) @db.UnsignedInt
entry_id Int @db.UnsignedInt
user_id Int @db.UnsignedInt
rating Int @default(0) @db.UnsignedTinyInt
created_at DateTime @default(now()) @db.DateTime(0)
updated_at DateTime? @db.DateTime(0)
@@id([entry_id, user_id])
}
If I wanted to return the average rating when querying posts, I could use a query like:
SELECT
p.*, ROUND(AVG(rt.rating), 1) AS user_rating
FROM
posts AS p
LEFT JOIN
ratings AS rt ON rt.entry_id = p.id
GROUP BY p.id;
I'm not exactly sure how/whether I can achieve something similar with Prisma, because as it stands right now, it seems like this would require two separate queries, which isn't optimal because there is sometimes the need for 2 or 3 joins or SELECTs from other tables.
How can I make a query/model/something in Prisma to achieve the above?
Yes, this is possible with Prisma!. For making this work, you need to specify on your "schema.prisma" file how are models related with each other. That way, code generation will set the possible queries/operations.
Change it to this:
model Post {
id Int @id @unique @default(autoincrement()) @map("id")
userId Int @map("user_id")
movieId Int @unique @map("movie_id")
title String @map("title") @db.Text
description String? @map("description") @db.Text
tags Json? @map("tags")
createdAt DateTime @default(now()) @map("created_at") @db.DateTime(0)
image String? @default("https://picsum.photos/400/600/?blur=10") @map("image") @db.VarChar(256)
year Int @map("year")
submittedBy String @map("submitted_by") @db.Text
tmdbRating Decimal? @default(0.0) @map("tmdb_rating") @db.Decimal(3, 1)
tmdbRatingCount Int? @default(0) @map("tmdb_rating_count")
ratings Rating[]
@@map("posts")
}
model Rating {
id Int @unique @default(autoincrement()) @map("id") @db.UnsignedInt
userId Int @map("user_id") @db.UnsignedInt
rating Int @default(0) @map("rating") @db.UnsignedTinyInt
entryId Int
entry Post @relation(fields: [entryId], references: [id])
createdAt DateTime @default(now()) @map("created_a") @db.DateTime(0)
updatedAt DateTime? @map("updated_a") @db.DateTime(0)
@@id([entryId, userId])
@@map("ratings")
}
Note: Please follow the naming conventions (singular form, PascalCase). I made those changes for you at the schema above. @@map allows you to set the name you use on your db tables.
Then, after generating the client, you will get access to the relational operations.
// All posts with ratings data
const postsWithRatings = await prisma.post.findMany({
include: {
// Here you can keep including data from other models
ratings: true
},
// you can also "select" specific properties
});
// Calculate on your API
const ratedPosts = postsWithRatings.map( post => {
const ratingsCount = post.ratings.length;
const ratingsTotal = post.ratings.reduce((acc, b) => acc + b.rating, 0)
return {
...post,
userRating: ratingsTotal / ratingsCount
}
})
// OR...
// Get avg from db
const averages = await prisma.rating.groupBy({
by: ["entryId"],
_avg: {
rating: true
},
orderBy: {
entryId: "desc"
}
})
// Get just posts
const posts = await prisma.post.findMany({
orderBy: {
id: "desc"
}
});
// then match the ratings with posts
const mappedRatings = posts.map( (post, idx) => {
return {
...post,
userRating: averages[idx]._avg.rating
}
})
You could also create a class with a method for making this easier. But I strongly recommend you to implement GraphQL on your API. That way, you can add a virtual field inside your post type. Any time a post is requested alone or in a list, the average will be calculated. In that same way, you would have the flexibility to request data from other models and the "JOINS" will get handled for you automatically.
Last but not least, if you ever want to do a lot of queries at the same time, you can take advantage of the Prisma transactions.