# DataTable - Julia Callbacks

## Backend Paging

With backend paging, we can load data into our table progressively. Instead of loading all of the data at once, we'll only load data as the user requests it when they click on the "previous" and "next" buttons.

Since backend paging integrates directly with your Dash callbacks, you can load your data from any Python data source.

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout =  dash_datatable(
      id="datatable-paging",
      columns=[
          Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
      ],
      page_current=0,
      page_size=PAGE_SIZE,
      page_action="custom"
    )

    callback!(app,
        Output("datatable-paging", "data"),
        Input("datatable-paging", "page_current"),
        Input("datatable-paging", "page_size")
        )  do page_current, page_size
        return Dict.(pairs.(eachrow(
          df[(page_current*page_size+1):((page_current+ 1)*page_size), :]
        )))

    end
    run_server(app, "0.0.0.0", debug=true)

index | continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | --- | ---
1 | Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
2 | Europe | Albania | 5937.029525999999 | 76.423 | 3600523
3 | Africa | Algeria | 6223.367465 | 72.301 | 33333216
4 | Africa | Angola | 4797.231267 | 42.731 | 12420476
5 | Americas | Argentina | 12779.37964 | 75.32 | 40301927

1

With backend paging, we can have front-end sorting and filtering but it will only filter and sort the data that exists on the page.

This should be avoided. Your users will expect that sorting and filtering is happening on the entire dataset and, with large pages, might not be aware that this is only occurring on the current page.

Instead, we recommend implementing sorting and filtering on the backend as well. That is, on the entire underlying dataset.

Note for returning users - changed property names:

- Sorted fields are now in `sort_by`, not `sorting_settings`
- The filter string is now in `filter`, not `filtering_settings`

## Backend Paging and Page Numbers

The pagination menu includes the number of the current page and the total page count. With native (i.e., frontend) pagination, the page count is calculated by the table. However, when using backend pagination, the data are served to the table through a callback; this makes it impossible for the table to calculate the total page count. As a consequence, the last-page navigation button is disabled (although all of the other buttons, as well as the direct navigation, are still functional).

To get around this, supply a value to the `page_count` parameter of the table. This will serve as the "last page", which will re-enable the last-page navigation button and be displayed in the pagination menu. Please note that you will not be able to use the pagination menu to navigate to a page that comes after the last page specified by `page_count`!

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout = html_div([
      dash_datatable(
        id="datatable-paging-page-count",
        columns=[
            Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
        ],
        page_current=0,
        page_size=PAGE_SIZE,
        page_action="custom"
      ),
      html_br(),
        dcc_checklist(
            id="datatable-use-page-count",
            options=[
                Dict("label"=> "Use page_count", "value"=> "True")
            ],
            value=["True"]
        ),
        "Page count:",
        dcc_input(
            id="datatable-page-count",
            type="number",
            min=1,
            max=29,
            value=20
        )
    ])

    callback!(app,
        Output("datatable-paging-page-count", "data"),
        Input("datatable-paging-page-count", "page_current"),
        Input("datatable-paging-page-count", "page_size")
        )  do page_current, page_size
        return Dict.(pairs.(eachrow(
          df[(page_current*page_size+1):((page_current+ 1)*page_size), :]
        )))

    end

    callback!(app,
        Output("datatable-paging-page-count", "page_count"),
        Input("datatable-use-page-count", "value"),
        Input("datatable-page-count", "value")
        ) do use_page_count, page_count_value
        if length(use_page_count) == 0 || page_count_value isa Nothing
            return nothing
        end
        return page_count_value
      end

    run_server(app, "0.0.0.0", debug=true)

index | continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | --- | ---
1 | Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
2 | Europe | Albania | 5937.029525999999 | 76.423 | 3600523
3 | Africa | Algeria | 6223.367465 | 72.301 | 33333216
4 | Africa | Angola | 4797.231267 | 42.731 | 12420476
5 | Americas | Argentina | 12779.37964 | 75.32 | 40301927

1
/
20
Use page_count
Page count:

## Backend Paging with Sorting

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout =  dash_datatable(
        id="table-paging-and-sorting",
        columns=[
            Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
        ],
        page_current=0,
        page_size=PAGE_SIZE,
        page_action="custom",

        sort_action="custom",
        sort_mode="single",
        sort_by=[]
      )

    callback!(app,
    Output("table-paging-and-sorting", "data"),
    Input("table-paging-and-sorting", "page_current"),
    Input("table-paging-and-sorting", "page_size"),
    Input("table-paging-and-sorting", "sort_by")
        ) do page_current, page_size, sort_by
        if isempty(sort_by)
          dff = df
        else
          if sort_by[1].direction == "asc"
            dir = true
          else
            dir = false
          end
          dff = sort(df, [sort_by[1].column_id], rev=dir)
        end
        return Dict.(pairs.(eachrow(
          dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
        )))

    end

    run_server(app, "0.0.0.0", debug=true)

index | continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | --- | ---
1 | Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
2 | Europe | Albania | 5937.029525999999 | 76.423 | 3600523
3 | Africa | Algeria | 6223.367465 | 72.301 | 33333216
4 | Africa | Angola | 4797.231267 | 42.731 | 12420476
5 | Americas | Argentina | 12779.37964 | 75.32 | 40301927

1

## Backend Paging with Multi Column Sorting

Multi-column sort allows you to sort by multiple columns. This is useful when you have categorical columns with repeated values and you're interested in seeing the sorted values for each category.

In this example, try sorting by continent and then any other column.

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout =  dash_datatable(
        id="table-sorting-filtering",
        columns=[
            Dict("name" =>  i, "id" =>  i, "deletable" =>  true) for i in sort(names(df))
        ],
        page_current=0,
        page_size=PAGE_SIZE,
        page_action="custom",

        filter_action="custom",
        filter_query="",
        sort_action="custom",
        sort_mode="multi",
        sort_by=[]
    )
    operators = [["ge ", ">="],
    ["le ", "<="],
    ["lt ", "<"],
    ["gt ", ">"],
    ["ne ", "!="],
    ["eq ", "=="],
    ["contains "],
    ["datestartswith "]]

    function split_filter_part(filter_part)
        for operator_type in operators
            for operator in operator_type
                if occursin(operator,filter_part)
                    name_part, value_part = split(filter_part,operator,limit=2)
                    name = name_part[
                        findfirst(isequal('{'),
                        name_part)+1:findlast(isequal('}'), name_part)-1
                    ]
                    value_part = strip(value_part)
                    v0 = value_part[1]
                    if (v0 == value_part[end] && v0 in ("'", '"', '`'))
                        value = replace(value_part[2: end], string("\\",v0) => v0)
                    else
                        try
                            value = Float64(value_part)
                        catch
                            value = value_part
                        end
                    end
                    return name, strip(operator_type[1]), value
                end
            end
        end
    end

    callback!(app,
      Output("table-sorting-filtering", "data"),
      Input("table-sorting-filtering", "page_current"),
      Input("table-sorting-filtering", "page_size"),
      Input("table-sorting-filtering", "sort_by"),
      Input("table-sorting-filtering", "filter_query")
        ) do page_current, page_size, sort_by, filter
        filtering_expressions = split(filter," && ")
        dff = df
        for filter_part in filtering_expressions
            if filter_part != ""
                col_name, operator, filter_value = split_filter_part(filter_part)
                if operator in ("eq", "ne", "lt", "le", "gt", "ge")
                    if operator == "eq"
                        dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
                    elseif operator == "ne"
                        dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
                    elseif operator == "lt"
                        dff = DataFrames.filter(r -> r[col_name]<filter_value, dff)
                    elseif operator == "le"
                        dff = DataFrames.filter(r -> r[col_name]<=filter_value, dff)
                    elseif operator == "gt"
                        dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
                    else
                        dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
                    end
                elseif operator == "contains"
                    dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
                else
                    dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
                end
            end
        end
        if !isempty(sort_by)
          dir = [(i.direction == "asc" ? true : false) for i in sort_by]
          cid = [i.column_id for i in sort_by]
          dff = sort(dff, cid, rev=dir)
        end
        if nrow(dff) > ((page_current+ 1)*page_size)
            return Dict.(pairs.(eachrow(
                dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
            )))
        else
            return Dict.(pairs.(eachrow(dff)))
        end
    end

    run_server(app, "0.0.0.0", debug=true)

continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | ---
Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
Europe | Albania | 5937.029525999999 | 76.423 | 3600523
Africa | Algeria | 6223.367465 | 72.301 | 33333216
Africa | Angola | 4797.231267 | 42.731 | 12420476
Americas | Argentina | 12779.37964 | 75.32 | 40301927

1

## Backend Paging with Filtering

DataTable's front-end filtering has its own filtering expression language.

Currently, backend filtering must parse the same filtering language. If you write an expression that is not "valid" under the filtering language, then it will not be passed to the backend.

This limitation will be removed in the future to allow you to write your own expression query language.

In this example, we've written a Pandas backend for the filtering language. It supports `eq`, `<`, and `>`. For example, try:

- Enter `eq Asia` in the "continent" column
- Enter `> 5000` in the "gdpPercap" column
- Enter `< 80` in the `lifeExp` column

> Note that unlike the front-end filtering, our backend filtering expression language doesn't require or support `num()` or wrapping items in double quotes (`"`). We will improve this syntax in the future, follow dash-table#169 for more.

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout =  dash_datatable(
        id="table-filtering",
        columns=[
            Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
        ],
        page_current=0,
        page_size=PAGE_SIZE,
        page_action="custom",

        filter_action="custom",
        filter_query=""
    )
    operators = [["ge ", ">="],
    ["le ", "<="],
    ["lt ", "<"],
    ["gt ", ">"],
    ["ne ", "!="],
    ["eq ", "=="],
    ["contains "],
    ["datestartswith "]]

    function split_filter_part(filter_part)
        for operator_type in operators
            for operator in operator_type
                if occursin(operator,filter_part)
                    name_part, value_part = split(filter_part,operator,limit=2)
                    name = name_part[
                        findfirst(isequal('{'),
                        name_part)+1:findlast(isequal('}'), name_part)-1
                    ]
                    value_part = strip(value_part)
                    v0 = value_part[1]
                    if (v0 == value_part[end] && v0 in ("'", '"', '`'))
                        value = replace(value_part[2: end], string("\\",v0) => v0)
                    else
                        try
                            value = Float64(value_part)
                        catch
                            value = value_part
                        end
                    end
                    return name, strip(operator_type[1]), value
                end
            end
        end
    end

    callback!(app,
        Output("table-filtering", "data"),
        Input("table-filtering", "page_current"),
        Input("table-filtering", "page_size"),
        Input("table-filtering", "filter_query")
        ) do page_current,page_size, filter
        filtering_expressions = split(filter," && ")
        dff = df
        for filter_part in filtering_expressions
            if filter_part != ""
                col_name, operator, filter_value = split_filter_part(filter_part)
                if operator in ("eq", "ne", "lt", "le", "gt", "ge")
                    if operator == "eq"
                        dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
                    elseif operator == "ne"
                        dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
                    elseif operator == "lt"
                        dff = DataFrames.filter(r -> r[col_name]<filter_value, dff)
                    elseif operator == "le"
                        dff = DataFrames.filter(r -> r[col_name]<=filter_value, dff)
                    elseif operator == "gt"
                        dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
                    else
                        dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
                    end
                elseif operator == "contains"
                    dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
                else
                    dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
                end
            end
        end
        if nrow(dff) > ((page_current+ 1)*page_size)
            return Dict.(pairs.(eachrow(
                dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
            )))
        else
            return Dict.(pairs.(eachrow(dff)))
        end
    end

    run_server(app, "0.0.0.0", debug=true)

continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | ---
Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
Europe | Albania | 5937.029525999999 | 76.423 | 3600523
Africa | Algeria | 6223.367465 | 72.301 | 33333216
Africa | Angola | 4797.231267 | 42.731 | 12420476
Americas | Argentina | 12779.37964 | 75.32 | 40301927

1

## Backend Paging with Filtering and Multi-Column Sorting

    using Dash

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash()

    PAGE_SIZE = 5

    app.layout =  dash_datatable(
        id="table-multicol-sorting",
        columns=[
            Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
        ],
        page_current=0,
        page_size=PAGE_SIZE,
        page_action="custom",

        sort_action="custom",
        sort_mode="multi",
        sort_by=[]
      )

     callback!(app,
        Output("table-multicol-sorting", "data"),
        Input("table-multicol-sorting", "page_current"),
        Input("table-multicol-sorting", "page_size"),
        Input("table-multicol-sorting", "sort_by")
        ) do page_current, page_size, sort_by
        if isempty(sort_by)
          dff = df
        else
          dir = [(i.direction == "asc" ? true : false) for i in sort_by]
          cid = [i.column_id for i in sort_by]
          dff = sort(df, cid, rev=dir)
        end

            return Dict.(pairs.(eachrow(
              dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
            )))
    end

    run_server(app, "0.0.0.0", debug=true)

continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | ---
Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
Europe | Albania | 5937.029525999999 | 76.423 | 3600523
Africa | Algeria | 6223.367465 | 72.301 | 33333216
Africa | Angola | 4797.231267 | 42.731 | 12420476
Americas | Argentina | 12779.37964 | 75.32 | 40301927

1

## Connecting Backend Paging with a Graph

This final example ties it all together: the graph component displays the current page of the `data`.

    using Dash
    using DashBootstrapComponents

    using CSV, DataFrames

    df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
    df[!," index"] =1:nrow(df)

    app = dash(external_stylesheets=[dbc_themes.BOOTSTRAP])

    PAGE_SIZE = 20

    app.layout = dbc_row([
      dbc_col([
        dash_datatable(
          id="table-paging-with-graph",
          columns=[
              Dict("name" =>  i, "id" =>  i) for i in sort(names(df))
          ],
          page_current=0,
          page_size=PAGE_SIZE,
          page_action="custom",

          filter_action="custom",
          filter_query="",

          sort_action="custom",
          sort_mode="multi",
          sort_by=[]
      )
    ], md=8)
    dbc_col(
      id="table-paging-with-graph-container", md=3
    )
    ])

    operators = [["ge ", ">="],
    ["le ", "<="],
    ["lt ", "<"],
    ["gt ", ">"],
    ["ne ", "!="],
    ["eq ", "=="],
    ["contains "],
    ["datestartswith "]]

    function split_filter_part(filter_part)
        for operator_type in operators
            for operator in operator_type
                if occursin(operator,filter_part)
                    name_part, value_part = split(filter_part,operator,limit=2)
                    name = name_part[
                        findfirst(isequal('{'),
                        name_part)+1:findlast(isequal('}'), name_part)-1
                    ]
                    value_part = strip(value_part)
                    v0 = value_part[1]
                    if (v0 == value_part[end] && v0 in ("'", '"', '`'))
                        value = replace(value_part[2: end], string("\\",v0) => v0)
                    else
                        try
                            value = Float64(value_part)
                        catch
                            value = value_part
                        end
                    end
                    return name, strip(operator_type[1]), value
                end
            end
        end
    end

    callback!(app,
      Output("table-paging-with-graph", "data"),
      Input("table-paging-with-graph", "page_current"),
      Input("table-paging-with-graph", "page_size"),
      Input("table-paging-with-graph", "sort_by"),
      Input("table-paging-with-graph", "filter_query")
        ) do page_current, page_size, sort_by, filter
        filtering_expressions = split(filter," && ")
        dff = df
        for filter_part in filtering_expressions
            if filter_part != ""
                col_name, operator, filter_value = split_filter_part(filter_part)
                if operator in ("eq", "ne", "lt", "le", "gt", "ge")
                    if operator == "eq"
                        dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
                    elseif operator == "ne"
                        dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
                    elseif operator == "lt"
                        dff = DataFrames.filter(r -> r[col_name]<filter_value, dff)
                    elseif operator == "le"
                        dff = DataFrames.filter(r -> r[col_name]<=filter_value, dff)
                    elseif operator == "gt"
                        dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
                    else
                        dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
                    end
                elseif operator == "contains"
                    dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
                else
                    dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
                end
            end
        end
        if !isempty(sort_by)
          dir = [(i.direction == "asc" ? true : false) for i in sort_by]
          cid = [i.column_id for i in sort_by]
          dff = sort(dff, cid, rev=dir)
        end
        if nrow(dff) > ((page_current+ 1)*page_size)
            return Dict.(pairs.(eachrow(
                dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
            )))
        else
            return Dict.(pairs.(eachrow(dff)))
        end
    end

    callback!(app,
        Output("table-paging-with-graph-container", "children"),
        Input("table-paging-with-graph", "data")
    )  do rows
        if rows isa Nothing
          throw(PreventUpdate())
        end
        dff = DataFrame(rows)
        return html_div(
            [ dcc_graph(
                    id=column,
                    figure=Dict(
                        "data" =>  [
                          Dict(
                                "x" =>  dff[!,"country"],
                                "y" =>   (column in names(dff)) ? dff[!, column] : [],
                                "type" =>  "bar",
                                "marker" =>  Dict("color" =>  "#0074D9"),
                            )
                        ],
                        "layout" =>  Dict(
                            "xaxis" =>  Dict("automargin" =>  true),
                            "yaxis" =>  Dict("automargin" =>  true),
                            "height" =>  250,
                            "margin" =>  Dict("t" =>  10, "l" =>  10, "r" =>  10),
                        ),
                    ),
                )
                for column in ["pop", "lifeExp", "gdpPercap"]
            ]
        )
    end

    run_server(app, "0.0.0.0", debug=true)

continent | country | gdpPercap | lifeExp | pop
--- | --- | --- | --- | ---
Asia | Afghanistan | 974.5803384 | 43.828 | 31889923
Europe | Albania | 5937.029525999999 | 76.423 | 3600523
Africa | Algeria | 6223.367465 | 72.301 | 33333216
Africa | Angola | 4797.231267 | 42.731 | 12420476
Americas | Argentina | 12779.37964 | 75.32 | 40301927
Oceania | Australia | 34435.367439999995 | 81.235 | 20434176
Europe | Austria | 36126.4927 | 79.829 | 8199783
Asia | Bahrain | 29796.04834 | 75.635 | 708573
Asia | Bangladesh | 1391.253792 | 64.062 | 150448339
Europe | Belgium | 33692.60508 | 79.441 | 10392226
Africa | Benin | 1441.284873 | 56.728 | 8078314
Americas | Bolivia | 3822.137084 | 65.554 | 9119152
Europe | Bosnia and Herzegovina | 7446.298803 | 74.852 | 4552198
Africa | Botswana | 12569.85177 | 50.728 | 1639131
Americas | Brazil | 9065.800825 | 72.39 | 190010647
Europe | Bulgaria | 10680.79282 | 73.005 | 7322858
Africa | Burkina Faso | 1217.032994 | 52.295 | 14326203
Africa | Burundi | 430.0706916 | 49.58 | 8390505
Asia | Cambodia | 1713.778686 | 59.723 | 14131858
Africa | Cameroon | 2042.09524 | 50.43 | 17696293

1