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:
json
API requests from the browser.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.
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
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
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
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
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" }
]
};
};
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.
Libraries which provided inspiration and some code include:
scrivener_ecto
: https://github.com/drewolson/scrivener_ecto
ex_sieve
: https://github.com/valyukov/ex_sieve