Complex Queries

Queries with complications

We want to provide an order history that we’ll display on each menu item show page. We’ll have to start by adding some fields to our GraphQL schema to connect menu items over to the orders. We’ll also need to make a few tweaks at the database and context level to make pulling the data out easier. Not only will it give us the ability to show this information on the show page, but we’ll also suddenly have the ability to display a subset of the information back on the index page. We’ll also set up everything we need for the next chapter.

Connecting items to orders

Let’s start by thinking about how we want to get the orders for a menuItem, because the database design we came up with in Going Live with Subscriptions does not make that quite as easy as we’d like. As we can recall, when an order is placed, a snapshot of the orders is taken at the time of ordering. This is so that any future price or name changes to the menu won’t affect our historical record. It also gives us an opportunity to experiment with how embedded schemas work with GraphQL.

However, this means that the snapshots don’t reference menu items by ID but rather by name, and snapshots are inside a JSONB column on the orders’ table. If this were a production system, we’d probably normalize this into a significantly more complicated setup with half a dozen join tables. However, for our purposes, we can do something a bit simpler.

In the code provided, we’ll find a database migration that creates a PostgreSQL view. A view is a table powered by an SQL query. We can treat it exactly like a table from within Ecto. This is the perfect way to expose an Elixir friendly interface for certain database operations that might otherwise be less ergonomic. We’ve included the code that creates this database view for reference here: