Left Joins with Prisma and SQLite
00:00 All right, so let's add our image ID to our Zod schema first, and then we'll come down to our UI and account for that. So now it's no longer a image object that's on the user. That is a little bit trickier to do with raw SQL. We're just going to do it as a property on the result. And so it's going to be called image ID.
00:18 If I save that, we're going to get a parsing error because our query doesn't support the image ID yet. So now we're going to add our left join. We're going to left join the user table on the user ID being equal to our user image user ID.
00:35 So that's our condition for our left and right join here. We're doing a left join, so we're going to take everything on the left side, and then we'll take the things that exist on the right side if the user ID matches the user image user ID. So with that now, we have access to the user images
00:53 ID, which is what we need for displaying stuff. So I can't put just ID right here, though, because we have some ambiguity. We have the user table has an ID, and the user image table has an ID. And so to disambiguate, we're going to say user image dot ID,
01:11 and then I'm going to alias this to image ID. And then just to further disambiguate things, I'm also going to just bring some more clarity by saying user dot ID and user dot username and user dot name. And even in our where clause, we'll say user dot username and user dot name. And with that, we should be golden.
01:31 Let's save that, and ta-da, there it is. So we're in a good place. We've got our users, and our search all still works. We can search for Cody, and all of that is still going to work as it did before. So that's awesome. So here we updated our search schema so that we could get that image ID.
01:49 We updated our UI to use that as well. And then we updated our query to add a left join to the user image table on the user dot ID being equal to the user image dot user ID. And then we added the user image ID to our results and aliased that to disambiguate between the user's
02:09 ID and the image ID. And then we also added the user table prefix to all of our properties just to add additional clarity for us. And yeah, that is a left join with Prisma and SQLite.