Next Steps and Required Homework

Now its time for you to do some work. Look at the core-schema of our database located in the file core-schema.ts. You can find this file using the command ctrl+p (windows) or cmd+p (mac) then type core-schema.

Also if you run

pnpm run db:studio

And go to this URL

https://local.drizzle.studio/

You can look and visualise the tables of your local database.

warning

You will need to perform your own queries and joins so if you get stuck reference the schema

To make things a little easier i will provide the code here as well for the schema:

import {
  timestamp,
  pgTable,
  text,
  varchar,
  numeric,
  integer,
  pgEnum,
  uuid,
  boolean,
  real,
  geometry,
  index,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { users } from './auth-schema';

// Places Table
export const places = pgTable(
  'places',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    googlePlaceId: varchar('google_place_id', { length: 255 }).notNull(),
    primaryType: varchar('primary_type', { length: 255 }),
    lat: numeric('lat'),
    lng: numeric('lng'),
    location: geometry('location', {
      type: 'point',
      mode: 'xy',
      srid: 4326,
    }).notNull(),
    name: varchar('name', { length: 255 }).notNull(),
    address: text('address').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP(3)`)
      .notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP(3)`)
      .notNull(),
  },
  (t) => ({
    spatialIndex: index('spatial_index').using('gist', t.location),
  })
);

// Enums for Reviews
export const responseEnum = pgEnum('response', ['yes', 'no', 'unsure']);
export const stepsEnum = pgEnum('steps', ['0', '1', '2', '3', '4+']);

// Photos Table
export const photos = pgTable('photos', {
  id: uuid('id').defaultRandom().primaryKey(),
  reviewId: uuid('review_id')
    .notNull()
    .references(() => reviews.id),
  photo: text('photo').notNull(),
});

// Reviews Table
export const reviews = pgTable('reviews', {
  id: uuid('id').defaultRandom().primaryKey(),
  placeId: uuid('place_id')
    .notNull()
    .references(() => places.id),
  title: text('title').notNull(),
  rating: integer('rating').notNull(),
  stepsOnEntry: stepsEnum('steps_on_entry').notNull(),
  stableRamp: responseEnum('stable_ramp').notNull(),
  acsBathroom: responseEnum('acs_bathroom').notNull(),
  acsSeating: responseEnum('acs_seating').notNull(),
  acsParking: responseEnum('acs_parking').notNull(),
  comment: text('comment'),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  authorId: text('author_id')
    .notNull()
    .references(() => users.id),
});

export const types = pgTable('types', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
});

export const placesToTypes = pgTable('places_to_types', {
  id: uuid('id').defaultRandom().primaryKey(),
  placeId: uuid('place_id')
    .notNull()
    .references(() => places.id),
  typeId: uuid('type_id')
    .notNull()
    .references(() => types.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
});

export const selfReportedReviews = pgTable('self_reported', {
  id: uuid('id').defaultRandom().primaryKey(),
  placeId: uuid('place_id')
    .notNull()
    .references(() => places.id),
  stepFree: boolean('step_free').notNull(),
  acsBathroom: boolean('acs_bathroom').notNull(),
  acsSeating: boolean('acs_seating').notNull(),
  acsParking: boolean('acs_parking').notNull(),
  pathwayInfo: text('pathwaysInfo'),
  transportInfo: text('transportInfo'),
  toiletInfo: text('toiletInfo'),
  parkingInfo: text('parkingInfo'),
  otherInfo: text('otherInfo'),
  pathwayPhotos: text('pathwayPhotos').array(),
  toiletPhotos: text('toiletPhotos').array(),
  transportPhotos: text('transportPhotos').array(),
  parkingPhotos: text('parkingPhotos').array(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
});

export const promotedPlaces = pgTable('promoted_places', {
  id: uuid('id').defaultRandom().primaryKey(),
  placeId: uuid('place_id')
    .notNull()
    .references(() => places.id),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
});

export const verifiedPlaces = pgTable('verified_places', {
  id: uuid('id').defaultRandom().primaryKey(),
  placeId: uuid('place_id')
    .notNull()
    .references(() => places.id),
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .notNull(),
});

Ok now for tasks:

1. Change the allPlaces page

I want you to make the allPlaces page prettier and display more data. I want you to try display the data as a shadcn table.

I also want you to be creative and make the page uniquely yours. Feel free to add or augment the query in any way you wish. But you are required to add the address of the place.

shadcn Data Table

2. Make a new query to find all reviews made in a certain date range

Ok so time for some independent learning.

Look at place.ts or the relevant tRPC documentation to see how to make a query with inputs as well. I want you to take two inputs, the start and the end date of the date range.

Next output all the reviews corresponding to that date range. Make a new page called reviewsByDate. That finds all the reviews in a specific date range. For now just choose an arbitrary range to test if your function works. places-query-provider.tsx is also where I access the place.searchPlacesByBounds endpoint - which has quite a complicated input.

If you want to be extra funky - add front end functionality to allow users to search by a specific date range - like an input and a search button

3. Make a brand new query

Ok so now just make a query based on anything you like. Look at the relevant schemas for inspiration. It needs to output some sort of data that you believe will be relevant to the team. Be creative. Make a new page as well for this similar to what i showed you

4. Provide Documentation

MAKE A GOOGLE DOC. Outlining exactly what you learnt and share it with me. It doesnt have to be too comprehensive. This is more for your sake to look back on. 200-300 words. PLEASE DO NOT CHAT IT

warning

Feel free to use AI tools but always understand your code. I will be asking you questions on this in the workshop so be prepared

Next week I will be looking at all your branches. Please come prepared. Will be looking at how to create protected and private routes and integrate it within the admin dashboard. Furthermore, will be looking at how to better present our data to make it more useable

1. Change the allPlaces page​

2. Make a new query to find all reviews made in a certain date range​

3. Make a brand new query​

4. Provide Documentation​

1. Change the allPlaces page

2. Make a new query to find all reviews made in a certain date range

3. Make a brand new query

4. Provide Documentation