View Source Dynamic Queries With EEx

When AyeSQL syntax for dynamic query generation falls short, we can leverage EEx templates for generating them.

Note: It's not possible to use defqueries/1 and defqueries/3 macros when using EEx templates. We'll need to use the function AyeSQL.eval_query/2 instead. This function will generate an anonymous function with the query.

One of such cases is when the identifiers in the query are not constant (table names, aliases, etc). The following EEx template has a query with dynamic tables, aliases, fragments and even joins:

-- File: queries.sql.eex
<% [%{name: name, as: as} | rest] = @tables %>
SELECT
  <%= as %>.datetime AS datetime,
  <%= @calculation %> AS calculation
FROM
  <%= name %> AS <%= as %>
  <%= for table <- rest do %>
  INNER JOIN <%= table.name %> AS <%= table.as %>
    ON <%= table.as %>.datetime = <%= as %>.datetime
  <% end %>
WHERE
  <%= as %>.datetime BETWEEN :start_date AND :end_date

With the previous template, we can generate a valid AyeSQL query with the following:

iex> assigns = [
...>   calculation: "(a.value / b.value)",
...>   tables: [
...>     %{name: "table_a", as: "a"},
...>     %{name: "table_b", as: "b"}
...>    ]
...> ]
iex> dynamic_query =
...>   "queries.sql.eex"
...>   |> File.read!()
...>   |> EEx.eval_string(assigns: assigns)

so, the variable dynamic_query contains the following query:

SELECT
  a.datetime AS datetime,
  (a.value / b.value) AS calculation
FROM
  table_a AS a
  INNER JOIN table_b AS b
    ON b.datetime = a.datetime
WHERE
  a.datetime BETWEEN :start_date AND :end_date

This query is then compatible with AyeSQL, so we can compile it into a function using the following:

iex> function = AyeSQL.eval_query(dynamic_query)

And finally use the function as any other AyeSQL function:

iex> {:ok, start_date, _} = DateTime.from_iso8601("2022-04-17T00:00:00Z")
iex> function.([start_date: start_date, end_date: DateTime.utc_now()], repo: MyApp.Repo)
{:ok, [%{datetime: ..., calculation: ...}, ...]}