column().search() not updating DataTable

jon4179

I have a DataTable that I would like to filter with HTML <select> elements. Here is an example of one of the <select> elements:

<select name="wirecenterFilter" id="wirecenterFilter" class="form-control">
        <option value="" disabled selected>Select a Wirecenter</option>
         {% for wirecenter in wirecenterOptions %}
              <option>{{wirecenter.get("Wirecenter")}}</option>
         {% endfor %}
</select>

I have column().search() set up to update my DataTable onchange from the <select> elements. Below is my DataTable:

$(document).ready(function () {
                const highlightedRows = {};

                var table = $("#availProjects").DataTable({
                  processing: true,
                  serverSide: true,
                  pageLength: 10,
                  ordering: false,
                  ajax: {
                    url: "/pushpulllist",
                    type: "POST",
                    contentType: "application/json",
                    data: function (d) {
                      return JSON.stringify(d);
                    },
                  },
                  columns: [
                    { data: "ProjectNumber" },
                    { data: "BudgetYear" },
                    { data: "State" },
                    { data: "Region" },
                    { data: "Wirecenter" },
                    { data: "NumberCustLocations" },
                    { data: "CPCL" },
                    { data: "ProjectType" },
                    { data: "JustificationCode" },
                    { data: "OriginalBudget" },
                    { data: "CurrentBudget" },
                    { data: "RemainderToSpendDirect" },
                    { data: "IRR" },
                    { data: "SpendToDateDirect" },
                    { data: "ISPtag" },
                    {
                      data: "PushPull",
                      render: function (data, type, row) {
                        return `<button class="btn btn-primary">${data}</button>`;
                      },
                    },
                  ],
                  createdRow: function (row, data, dataIndex) {
                    $("td:eq(0)", row).addClass("project-number");
                    $("td:eq(2)", row).addClass("project-state");
                    $("td:eq(8)", row).addClass("justification-code");
                    $("td:eq(10)", row).addClass("current-project-budget");
                    $("td:eq(11)", row).addClass("remainder-to-spend");
                  },
                  drawCallback: function () {
                    table.rows().every(function () {
                      const rowData = this.data();
                      const projectNumber = rowData.ProjectNumber;

                      if (highlightedRows[projectNumber]) {
                        $(this.node()).addClass("custom-grey");
                      } else {
                        $(this.node()).removeClass("custom-grey");
                      }
                    });
                  },
                });

                $("#yearFilter").on("change", function () {
                  var selectedValue = $(this).val().trim();
                  table.column(1).search(selectedValue).draw();
                  
                });
                
                $("#wirecenterFilter").on("change", function () {
                  var selectedValue = $(this).val().trim();
                  table.column(4).search(selectedValue).draw();
                });

Wondering what the issue is that is causing the DataTable to not sort. I have already checked the value gathered from the <select> element and made sure there are no data type mismatches (the data in the DataTable and the value from the <select> are both strings), I've trimmed the value to make sure there no spaces. Any ideas?

kthorngren

You have server side processing enabled with serverSide: true,. The server scrip tis responsible for the searches. Are you using a Datatables supplied server side processing script or a custom script? The server script will need debugging to find the issue.

Kevin

jon4179

Great point. I'm using my own script for serverSide (below):

@server.route('/pushpulllist', methods=['GET', 'POST'])
def push_pull_list():
    if request.method == 'POST':
        # DataTables parameters
        draw = request.json.get('draw', 1)
        start = request.json.get('start', 0)
        length = request.json.get('length', 10)
        search_value = request.json.get('search', {}).get('value', '')
        
        base_query = "SELECT * FROM [PushPull].[PPMasterTable] WHERE 1=1"
        params = []

        total_count_query = "SELECT COUNT(*) FROM [PushPull].[PPMasterTable] WHERE 1=1"
        cur.execute(total_count_query)
        total_records = cur.fetchone()[0]

        filtered_records = total_records

        if search_value:
            search_query = base_query + " AND (JustificationCode LIKE ? OR State LIKE ? OR Region LIKE ? OR BudgetYear LIKE ? OR Wirecenter LIKE ?)"
            search_params = [f'%{search_value}%', f'%{search_value}%', f'%{search_value}%', f'%{search_value}%', f'%{search_value}%']

            filtered_count_query = "SELECT COUNT(*) FROM [PushPull].[PPMasterTable] WHERE (JustificationCode LIKE ? OR State LIKE ? OR Region LIKE ? OR BudgetYear LIKE ?  OR Wirecenter LIKE ?)"
            cur.execute(filtered_count_query, search_params)
            filtered_records = cur.fetchone()[0]  

            sql_query = search_query + " ORDER BY [ProjectNumber] OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
            params = search_params + [start, length]
        else:
            sql_query = base_query + " ORDER BY [ProjectNumber] OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
            params = [start, length]

        cur.execute(sql_query, params)
        desc = cur.description
        column = [col[0] for col in desc]
        data = [dict(zip(column, row)) for row in cur.fetchall()]

        response = {
            "draw": draw,
            "recordsTotal": total_records,
            "recordsFiltered": filtered_records,
            "data": data
        }

        return jsonify(response)

return render_template("pushpulllist.html")

I wasn't sure if the search_value was actually receiving anything, so I tried modifying my filter function to send it onChange... Not sure if I have the right idea.

$("#yearFilter").on("change", function () {
                  var selectedValue = $(this).val().trim();
                  fetch("/pushpulllist", {
                    method: "POST",
                    headers: {
                      "Content-Type": "application/json",
                    },
                    body: JSON.stringify({
                      search: {
                        value: selectedValue,
                      },
                    }),
                  });
                  table.column(1).search(selectedValue).draw();
                });

kthorngren

table.column(1).search(selectedValue).draw();

That is the way to column search whether using client side or server side processing. See the SSP protocol docs to see how the column searches are sent to the server.

Looks like you will need to update your code to grab the column search value and apply to the query string.

Kevin