What is the standard way to add filtering, sorting, pagination, sorting to Phoenix LiveViews?

Hello community

The phx.gen.live utility gave me a great way to get started with basic CRUD funcionality.
And now, I am wondering if there is a standard way to now add other basic features to this view, namely filtering, sorting, searching and paginating the tabular view.

My cursory search stroll across the internet took me to the following:

So, pray tell, what in your opinion is the best way.

3 Likes

I have tables with 100,000’s of rows and a couple dozen columns. I keep a base_query in the assigns and apply whatever filters are set on the columns and the sorting then fetch the data, limited by how many rows will fit in the screen.

I don’t use pagination, as that would be absurd for this many rows. Instead I use nouiscroller as a scrollbar with time as the value (table contains events).

2 Likes

Is there a publicly available snippet I could see and learn from?

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

4 Likes

There is a little neat library for that called flop, you can check it out here: flop | Hex

It also has a phoenix package with some components already written to help you flop_phoenix | Hex

1 Like