Using JOIN in SQL queries in Flask

In this lesson, we'll learn how to write a query to generate a list of bookings or reservations in our hotel booking system. We'll also explore how to incorporate multiple joins in the query to gather information from different tables for a more in-depth result.

Setting up the query

To begin, let's head over to the index route in our code. Here, we have an empty list called bookings. This will eventually become the list of dictionaries that represents the results from the database.

First, we need to get the database and create a cursor:

db = get_db()
cursor = db.cursor(dictionary=True)

Now, let's close the cursor underneath the bookings variable:

cursor.close()

With everything set up, it's time to write the actual query.

cursor.execute("""
    SELECT
        booking.reference_number,
        guest.name,
        booking.check_in,
        booking.check_out,
        room_type.description,
        booking.airport_pickup_time,
        booking.breakfast
    FROM
        booking
    JOIN
        guest ON booking.guest_id = guest.id
    JOIN
        room_type ON booking.room_type_id = room_type.id;
""")

In the multi-line string provided above, notice that we first specify the columns needed to complete our list (which include reference_number, guest_name, check_in and check_out dates, room_type.description, airport_pickup_time, and breakfast_details), and then we write the JOIN statements.

Here, we are joining two tables: the guest and room_type tables. By joining the tables, we can access additional information about the guests who made a booking as well as the room type they booked.

Returning and displaying the results

Now that the query is in place, let's update the bookings variable below it:

bookings = cursor.fetchall()

The fetchall() function returns a list of dictionaries, which will then be passed to the template.

Let's now check if our list of reservations is generated as expected. Refresh the page in your browser or web-server to see the list of bookings.

Breaking down the template

To understand how the generated list is displayed on the page, let's look at an example in the list template. The template includes a loop that goes through the bookings variable.

Each item in the loop represents a row in the database, which is a dictionary. The template then references the different columns using the item:

{% for item in bookings %}
<tr>
    <td>{{ item.reference_number }}</td>
    <td>{{ item.name }}</td>
    <td>{{ item.check_in }}</td>
    <td>{{ item.check_out }}</td>
    <td>{{ item.description }}</td>
    <td>{% if item.breakfast %}Yes{% else %}No{% endif %}</td>
    <td>{{ item.airport_pickup_time or '' }}</td>
</tr>
{% endfor %}

For the breakfast field, we use an if statement to display Yes if the value is true and No if the value is false. For the airport_pickup_time, we display the time if there is a value, and nothing if it's empty.

Wrapping up

And that's it for the index page, or the home page of the hotel booking system! In the next part, we will work on the route that allows us to create new bookings in our system and save them to the database.