如何在jQuery DataTable中实现单独搜索并排除不可搜索列？

Hey there! Let's walk through how to add per-column search to your existing DataTables setup, while skipping columns that shouldn't be searchable (like your Edit button column). Since you're using server-side processing, we'll cover both the frontend setup and what you need to handle on the backend.

Step 1: Mark Non-Searchable Columns in DataTables Config #

First, update your DataTables initialization to explicitly define which columns are searchable. Add a columns array where you set searchable: false for columns you want to exclude (like the actions column with your Edit button).

Here's how to modify your existing code:

$(document).ready(function () { 
    var table = $('#table').DataTable({ 
        "dom": 'Bfrtip', 
        "order": [2, "asc"], 
        "createdRow": function (row, data, dataIndex) { 
            if (data.IsActive === false) { 
                $(row).addClass('inactive'); 
            } 
        }, 
        "serverSide": true, 
        "processing": true, 
        "paging": true, 
        "bLengthChange": false,
        // Add this columns array to control searchability
        "columns": [
            { "searchable": true },   // Column 0: searchable
            { "searchable": true },   // Column 1: searchable
            { "searchable": true },   // Column 2: searchable (your default sorted column)
            { "searchable": false },  // Column 3: non-searchable (e.g., Edit button column)
            // Add entries for all your columns, setting searchable true/false as needed
        ]
        // ... rest of your existing config
    });

    // Step 2: Add Search Inputs to Table Footer
    // First, make sure your HTML table has a <tfoot> section with <th> elements for each column
    $('#table tfoot th').each(function(index) {
        var column = table.column(index);
        // Only add an input if the column is marked as searchable
        if (column.settings()[0].aoColumns[index].bSearchable) {
            $(this).empty(); // Clear existing footer content
            $('<input type="text" placeholder="Search ' + $(this).text() + '" class="form-control form-control-sm" />')
                .appendTo($(this))
                .on('keyup change', function() {
                    // Trigger column search only if the input value changed
                    if (column.search() !== this.value) {
                        column.search(this.value).draw();
                    }
                });
        }
    });
});

Step 3: Update Your HTML Table to Include a Footer #

Don't forget to add a <tfoot> section to your table in the HTML—this is where the search inputs will live. For example:

<table id="table" class="table table-striped">
    <thead>
        <tr>
            <th>Column 1</th>
            <th>Column 2</th>
            <th>Column 3</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        <!-- Table rows will be populated by DataTables -->
    </tbody>
    <tfoot>
        <tr>
            <th>Column 1</th>
            <th>Column 2</th>
            <th>Column 3</th>
            <th>Actions</th>
        </tr>
    </tfoot>
</table>

Step 4: Handle Column Search on the Server (Critical for Server-Side Processing) #

Since you have serverSide: true, all search logic happens on your backend. DataTables will send additional parameters for each column's search value in the AJAX request. You'll need to parse these and apply the filters to your database query.

For example, in the request, you'll get parameters like:

• columns[0][search][value]: The search term for column 0
• columns[1][search][value]: The search term for column 1
• And so on...

Your backend code should check each searchable column's value and add a WHERE clause to your query if the value is not empty. For example, in C# (since your URL is /Customer/Edit/, you're likely using ASP.NET):

// Example: Extract column search values from the request
var column0Search = Request.Form["columns[0][search][value]"];
var column1Search = Request.Form["columns[1][search][value]"];

// Apply filters to your query
var query = db.Customers.AsQueryable();

if (!string.IsNullOrEmpty(column0Search))
{
    query = query.Where(c => c.Column1.Contains(column0Search));
}

if (!string.IsNullOrEmpty(column1Search))
{
    query = query.Where(c => c.Column2.Contains(column1Search));
}

// Continue with sorting, paging, etc.

Key Notes: #

• The columns array in DataTables must match the number of columns in your table. Double-check the index positions to avoid mismatches.
• If you want to use a different input type (like a dropdown for status columns), you can replace the text input with a select element and adjust the event listener accordingly.
• The draw() method refreshes the table with the new search filters—this is necessary for server-side processing to fetch updated data.

内容的提问来源于stack exchange，提问作者Donald Jansen