如何在React+Next.js API驱动应用中实现筛选与排序？

1. 筛选与排序的触发逻辑问题 #

当前SearchBar中，排序选项变更后未主动触发数据更新，且筛选按钮仅提交筛选条件，未同步排序状态。需要调整逻辑，确保点击按钮时同时提交筛选和排序参数。

修改SearchBar.js：

import { useState } from "react";

export default function SearchBar({ onFilter, onSort }) {
  const [locationFilter, setLocationFilter] = useState("");
  const [monthFilter, setMonthFilter] = useState("");
  const [sortOption, setSortOption] = useState("");

  // 合并筛选与排序的提交逻辑
  const handleSubmit = () => {
    onFilter({ location: locationFilter, month: monthFilter });
    onSort(sortOption);
  };

  return (
    <div className="max-w-4xl mx-auto border rounded-md border-black dark:bg-white bg-white shadow-lg p-2 flex flex-col sm:flex-row">
      {/* 原有筛选选项部分保持不变 */}
      <div className="flex sm:border-none border-b border-black">
        <div className="flex flex-1 justify-center sm:justify-start">
          <select
            id="location"
            value={locationFilter}
            onChange={(e) => setLocationFilter(e.target.value)}
            className="text-black w-full sm:w-64"
          >
            <option value="">Departure Location</option>
            <option value="Mumbai">Mumbai</option>
            <option value="Delhi">Delhi</option>
            <option value="Aurangabad">Aurangabad</option>
          </select>
        </div>
        <div className="flex flex-1 justify-center mt-0 sm:mt-0">
          <input
            id="month"
            type="month"
            value={monthFilter}
            onChange={(e) => setMonthFilter(e.target.value)}
            className="text-black"
          />
        </div>
      </div>
      <div className="flex">
        <div className="flex flex-1 justify-center sm:justify-end mt-0 sm:mt-0">
          <select
            id="sort"
            value={sortOption}
            onChange={(e) => setSortOption(e.target.value)}
            className="text-black dark:text-black w-[174px] lg:pt-3 pt-6 sm:w-48"
          >
            <option value="">Sort</option>
            <option value="price ASC">Low to High</option>
            <option value="price DESC">High to Low</option>
            <option value="ratings DESC">Ratings</option>
          </select>
        </div>
        <div className="flex flex-1 justify-center sm:justify-end mt-2 sm:mt-0">
          <input className="w-full sm:w-auto" type="text" placeholder="Search"/>
        </div>
        <div className="flex justify-center mt-2 sm:mt-0">
          {/* 按钮点击事件改为handleSubmit */}
          <button
            className="sm:w-9 w-full sm:block sm:pt-0 pt-2 sm:pr-0 pr-2 mt-1 flex sm:justify-normal justify-center h-8 rounded bg-gray-900"
            onClick={handleSubmit}
          >
            <svg xmlns="http://www.w3.org/2000/svg" className="h-4 ml-3" viewBox="0 0 512 512">
              <path fill="#ffffff" d="M416 208c0 45.9-14.9 88.3-40 122.7L502.6 457.4c12.5 12.5 12.5 32.8 0 45.3s-32.8 12.5-45.3 0L330.7 376c-34.4 25.2-76.8 40-122.7 40C93.1 416 0 322.9 0 208S93.1 0 208 0S416 93.1 416 208zM208 352a144 144 0 1 0 0-288 144 144 0 1 0 0 288z"/>
            </svg>
          </button>
        </div>
      </div>
    </div>
  );
}

2. CardsArea的状态重置问题 #

筛选或排序条件变化时，未重置分页相关状态（page、hasMore），导致新筛选后仍从旧页码加载数据，且未清空原有卡片。需要在条件变更时重置状态，重新加载第一页数据。

修改CardsArea.js：

import { useEffect, useState } from "react";
import Card from "./Card";

export default function CardsArea({ filterOption, sortOption }) {
  const [cards, setCards] = useState([]);
  const [loading, setLoading] = useState(false);
  const [page, setPage] = useState(1);
  const [hasMore, setHasMore] = useState(true);

  useEffect(() => {
    // 条件变更时重置分页与卡片状态
    setPage(1);
    setHasMore(true);
    setCards([]);
    loadInitialCards();
  }, [filterOption, sortOption]);

  const loadInitialCards = async () => {
    setLoading(true);

    try {
      const response = await fetchCards(page, filterOption, sortOption);
      const data = await response.json();

      if (data.length === 0) {
        setHasMore(false);
        setCards([]);
      } else {
        setCards(data);
        setPage(prev => prev + 1);
      }
    } catch (error) {
      console.error("Error loading initial cards:", error);
    } finally {
      setLoading(false);
    }
  };

  // 优化URL拼接，过滤空参数
  const fetchCards = async (pageNum, filterOption, sortOption) => {
    const params = new URLSearchParams();
    params.append('page', pageNum);
    if (filterOption.location) params.append('location', filterOption.location);
    if (filterOption.month) params.append('month', filterOption.month);
    if (sortOption) params.append('sort', sortOption);

    const url = `/api/cards?${params.toString()}`;
    const response = await fetch(url);
    return response;
  };

  return (
    <div className="h-auto w-full flex bg-white flex-col justify-center pt-5">
      <div className="flex justify-center flex-col mx-auto">
        {cards.map((card, index) => (
          <Card key={index} {...card} />
        ))}
        {loading && <p>Loading...</p>}
        {!loading && !hasMore && <p>End</p>}
      </div>
    </div>
  );
}

3. API路由的SQL注入风险与月份参数错误 #

直接拼接用户输入到SQL语句存在注入风险，且月份参数处理逻辑错误（type="month"返回YYYY-MM格式，后端需提取数字月份）。

修改route.js：

import { NextResponse } from "next/server";
import { query } from "../../../lib/db";

export const GET = async (req) => {
  const { searchParams } = new URL(req.url);
  const location = searchParams.get('location');
  const monthStr = searchParams.get('month');
  const sort = searchParams.get('sort');
  
  try {
    let sqlQuery = "SELECT * FROM card";
    let conditions = [];
    let values = [];

    // 处理location筛选，使用参数化查询
    if (location) {
      conditions.push(`location = ?`);
      values.push(location);
    }
    // 处理月份筛选，提取数字部分
    if (monthStr) {
      const month = parseInt(monthStr.split('-')[1], 10);
      if (!isNaN(month)) {
        conditions.push(`MONTH(departDate) = ?`);
        values.push(month);
      }
    }
    // 拼接WHERE条件
    if (conditions.length > 0) {
      sqlQuery += " WHERE " + conditions.join(" AND ");
    }
    // 验证排序参数，限制允许的排序规则
    const allowedSorts = ['price ASC', 'price DESC', 'ratings DESC'];
    if (sort && allowedSorts.includes(sort)) {
      sqlQuery += ` ORDER BY ${sort}`;
    }

    // 执行参数化查询
    const results = await query({ 
      query: sqlQuery,
      values: values
    });

    return NextResponse.json(results);
  } catch (e) {
    return NextResponse.json({ message: e.message }, { status: 500 });
  }
};

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