Prepare and execute a provided query, modified based on the params map. with the results returned in a Payload which can be encoded to json by Phoenix / Poison and consumed by the DataTables client.

## Options

• :columns - If columns are not provided, the list of valid columns to use for filtering and ordering is determined by introspection of the Ecto query, and the attributes and associations defined in the Schemas used in that query. This will not always work - Ecto queries may contain subqueries or schema-less queries. Such queryables will need to be accompanied by :columns options.

   

  Even if the queryable uses only schemas and joins built with assoc there are security reasons to provide a :columns option.

  The client will provide columns to use for filterng and searching in its request, but client input cannot be trusted. A denial of service attack could be constructed by requesting search against un-indexed fields on a large table for example. To harden your server you could limit the on the server-side the sorting and filtering possiblities by specifying the columns that should be available.

   

  A list of valid columns that are eligibile to be used for sorting and filtering can be passed in a nested keyword list, where the first keyword is the table name, and second is the column name and query binding order.

   

  In the below example, the query is a simple join using assoc and could be introspected. :columns are optional.

  In the example, columns is bound to such a list. Here the 0 means the nsn column belongs to the from table, and there is a category.name field, which is the first join table in the query. In the client datatables options, the column :data attribute should be set to nsn for the first column and category.name for the second.

   

• :total_entries - Provides a way for the application to use cached values for total_entries; when this is provided, phoenix_datatables won't do a query to get the total record count, instead using the provided value in the response. The mechanism for cacheing is left up to the application.

    query =
      (from item in Item,
      join: category in assoc(item, :category),
      select: %{id: item.id, item.nsn, category_name: category.name})
  
    options = [columns: [nsn: 0, category: [name: 1]], total_entries: 25]
  
    Repo.fetch_datatable(query, params, options)

   

• :nulls_last - When true, results will be sorted with NULL fields sorted last. This option is only valid with PostgreSQL.

Use the provided function to transform the records embeded in the Payload, often used in a json view for example to convert an Ecto schema to a plain map so it can be serialized by Poison.

 query
 |> Repo.fetch_datatable(params)
 |> PhoenixDatatables.map_payload(fn item -> %{
      nsn: item.nsn,
      category_name: item.category.name}
    end)