如何按日期筛选？基于End_date状态的下拉框表格过滤问询

Hey there! Let's walk through exactly how to build this status filter for your table, using the End_date field to determine active/inactive records. I'll cover core logic and practical code examples for common scenarios.

Core Filtering Logic #

First, let's clarify the rules we need to enforce:

• All: Return every record in the dataset—no filtering applied
• Active: Only return records where End_date is null (no end date set)
• Inactive: Only return records where End_date is not null (has an end date value)

Practical Implementation Examples #

1. Vanilla JavaScript (Pure Frontend) #

If you're working with plain HTML and JS, here's a complete example:

HTML Structure

<select id="statusFilter">
  <option value="All">All</option>
  <option value="Active">Active</option>
  <option value="Inactive">Inactive</option>
</select>

<table id="resultsTable">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>End Date</th>
    </tr>
  </thead>
  <tbody id="tableBody">
    <!-- Filtered rows will populate here -->
  </tbody>
</table>

JavaScript Logic

// Replace this with your actual dataset
const tableData = [
  { id: 1, name: "Project Alpha", end_date: null },
  { id: 2, name: "Project Beta", end_date: "2024-06-20" },
  { id: 3, name: "Project Gamma", end_date: null },
  { id: 4, name: "Project Delta", end_date: "2023-11-05" }
];

// Grab DOM elements
const filterDropdown = document.getElementById('statusFilter');
const tableBody = document.getElementById('tableBody');

// Helper function to render table rows
function renderFilteredRows(data) {
  tableBody.innerHTML = '';
  data.forEach(item => {
    const row = document.createElement('tr');
    row.innerHTML = `
      <td>${item.id}</td>
      <td>${item.name}</td>
      <td>${item.end_date || 'N/A'}</td>
    `;
    tableBody.appendChild(row);
  });
}

// Initial render with all data
renderFilteredRows(tableData);

// Add filter change listener
filterDropdown.addEventListener('change', () => {
  const selectedStatus = filterDropdown.value;
  let filteredData;

  switch(selectedStatus) {
    case 'Active':
      filteredData = tableData.filter(item => item.end_date === null);
      break;
    case 'Inactive':
      filteredData = tableData.filter(item => item.end_date !== null);
      break;
    default: // 'All' case
      filteredData = [...tableData]; // Copy original data to avoid mutation
      break;
  }

  renderFilteredRows(filteredData);
});

2. Vue.js Example #

If you're using Vue, you can leverage computed properties for clean, reactive filtering:

<template>
  <div class="filter-container">
    <select v-model="selectedStatus">
      <option value="All">All</option>
      <option value="Active">Active</option>
      <option value="Inactive">Inactive</option>
    </select>

    <table>
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>End Date</th>
        </tr>
      </thead>
      <tbody>
        <tr v-for="item in filteredData" :key="item.id">
          <td>{{ item.id }}</td>
          <td>{{ item.name }}</td>
          <td>{{ item.end_date || 'N/A' }}</td>
        </tr>
      </tbody>
    </table>
  </div>
</template>

<script>
export default {
  data() {
    return {
      selectedStatus: 'All',
      tableData: [
        { id: 1, name: "Project Alpha", end_date: null },
        { id: 2, name: "Project Beta", end_date: "2024-06-20" },
        { id: 3, name: "Project Gamma", end_date: null },
        { id: 4, name: "Project Delta", end_date: "2023-11-05" }
      ]
    };
  },
  computed: {
    filteredData() {
      switch(this.selectedStatus) {
        case 'Active':
          return this.tableData.filter(item => item.end_date === null);
        case 'Inactive':
          return this.tableData.filter(item => item.end_date !== null);
        default:
          return this.tableData;
      }
    }
  }
};
</script>

3. Backend SQL Filtering (For Large Datasets) #

If you're fetching data from a database, it's more efficient to handle filtering on the backend to reduce data transfer. Here's how to write the SQL query, assuming you pass the selected status as a parameter:

SELECT * FROM your_table_name
WHERE 
  CASE 
    WHEN ? = 'Active' THEN end_date IS NULL
    WHEN ? = 'Inactive' THEN end_date IS NOT NULL
    ELSE 1 = 1 -- Returns all records for 'All'
  END;

Note: Use parameterized queries like this to avoid SQL injection—don't concatenate user input directly into your SQL string.

Key Notes to Consider #

• Edge Cases: If your data uses empty strings ('') instead of null for missing end dates, adjust your filter conditions to item.end_date === null || item.end_date === ''
• Performance: For large datasets, always prioritize backend filtering over frontend filtering
• Framework Adaptation: The core logic works for React, Angular, or any other framework—just adjust state management and rendering to match the framework's patterns

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