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

Download the code here

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>Listing bookings</h2>

<table class="table">
  <thead>
    <tr>
      <th>Pickup address</th>
      <th>Drop off address</th>
      <th>Status</th>
    </tr>
  </thead>
  <tbody>
<%= for booking <- @bookings do %>
    <tr>
      <td><%= booking.pickup_address %></td>
      <td><%= booking.dropoff_address %></td>
      <td><%= booking.status %></td>
    </tr>
<% end %>
  </tbody>
</table>

Adding association classes

During the lecture, 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:

Taxi allocation - Class diagram

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. Moreover, I have decided not to use Ecto’s macros one_to_many, one_to_one or many_to_many because, as I mentioned during the lecture, I find that they add some unnecessary complexity to the code (that is my personal opinion). You can find some examples of code using the aforementioned macros here, if you are interested in seeing them in action.

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.CreateAllocation 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 web/models/allocation.ex and copy there the following code.

defmodule Takso.Allocation do
  use Takso.Web, :model

  schema "allocations" do
    field :status, :string
    belongs_to :booking, Takso.Booking
    belongs_to :taxi, Takso.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
    user = conn.assigns.current_user

    struct = build_assoc(user, :bookings, Enum.map(booking_params, fn({key, value}) -> {String.to_atom(key), value} end))
    changeset = Booking.changeset(struct)
                |> Changeset.put_change(:status, "open")

    booking = Repo.insert!(changeset)

    query = from t in Taxi, where: t.status == "available", select: t
    available_taxis = Repo.all(query)
    if length(available_taxis) > 0 do
      taxi = List.first(available_taxis)

      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

      conn
      |> put_flash(:info, "Your taxi will arrive in 5 minutes")
      |> redirect(to: booking_path(conn, :index))
    else
      Booking.changeset(booking) |> Changeset.put_change(:status, "rejected")
      |> Repo.update

      conn
      |> put_flash(:error, "We apologize, we cannot serve your request in this moment")
      |> redirect(to: booking_path(conn, :index))
    end
  end

Wow … lots of changes! Let me try to explain little by little.

Let us first assume there is at least one taxi available by the time a booking request arrives. In the code above, you will see that we keep a reference to the booking just after inserting the information to the database:

    booking = Repo.insert!(changeset)

also, 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

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
    query = from t in Taxi,
            join: a in Allocation, on: t.id == a.taxi_id,
            group_by: t.username,
            where: a.status == "accepted",
            select: {t.username, count(a.id)}
    render conn, "summary.html", tuples: Repo.all(query)
  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.