Polling Webs App - Adding Results & Getting Options with a Poll

Unfortunately I made a rather simple mistake when requesting a poll by its id. Looking back I should have noticed it sooner, by mainly testing things I some times won't test it or it will simply slip my made to do so. Regardless all you can hope for is to catch them early before they become greater issues in the future.

The process of making my polling app continues, when you get a poll you might also want the options for it. Separately you might also like to have results for a poll. While it would be easy to store the results along side their options, I am experimenting to see how things will go if they were split from each other.

Fixing Issues with Polls GET Request

While testing "/single-poll" two issues were discovered, those being you can't use request.body with a GET request and the type for "getPollsById" cause issues when changing to query strings. The first fix is fairly simple, while still destructing for "pollID" change the req.body to req.query. This change however causes issues with the "getPollsById" function as its wants to take in a string, but gets a Query String instead. To address this conflict simply changing the type in "getPollsById" from "string" to "any" resolves this.

Returning Poll Options with Getting Polls

Collecting Poll Options

To collect all options for a specific poll, a new service file, "getOptions.ts", was added to the services/options directory. Within the file, similar to "getPolls.ts", import "eq" from "drizzle-orm", "db" from "drizzle.ts", and the matching schema. Make a new export function named "getAllPollOptions" that takes "pollID" as an argument with a type of "any". In the function have a database query that does the following, selects the "id" and "option" fields, uses the options table, and has the options.pollId is equal to the "pollID". Store the query in a const and return the const.

import { eq } from "drizzle-orm";
import { db } from "../../db/drizzle";
import { options } from "../../db/schema";

export const getAllPollOptions = async (pollID: any) => {
	const pollOptions = await db
    	.select({
          id: options.id,
          option: options.option
        })
        .from(options)
        .where(eq(options.pollId, pollID))

	return pollOptions
}

Getting A Poll Also Gets Their Options

Now that there is something to collect the options for a poll, update the "getPollsById" to also respond with the options for that poll. In the "getPoll.ts" import the "getAllPollOptions" function. Add an empty Typescript Object named "results" within "getPollsById". Next store the database query for polls in a const named "poll". After that have a const name "options" that calls the "getAllPollOptions" using the "poll" id const as an argument. Have result now equal "poll" and "options", lastly return results.

export const getPollsById = async (pollID: any) => {
	let result = {}

	const poll = await db
		.select()
		.from(polls)
		.where(eq(polls.id, pollID))

	const options = await getAllPollOptions(poll[0].id)

	result = {poll, options}

	return result
}

Poll Results

Creating the Results Schema

For the third schema for the polling application, a new "results" scheme is added to "schema.ts" it will track a poll's outcome. Like the other schemas, it uses the "pgTable" function with the tables naming being "results". The results table is composed of four parts. First the "id", similar to the others it is a generated uuid. Next the "total" is an integer that will store the total votes for an option. There will be two foreign keys used, one for pollId and the other for optionsId. Both foreign keys will be configured with cascading deletes, that automatically removing related results when a poll or its options are deleted.

export const results = pgTable('results', {
	id: uuid('id')
          .default(sql`gen_random_uuid()`)
          .primaryKey(),
	total: integer('total'),
	optionId: uuid('option')
          .references(() => options.id, {onDelete: 'cascade'})
          .notNull(),
	pollId: uuid('poll_id')
          .references(() => polls.id, {onDelete: 'cascade'})
          .notNull()
});

Returning the Option's ID

As the "results" schema requires the "optionId" there needs to be some changes done to "addOption". To guarantee that results will be made the "addOption" needs to store an "id" as a const when it is inserting an option into the database. The function takes that const value and returns it.

export const addOption = async (option: string, pollID: string) => {
	const id = await db
    	.insert(options)
    	.values({
    		option,
    		pollId: pollID
    	})
    	.returning({ id: options.id })
					
	return id
}

Make a Results Entry

Finishing the setup for the "results" schema, create a new directory in the "services" directory for "results". Within this directory, make a file called "addResults.ts" to handle adding results to a database. Import the "db" from "drizzle.ts" and "results" from "schema.ts". A new export function called "addResult" is added to the file. The function takes two arguments the first being "optionID" and the other being "pollID", both with a type of any. For the moment it simply calls the db function to insert into the database with values of the option id, poll id, and setting the total to zero.

import { db } from "../../db/drizzle";
import { results } from "../../db/schema";

export const addResult = async (optionID: any, pollID: any) => {
	await db
	.insert(results)
	.values({
		total: 0,
		optionId: optionID,
		pollId: pollID
	})
}

Modify Create Polls To Create Results As Well

To make sure that results are being made the fastest way to incorporate it into the existing code is to import it in "addPoll.ts". While the for loop is making options change the function to store the return id in a let variable. Then call the "addResult" function with the poll id and option id as its arguments.

import { addResult } from "../results/addResults";

export const addPoll = async (question: string, options: any) => {
	const poll = await db
			.insert(polls)
			.values({
				question
			})
			.returning({ id: polls.id })

	for (const value in options) {
		let option = await addOption(options[value], poll[0].id)
		addResult(option[0].id, poll[0].id)
	}

	return poll[0].id

Conclusion

As I continue to make this polling app, I will continue to make both minor and major issues I hope to catch them before I post about it, but that is not something I can guarantee. For the moment I am happy with the solution, but I might come back to it and try some other solutions out.

The schemas for polls, options, and results should be enough for the time being, to setup most of the basic functionality for the polling app. That doesn't mean I will not have additional schemas in the future, but I will either be modifying the existing three or leave them as is. Speaking on basic functionality, I have now added to the app the ability to collect options and create results.

If you would like to check out the code or us it for yourself: Github Link

If this blog was helpful to you please consider subscribing.