Phoenix Datatables

Phoenix Datatables provides support to quickly build an implementation of the DataTables server-side API in your Phoenix Framework application.

Add this to your mix.exs dependency list:

{:phoenix_datatables, "~> 0.4.0"}

There is a complete example.

A full solution would typically use or include the following components:

• A repository helper function to execute queries with client params
• An Ecto query (Schema or other queryable object)
• A Context module function which takes the request parameters from the browser and invokes the repository helper along with the query to fetch the results and build a response structure
• A controller module and approprate route entries which will receive the json API requests from the browser.
• A view function to format the JSON data according to the requirements of the client.
• HTML and Javascript on the client configured per the Datatables library requirements.

The below example is one way to compose your application but there are others; while most needs can probably be met just using the Repo.fetch_datatable function, public functions are documented which can be used to retain more control in your application.

Repository

You can optionally use PhoenixDatatables.Repo. This creates a helper function Repo.fetch_datatable.

  defmodule PhoenixDatatablesExample.Repo do
    use Ecto.Repo, otp_app: :phoenix_datatables_example
    use PhoenixDatatables.Repo
  end

Context

It is recommended to follow Phoenix 1.3 conventions and place the query and repository invocations in a function in an application context module.

  def PhoenixDatatablesExample.Stock do
    import Ecto.Query, warn: false
    alias PhoenixDatatablesExample.Repo
    alias PhoenixDatatablesExample.Stock.Item

    def datatable_items(params) do
      Repo.fetch_datatable(Item, params)
    end
  end

Controller

The controller is like any other Phoenix json controller - the raw params request from Datatables needs to be passed to the datatables context function and the output sent to the view for rendering as json. Typically the routing entry would be setup under the :api scope.

  defmodule PhoenixDatatablesExampleWeb.ItemTableController do
    use PhoenixDatatablesExampleWeb, :controller
    alias PhoenixDatatablesExample.Stock

    action_fallback PhoenixDatatablesExampleWeb.FallbackController

    def index(conn, params) do
      render(conn, :index, payload: Stock.datatable_items(params))
    end
  end

  #router.ex

  scope "/api", PhoenixDatatablesExampleWeb do
    pipe_through :api

    get "/items", ItemTableController, :index
  end

View

As with any Phoenix json method, a loaded Ecto schema cannot be serialized directly to json by Poison. There are two solutions: Either the Ecto query needs to use a select to return a plain map, e.g.

from item in Item,
  select: %{
    nsn: item.nsn,
    rep_office: item.rep_office,
    ...
  }

Or a map function is required to transform the results in the view. This is preferred if other transformations are also required.

def render("index.json", %{payload: payload}) do
  PhoenixDatatables.map_payload(payload, &item_json/1)
end

def item_json(item) do
  %{
    nsn: item.nsn,
    rep_office: item.rep_office,
    common_name: item.common_name,
    description: item.description,
    price: item.price,
    ui: item.ui,
    aac: item.aac
  }
end

Client

The client uses jQuery and datatables.net packages; those need to be in your package.json.

A very basic client implementation might look something like the below - what is most important is that serverSide: true is set and the ajax: option is set to the correct route based on your entry in router.ex.

There are many, many options that can be set and various hooks into the request/response lifecycle that can be used to customize rendering and enable various features - please refer to the excellent manual, references and community content available throught the DataTables website.

package.json

  "dependencies": {
    "jquery": "^3.2.1",
    "datatables.net": "^1.10.15",
    "datatables.net-dt": "^1.10.15"
  },

  "devDependencies": {
    "copycat-brunch": "^1.1.0"
  }

index.html.eex

<table data-datatable-server class="table">
  <thead>
    <tr>
      <th>Nsn</th>
      <th>Rep office</th>
      <th>Common name</th>
      <th>Description</th>
      <th>Price</th>
      <th>Ui</th>
      <th>Aac</th>
    </tr>
  </thead>
</table>

app.js

import $ from 'jquery';
import dt from 'datatables.net';

$(document).ready(() => {
  dt();
  $('[data-datatable-server]').dataTable({
    serverSide: true,
    ajax: 'api/items',
    columns: [
      { data: "nsn" },
      { data: "rep_office" },
      { data: "common_name" },
      { data: "description" },
      { data: "price" },
      { data: "ui" },
      { data: "aac" }
    ]
  };
};

Joins

Ecto queryables using joins are supported with automatic introspection - meaning columns used in the DataTable will be sortable and searchable if they are specified appropriately in the client-side configuration. The example/ project in the source repo works this way.

Assuming an Ecto queryable that looks like:

    query =
      from item in Item,
      join: category in assoc(item, :category),
      join: unit in assoc(item, :unit),
      preload: [category: category, unit: unit]

And a view transformation that looks like:

  def item_json(item) do
    %{
      nsn: item.nsn,
      rep_office: item.rep_office,
      common_name: item.common_name,
      description: item.description,
      price: item.price,
      ui: item.ui,
      aac: item.aac,
      unit_description: item.unit.description,
      category_name: item.category.name,
    }
  end

Could be used with a client-side configuration that looks like:

      columns: [
        { data: "nsn" },
        { data: "category_name", name: "category.name"},
        { data: "common_name" },
        { data: "description" },
        { data: "price" },
        { data: "unit_description", name: "unit.description" },
        { data: "aac" },
      ]
    });

You'll notice this differs from the basic configuration in that a name attribute is specified with the qualified column name. You could alternatively use the value unit.description in the data attribute and not supply a name attribute, but then the DataTables client library will expect to find a nested structure in the response message, so your view would have to nest it e.g.:

  def item_json(item) do
    %{
      nsn: item.nsn,
      rep_office: item.rep_office,
      common_name: item.common_name,
      description: item.description,
      price: item.price,
      ui: item.ui,
      aac: item.aac,
      unit: %{
        description: item.unit.description
      },
      category: %{
        name: item.category.name
      },
    }
  end

The important thing to understand is that when a name attribute is supplied, the server uses that to identify the field to search / sort - regardless of the value of the data attribute. The client library always uses data to identify the data path to map the response into the generated HTML. The client uses the name attribute only to make it easier to refer to columns by name in scripts using the client API.

Credits

Libraries which provided inspiration and some code include:

• scrivener_ecto: https://github.com/drewolson/scrivener_ecto

• ex_sieve: https://github.com/valyukov/ex_sieve