Returning All Reviewed Places

Ok so we are going to build up to the following query

await db
  .selectDistinct({
    id: places.id,
    name: places.name,
  })
  .from(places)
  .innerJoin(reviews, eq(places.id, reviews.placeId));

First our objective is to get all the places that have been reviewed. First lets just get all the places from our database. We can do this by just selecting from places

await db
  .select({
    id: places.id,
  })
  .from(places);

lets integrate this within our actual query and see what it returns

getAllReviewedPlaces: publicProcedure.query(async () => {
  const reviewedPlaces = await db.select({
    id: places.id
  }).from(places)

  return reviewedPlaces;
}),

Keep in mind we have turned our function into an async function by using the keyword on line 1 at the start of the arrow function definition. Asynchronous functions are functions that run in a non-blocking way, meaning they return a promise and allow the program to continue executing other code while waiting for long-running tasks (like database queries or API calls) to finish.

Line 2-4 defines a variable that selects the id column from the places table. To give a little bit of context, the places variable does not come from nowhere. It comes from a schema we have defined in a specific file and we have imported tables from that schema

import {
  places,
  placeSummary,
  types,
  placesToTypes,
  reviews,
} from '@/db/schema';

Drizzle is very smart and with certain commands makes sure our sql database follows this specific schema. If you are interested you can look at the specific schema by locating a file called core-schema in the EM codebase

We import the tables from this schema and in doing so we also know the expected types of each column in the table.

Line 7 returns the reviewed places. Keep in mind what we return is the data that is returned from the API endpoint we defined

Let us visit our endpoint again and see what is returned:

http://localhost:3000/api/trpc/place.getAllReviewedPlaces

{
  "result": {
    "data": [
      {
        "id": "1d0f0bc5-598f-44d3-bfe5-f346cad7a612"
      },
      {
        "id": "ad857070-184a-4591-9aa8-1e74fda97256"
      },
      {
        "id": "7400740f-1261-435a-a1ef-934ac5a78012"
      },
      ...
    ]
  }
}

Perfect! We have gotten the IDs of all the places. But hmm some places may not have a review within the places column.

Ok so this gets all the places instead of all the places that have been reviewed. How about we inner join with the reviewed table to get all the reviewed places?

getAllReviewedPlaces: publicProcedure.query(async () => {
  const reviewedPlaces = await db
    .select({
      id: places.id,
    })
    .from(places)
    .innerJoin(reviews, eq(places.id, reviews.placeId));

One final step though. This returns the same place multiple times. We need to just return the places distinctly. Finally lets also return the place.name as well.

getAllReviewedPlaces: publicProcedure.query(async () => {
  const reviewedPlaces = await db
    .selectDistinct({
      id: places.id,
      name: places.name,
    })
    .from(places)
    .innerJoin(reviews, eq(places.id, reviews.placeId));

  return reviewedPlaces;
}),

Let us see what is returned

{
  "result": {
    "data": [
      {
        "id": "10e42dcb-2b2d-4164-98ed-90cacc9bcecc",
        "name": "Dooboo Korean tofu hot pot"
      },
      {
        "id": "8a2656e0-660a-4317-a7b9-3f070fd6ae5c",
        "name": "Melbourne Cellar Door"
      },
      ...
    ]
  }
}

Perfect now we have created an API endpoint of places that have been reviewed.