如何在React中实现Shadcn Table：用Prisma全文搜索替代内置过滤器

PostgreSQL 方案 #

1. 模型定义（添加全文索引）

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  createdAt DateTime @default(now())

  @@fulltext([title, content]) // 为title和content字段建立全文索引
}

2. 执行搜索查询

const searchPosts = async (query) => {
  return await prisma.post.findMany({
    where: {
      OR: [
        { title: { search: query } },
        { content: { search: query } },
      ],
    },
  });
};

MySQL 方案 #

1. 模型定义（添加全文索引）

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  createdAt DateTime @default(now())

  @@index([title, content], type: "fulltext")
}

2. 执行搜索查询（支持布尔模式）

const searchPosts = async (query) => {
  // 使用原生SQL实现精准全文搜索
  return await prisma.$queryRaw`
    SELECT * FROM Post 
    WHERE MATCH(title, content) AGAINST(${query} IN BOOLEAN MODE)
  `;
};

前端本地过滤方案 #

适合数据量较小的场景，直接在前端对已有数据进行过滤：

import { useTable, useGlobalFilter } from '@tanstack/react-table';

export default function DataTable({ columns, data }) {
  const {
    getTableProps,
    getTableBodyProps,
    headerGroups,
    rows,
    prepareRow,
    setGlobalFilter,
  } = useTable(
    { columns, data },
    useGlobalFilter
  );

  return (
    <div>
      <input
        type="text"
        placeholder="输入关键词搜索"
        onChange={(e) => setGlobalFilter(e.target.value || undefined)}
      />
      <table {...getTableProps()}>
        <thead>
          {headerGroups.map(headerGroup => (
            <tr {...headerGroup.getHeaderGroupProps()}>
              {headerGroup.headers.map(column => (
                <th {...column.getHeaderProps()}>{column.render('Header')}</th>
              ))}
            </tr>
          ))}
        </thead>
        <tbody {...getTableBodyProps()}>
          {rows.map(row => {
            prepareRow(row);
            return (
              <tr {...row.getRowProps()}>
                {row.cells.map(cell => (
                  <td {...cell.getCellProps()}>{cell.render('Cell')}</td>
                ))}
              </tr>
            );
          })}
        </tbody>
      </table>
    </div>
  );
}

后端联动搜索方案 #

适合大数据量场景，通过API调用Prisma的搜索接口实现分页+搜索：

import { useState, useEffect } from 'react';
import { useTable } from '@tanstack/react-table';

export default function DataTable({ columns }) {
  const [searchQuery, setSearchQuery] = useState('');
  const [data, setData] = useState([]);
  const [loading, setLoading] = useState(false);

  // 防抖请求后端数据
  useEffect(() => {
    const timer = setTimeout(async () => {
      setLoading(true);
      const res = await fetch(`/api/posts?search=${encodeURIComponent(searchQuery)}`);
      const result = await res.json();
      setData(result);
      setLoading(false);
    }, 300);
    return () => clearTimeout(timer);
  }, [searchQuery]);

  const {
    getTableProps,
    getTableBodyProps,
    headerGroups,
    rows,
    prepareRow,
  } = useTable({ columns, data });

  return (
    <div>
      <input
        type="text"
        placeholder="输入关键词搜索"
        value={searchQuery}
        onChange={(e) => setSearchQuery(e.target.value)}
      />
      {loading && <div className="loading">加载中...</div>}
      <table {...getTableProps()}>
        <thead>
          {headerGroups.map(headerGroup => (
            <tr {...headerGroup.getHeaderGroupProps()}>
              {headerGroup.headers.map(column => (
                <th {...column.getHeaderProps()}>{column.render('Header')}</th>
              ))}
            </tr>
          ))}
        </thead>
        <tbody {...getTableBodyProps()}>
          {rows.map(row => {
            prepareRow(row);
            return (
              <tr {...row.getRowProps()}>
                {row.cells.map(cell => (
                  <td {...cell.getCellProps()}>{cell.render('Cell')}</td>
                ))}
              </tr>
            );
          })}
        </tbody>
      </table>
    </div>
  );
}

后端API示例（Node.js/Express） #

const express = require('express');
const prisma = require('./prisma/client');
const app = express();

app.get('/api/posts', async (req, res) => {
  const { search } = req.query;
  let posts;
  if (search) {
    posts = await prisma.post.findMany({
      where: {
        OR: [
          { title: { search } },
          { content: { search } },
        ],
      },
    });
  } else {
    posts = await prisma.post.findMany();
  }
  res.json(posts);
});

app.listen(3001, () => console.log('Server running on port 3001'));

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