Ecto and tricky joined tables

Elixir’s Ecto framework has a lot of quirks. For the first time, I wanted to try to sort on a table that I joined up to another table using a LEFT JOIN. Furthermore, these fields are in a JS Datatable, and I wanted to be able to dynamically sort the query based on what header the user clicks on.

I therefore needed to be able to switch the table in my ORDER BY clause. The original query was as follows:

query = from a in query,
     left_join: ag in assoc(a, :agents),
     order_by: [{:asc, ag.status}],
     select: {a, ag}

In this query, the joined dataset will always be sorted by the “status” column in the associated “agents” table. But what if I want to sort it by fields from either table dynamically? You can’t really select “a” or “ag” dynamically in this case, nor can you pass them to functions.

Ultimately, I came up with this solution that transforms the query object based on the field I want to sort. Given variables “col” and “dir” set earlier which are atoms representing the fields and direction of sort:

query = (from a in query,
   left_join: ag in assoc(a, :agents),
     select: {a, ag})
   |> sort_field(col, dir)

def sort_field(query, col, dir) do
   case col do
     :agent_status -> from [a, ag] in query, order_by: [{^dir, field(ag, ^:status)}]
     :agent_ttl ->    from [a, ag] in query, order_by: [{^dir, field(ag, ^:price)}]
     _ ->             from [a, ag] in query, order_by: [{^dir, field(a, ^col)}]

Now the two fields from the join table (agent_status and agent_ttl) will sort based on the “ag” table reference, and anything else will use the “a” table reference.

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *