# Phoenix Datatables
Phoenix Datatables provides support to quickly build an implementation
of the [DataTables](https://datatables.net/) server-side API in your [Phoenix Framework](http://phoenixframework.org/) application.
Add this to your `mix.exs` dependency list:
```elixir
{:phoenix_datatables, "~> 0.4.0"}
```
There is a complete [example](https://github.com/smartmetals/phoenix_datatables/tree/master/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`.
```elixir
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.
```elixir
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.
```elixir
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.
```elixir
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.
```elixir
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](https://datatables.net/manual/server-side).
`package.json`
```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`
```html
<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`
```javascript
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:
```elixir
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:
```elixir
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:
```javascript
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.:
```elixir
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