基于Prisma实现菜谱表搜索并按评分表累计平均评分排序的优化方案咨询

Hey there! Let's break down your questions and fix the underlying issues first—your current SQL has some flaws that are causing the wonky sorting behavior, so we'll address that too.

1. Should I store average ratings in the recipes table or create a new statistics table? #

Short answer: Add avg_rating and rating_count columns directly to the recipes table, and use PostgreSQL triggers to keep them updated automatically.

Here's why this is the best fit for your production database with 100k+ records:

• Performance is king: Real-time AVG() calculations combined with LIKE searches (which often bypass indexes) and joins will get slow fast as your dataset grows. Precomputing these values lets you sort and query directly on indexed columns, which is night-and-day faster for user-facing search.
• Minimal schema changes: You only need to add two columns to your existing recipes table—no new tables to manage. This aligns perfectly with your goal of avoiding major database modifications.
• Automatic consistency: Triggers handle updating the ratings whenever a new review is added, modified, or deleted. You won't have to manually sync data in your application code.

Example implementation steps:

First, add the columns to recipes:

ALTER TABLE recipes 
ADD COLUMN avg_rating numeric(3,2) DEFAULT 0,
ADD COLUMN rating_count int DEFAULT 0;

Then create a function to update the ratings, and attach triggers to the ratings table:

-- Function to recalculate recipe ratings
CREATE OR REPLACE FUNCTION update_recipe_rating()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        -- Update when a rating is added or changed
        UPDATE recipes
        SET avg_rating = (SELECT AVG(user_rating) FROM ratings WHERE recipe_id = NEW.recipe_id),
            rating_count = (SELECT COUNT(*) FROM ratings WHERE recipe_id = NEW.recipe_id)
        WHERE recipe_id = NEW.recipe_id;
    ELSIF TG_OP = 'DELETE' THEN
        -- Update when a rating is removed
        UPDATE recipes
        SET avg_rating = (SELECT COALESCE(AVG(user_rating), 0) FROM ratings WHERE recipe_id = OLD.recipe_id),
            rating_count = (SELECT COUNT(*) FROM ratings WHERE recipe_id = OLD.recipe_id)
        WHERE recipe_id = OLD.recipe_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger to run the function on rating changes
CREATE TRIGGER trigger_update_recipe_rating
AFTER INSERT OR UPDATE OR DELETE ON ratings
FOR EACH ROW EXECUTE FUNCTION update_recipe_rating();

A dedicated statistics table would work, but it adds unnecessary complexity: you'd still need triggers to sync data, and you'd have to join this table with recipes every time you query—wasting performance for no real benefit unless you plan to add dozens of other metrics later.

2. Can I implement this with Prisma, or should I use raw SQL? #

You absolutely can (and should) use Prisma for this! The precomputed rating columns play perfectly with Prisma's type-safe API, and you won't need to fall back to raw SQL unless you hit edge cases with extremely complex queries.

How to set this up in Prisma:

1. Update your Prisma schema to include the new columns:

model Recipe {
  recipe_id    Int      @id @default(autoincrement())
  recipe_title String
  avg_rating   Decimal? @db.Decimal(3,2) @default(0)
  rating_count Int      @default(0)
  // Keep your existing fields and relations
  resources    Resource[]
  ratings      Rating[]
}

2. Run prisma migrate deploy (for production) or prisma migrate dev (for local) to sync the schema with your database.

3. Implement search, sorting, and cursor-based pagination with Prisma's built-in methods:

async function searchRecipes(
  query: string,
  nextCursor?: { recipe_id: number; avg_rating: number },
  take = 30
) {
  const queryParams = {
    where: {
      recipe_title: {
        contains: query,
        mode: 'insensitive' // Optional: make search case-insensitive
      }
    },
    include: {
      resources: { take: 1 } // Fetch one resource per recipe (adjust as needed)
    },
    orderBy: [
      { avg_rating: 'desc' },
      { recipe_id: 'desc' } // Fallback sort to avoid unstable ordering for same ratings
    ],
    take: take + 1, // Fetch one extra to check for next page
    ...(nextCursor && {
      cursor: { recipe_id: nextCursor.recipe_id },
      skip: 1
    })
  };

  const recipes = await prisma.recipe.findMany(queryParams);
  const hasNextPage = recipes.length > take;
  const results = hasNextPage ? recipes.slice(0, take) : recipes;

  return {
    recipes: results,
    nextCursor: hasNextPage 
      ? { recipe_id: results[results.length - 1].recipe_id, avg_rating: results[results.length - 1].avg_rating }
      : null
  };
}

If you ever need to run a one-off complex query, Prisma supports raw SQL via prisma.$queryRaw, but for your core search and pagination needs, the native API is cleaner, safer, and fully leverages Prisma's benefits.

Quick fix for your original SQL (if you want to test before adding triggers) #

Your original query had two critical issues causing broken sorting:

1. No GROUP BY clause, which led to incorrect aggregation of ratings.
2. Joining resources before aggregating ratings caused duplicate rating rows, skewing the average.

Here's a corrected version to validate expected sorting:

WITH recipe_ratings AS (
  SELECT 
    recipe_id,
    AVG(user_rating) AS recipe_rating,
    COUNT(rating_id) AS rating_count
  FROM ratings
  GROUP BY recipe_id
)
SELECT DISTINCT ON (r.recipe_id)
  r.recipe_id,
  r.recipe_title,
  COALESCE(rr.recipe_rating, 0) AS recipe_rating,
  COALESCE(rr.rating_count, 0) AS rating_count,
  res.resource_id
FROM recipes r
LEFT JOIN recipe_ratings rr ON r.recipe_id = rr.recipe_id
LEFT JOIN resources res ON r.recipe_id = res.recipe_id
WHERE r.recipe_title LIKE '%${query}%'
ORDER BY r.recipe_id, recipe_rating DESC
LIMIT 30

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