Note, there are the QueryBuilder and filtery and other libraries, which may be of interest. I saw one recently in ElixirWeekly or Elixir Radar that is quite similar to how I do it but alas I don’t remember its name.
Mine isn’t prefect and has inherited some things from a JS query builder I was using previously. The filter is turned into a dynamic query in a method similar to this. There is a lot of code in here but it is mostly one line functions handling each type of field/operator/field type for the dynamic query. My filter is structured slightly differently, like below, which I lifted from the JS library I was using for this purpose. This is a different filter to the one in my query params below. I have an over-arching filter in this style plus individual column filters. The column filters are merged into this one later.
%{
"condition" => "or",
"rules" => [
%{"field" => "pants", "operator" => "eq", "value" => "chinos"},
%{"condition" => "and", "rules" => [...]
]
}
For the column filters, I designate fields as either an options
, text
or number
field and filter on each appropriately, i.e. different operators are available and a different dropdown is displayed.
Assign the base query on first mount:
def mount(_params, _session, socket) do
{:ok, assign_new(socket, :base_query, Thing.base_query()}
# `base_query` can be as simple as `Thing` or `from t in Thing, ...`
end
Keep everything in the query params and do the work in handle_params
.
def handle_params(params, _uri, socket) do
socket =
socket
|> assign(:params, parse_params(params)
|> assign_overarching_filter()
|> assign_filters()
|> assign_query()
|> assign_things() # hits the db
{:noreply, socket}
end
defp assign_filters(socket) do
%{"filters" => filters} = socket.assigns.params
filters = Map.take(filters, @columns)
filters =
Enum.reduce(filters, %{}, fn
{field, value}, acc ->
field = String.to_existing_atom(field)
if Filters.field_filter_type(field) == :options and is_list(value) and @blanks_category_name in value do
Map.put(acc, field, [nil | Enum.reject(value, fn v -> v == @blanks_category_name end)])
else
Map.put(acc, field, value)
end
end)
assign(socket, :filters, filters)
end
defp assign_query(socket) do
%{
date: date,
filters: filters,
selected_columns: selected_columns
} = socket.assigns
range = ...
query =
Thing.base()
|> Thing.apply_filters(filters)
|> Thing.in_time_range(range)
|> Alarm.select_map(selected_columns)
assign(socket, :query, query)
end
def apply_filters(query \\ Thing, filters)
def apply_filters(query, filters) when filters == %{}, do: query
def apply_filters(query, filters) do
# morph our filters map into the form returned by the old query designer
filters = parse_filters(filters)
query
|> Ecto.Query.exclude(:where)
|> add_filter_conditions(filters)
end
def parse_filters(filters) do
%{
"condition" => "and",
"rules" => []
}
|> maybe_add_datetime_filter("time", filters[:time])
|> maybe_add_text_filter("name", filters[:name])
|> maybe_add_options_filter("size", filters[:size])
|> maybe_add_number_filter("weight", filters[:weight])
...
end
...
# at some point I handle the wildcards
defp maybe_add_text_filter(filters, field, value) do
rule = %{"field" => field, "value" => value, "operator" => "ilike"}
update_in(filters["rules"], &[rule | &1])
end
...
Then I have these event handlers for each filter type.
def handle_event("apply_text_filter", %{"field" => field, "value" => value}, socket) do
filters_params = Map.put(socket.assigns[:params]["filters"] || %{}, field, value)
{:noreply, patch(socket, %{"filters" => filters_params})}
end
def handle_event("clear_text_filter", %{"field" => field}, %{assigns: %{params: %{"filters" => filters}}} = socket)
when is_map_key(filters, field) do
{:noreply, patch(socket, %{"filters" => Map.delete(filters, field)})}
end
def handle_event("clear_text_filter", _params, socket) do
{:noreply, socket}
end
Options:
Text:
EDIT: there is also anntel’s utils