In this session, we will review the concepts of table associations and advanced query support.

Filtering information based on current user

It is often the case that we have to filter some information, before presenting it to the end user. For instance, if a customer wants to see the list of trips on the last month, we will just present the list of his/her trips and not those the other customers. Similarly, we will provide a summary of the rides to the taxi driver for invoicing at the end of each month, etc.

To illustrate this idea, we will add the action to list the history of bookings for the currently logged in customer. The following code would do what we need in the controller.

  def index(conn, _params) do
    bookings = Repo.all(from b in Booking, where: b.user_id == ^conn.assigns.current_user.id)
    render conn, "index.html", bookings: bookings
  end

Please note that we are using a query. Moreover, such query takes as input the identifier of the currently logged in user. Since that value is taken from outside of the scope of the query, we need to use character ^. In the documentation this is referred to as value interpolation inside ecto queries.

Use the following EEX template to complete the rendering of the list of bookings.

<h2>List of bookings</h2>

<.table id="bookings-list" rows={@bookings}>
  <:col :let={booking} label="Pickup Address">
    <%= booking.pickup_address %>
  </:col>
  <:col :let={booking} label="Dropoff Address">
    <%= booking.dropoff_address %>
  </:col>
  <:col :let={booking} label="Status">
    <%= booking.status %>
  </:col>
</.table>

Adding association classes

During the previous class, we learned how to connect two tables, by using table associations. In that example, we were enforcing the constraints: “One booking belongs to a single customer” and also “One customer may have many bookings”. Such kind of associations can be translated to foreign keys in the relational realm. There are some types of associations that are more complex than that and that deserve more attention, such as the one shown in the figure below:

As you can see, in the class diagram we have what is called an association class, namely the (taxi) Allocation class. The diagram above models the situation where STRS needs to find a taxi to serve a ride booking request: the system would select one taxi (e.g. based on the proximity taxi-pickup address), propose the ride to the driver and wait for his/her decision and, in case of rejection, repeat the procedure with another taxi. In fact, you will see that each of the classes above has an attribute status. Please note that the values for such attributes are different in each case. The status of a taxi may be: available, busy, invisible or off-duty. The status of a booking may be: open (while trying to find a taxi), accepted (when a taxi driver accepts it), rejected (when there is not taxi to serve the request) and cancelled (when the customer cancels the request). Finally, the status of a taxi allocation may be preallocated (a taxi driver has been selected), accepted, rejected and cancelled.

Implementing all the above is not trivial, so we are going to do it step by step. In fact, to simplify the things, we will assume for now that all the available taxis will unconditionally accept a taxi allocation.

By now, we have already two of the entities on our database schema (i.e. bookings and taxi). Hence, we only need to add the support for the association class (taxi) Allocation. Let us then create one migration and one model. Below, you will find the corresponding migration script:

defmodule Takso.Repo.Migrations.CreateAllocations do
  use Ecto.Migration

  def change do
    create table(:allocations) do
      add :status, :string
      add :booking_id, references(:bookings)
      add :taxi_id, references(:taxis)

      timestamps()
    end

    create unique_index(:allocations, [:booking_id, :taxi_id])
  end
end

Note that in the case of association classes, we have to include the reference to each one of the classes participating in the association. Moreover, to be more strict, we have instructed ecto to use the combination of booking_id and taxi_id as the primary key of this table. This makes sense when we think that we are not going to propose a taxi driver the same booking more than once.

Create the file takso/sales/allocation.ex and copy there the following code.

defmodule Takso.Sales.Allocation do
  use Ecto.Schema
  import Ecto.Changeset

  schema "allocations" do
    field :status, :string
    belongs_to :booking, Takso.Sales.Booking
    belongs_to :taxi, Takso.Sales.Taxi

    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:status])
    |> validate_required([:status])
  end
end

I think the code follows the conventions that we already understand. I will not develop on that module anymore.

Let us now focus on the changes that we need to make on function create/2 inside the module BookingController. Replace the current implementation with the code below.

  def create(conn, %{"booking" => booking_params}) do
    # Retrieve logged user (we assume there is one)
    user = conn.assigns.current_user
    # Build the association (with user) for the booking
    booking_assoc =
      Ecto.build_assoc(
        user,
        :bookings,
        Enum.map(booking_params, fn {key, value} -> {String.to_atom(key), value} end)
      )

    # Update state of the booking to "open"
    booking_changeset =
      Booking.changeset(booking_assoc, %{})
      |> Changeset.put_change(:status, "open")

    # Insert booking un the database
    case Repo.insert(booking_changeset) do
      {:ok, booking} ->
        # Success, query the database to retrieve the list of available taxis
        query = from(t in Taxi, where: t.status == "available", select: t)
        available_taxis = Repo.all(query)
        # If there are available taxis
        if length(available_taxis) > 0 do
          # Retrieve first taxi available
          taxi = List.first(available_taxis)
          # Update database and corresponding statuses
          Multi.new()
          |> Multi.insert(
            :allocation,
            Allocation.changeset(%Allocation{}, %{status: "accepted"})
            |> Changeset.put_change(:booking_id, booking.id)
            |> Changeset.put_change(:taxi_id, taxi.id)
          )
          |> Multi.update(
            :taxi,
            Taxi.changeset(taxi, %{})
            |> Changeset.put_change(:status, "busy")
          )
          |> Multi.update(
            :booking,
            Booking.changeset(booking, %{})
            |> Changeset.put_change(:status, "allocated")
          )
          |> Repo.transaction()

          # Redirect with success message
          conn
          |> put_flash(:info, "Your taxi will arrive in 15 minutes.")
          |> redirect(to: ~p"/bookings")
        else
          # Update state of the booking to "rejected"
          Booking.changeset(booking, %{})
          |> Changeset.put_change(:status, "rejected")
          |> Repo.update()

          # Redirect informing there are no taxis
          conn
          |> put_flash(:error, "We are sorry, but there are no taxis available, try again later.")
          |> redirect(to: ~p"/bookings")
        end

      {:error, %Ecto.Changeset{} = changeset} ->
        # Error trying to insert booking, report
        render(conn, "new.html", changeset: changeset)
    end
  end

Wow … lots of changes! Let me try to explain little by little. Please, don’t forget to add alias Ecto.{Changeset, Multi} and alias Takso.Sales.{Taxi, Booking, Allocation} at the beginning of the controller, otherwise you will get a compilation error.

Let us first assume there is at least one taxi available by the time a booking request arrives. For simplicity, we select the first taxi in the list of available taxis as follows:

    taxi = List.first(available_taxis)

With the information above, we can initialize a changeset for a (taxi) allocation as follows:

Allocation.changeset(%Allocation{}, %{status: "accepted"})
|> Changeset.put_change(:booking_id, booking.id)
|> Changeset.put_change(:taxi_id, taxi.id)

You can see that I decided not to build the association, but to use the function Changeset.put_change/3 to add explicitly the reference to the booking and to the taxi, one by one. (I did not include the booking nor taxi ids in the call to changeset, because they are not included in the list of casted values. They will be discarded for this reason.)

With the changeset above, we are ready to insert a row in the corresponding database table. However, in this case, the situation is a bit more complex: we need to ensure that the database is left consistent after three operations, i.e. the insertion of the (taxi) allocation and the changes of status for booking (status should be accepted) and taxi (status should be busy). As you should know, that requires a database transaction. In the code above, I have used Ecto’s Multi module (see its documentation here). Let us see the corresponding code below:

      Multi.new
      |> Multi.insert(:allocation, ...) # Allocation's changeset
      |> Multi.update(:taxi, ...)       # Taxi's changeset
      |> Multi.update(:booking, ...)    # Booking's changeset
      |> Repo.transaction               # Perform set of transations

As you can see, we create a Multi and then specify a sequence of database operations (e.g. insert, update, delete, etc.) which will be executed inside a single transaction. The atom that we use on each step is just for documentation purposes, a sort of name. Note that in my case, I inlined on each operation the creation of the corresponding changeset. I hope the code is still readable. You can for sure refactor it as you wish.

Querying the database

The last part of this session is devoted to querying with ecto. As we discussed in the previous week, Ecto provides a very convenient DSL for database querying which resembles a lot the syntax of SQL. The only thing that changes is the order of the keywords.

For demonstration purposes, we will assume that we need to show a summary of the number of booking requests accepted by each one of the taxis. A query of this sort would require joining two tables (i.e. taxis and allocations), grouping the rows by taxi (i.e. using the taxi’s username) and aggregating the information (i.e. using the aggregation function count). The syntax for such query is illustrated below:

  def summary(conn, _params) do
    # JOIN query with alias for the columns
    query =
      from(t in Taxi,
        join: a in Allocation,
        on: t.id == a.taxi_id,
        group_by: t.username,
        where: a.status == "accepted",
        select: %{driver: t.username, trips: count(a.id)}
      )
    summary = Repo.all(query)
    # Redirect to the summary page
    render(conn, "summary.html", tuples: summary)
  end

I decided to add the function above to our Bookings controller. To complete the example, I have to configure the routes and add the corresponding EEX template. I will leave you to write the template. Let me then illustrate the configuration of the routes here:

  scope "/", Takso do
    pipe_through :browser # Use the default browser stack

    get "/", PageController, :index
    resources "/users", UserController
    get "/bookings/summary", BookingController, :summary
    resources "/bookings", BookingController
    resources "/sessions", SessionController, only: [:new, :create, :delete]
  end

Note that I added the route before the already existing resources "/bookings" .... This is because I want to reuse the prefix /bookings. What happens if we put the line get "/bookings/summary" ... after the line resources "/bookings" ...?

I can imagine other sort of queries but my other examples require further information. For example, we could add the fares of all the served trips to compute a monthly invoice to the taxi driver. We could also compute the number of trips in a period of time for each customer, for instance to assign points within a fidelity program, etc.

That is all for this session, though.