Knex多表关联查询求助：含自连接、内连接等

Hey there! I see you're stuck on getting the correct comment count per reviewer, plus pulling all those related arrays in one query. Let's break this down and fix the issues step by step.

The Core Problem with Your reviewed Subquery #

Your current reviewed subquery isn't properly correlated to the outer query's reviewer. When you use database.ref('reviews.user_id'), it's just referencing the user_id from the main query's reviews row, but the subquery isn't restricted to count only reviews written by that specific reviewer. Instead, it ends up counting all reviews across the table that match that user_id in a way that doesn't tie to the grouping correctly, leading to a global count instead of per-reviewer.

Fixes & Full Solution #

Let's rewrite the query to handle all your requirements correctly. We'll use correlated subqueries for accurate per-reviewer/per-review aggregates, and array_agg to build the list arrays you need—while avoiding messy Cartesian products from joining multiple one-to-many tables directly.

const reviews = await database('reviews')
  // Join to get the reviewer's user info
  .innerJoin('users', 'users.id', 'reviews.user_id')
  .select(
    // Basic fields from reviews and the reviewer's profile
    {
      username: 'users.username',
      review_pic: 'reviews.review_pic',
      user_pic: 'users.user_pic',
      review_id: 'reviews.id',
      reviewer_id: 'reviews.user_id',
      author_id: 'reviews.author_id',
      review_text: 'reviews.text',
      recipe_id: 'reviews.recipe_id',
    },
    // Total number of reviews written by this reviewer
    database.raw(`(SELECT COUNT(id) FROM reviews WHERE user_id = users.id) AS reviewed`),
    // Total vote sum for this specific review (COALESCE handles 0 votes)
    database.raw(`(SELECT COALESCE(SUM(vote), 0) FROM review_votes WHERE review_id = reviews.id) AS liked`),
    // Array of the reviewer's other reviews (customize fields as needed)
    database.raw(`(SELECT ARRAY_AGG(DISTINCT json_build_object('id', id, 'text', text, 'recipe_id', recipe_id)) FROM reviews WHERE user_id = users.id AND id != reviews.id) AS reviewer_reviews`),
    // Array of recipes created by the reviewer
    database.raw(`(SELECT ARRAY_AGG(DISTINCT json_build_object('id', id, 'title', title, 'description', description)) FROM recipes WHERE user_id = users.id) AS reviewer_recipes`),
    // Array of follower IDs (swap with username if preferred) for the reviewer
    database.raw(`(SELECT ARRAY_AGG(DISTINCT user_id) FROM followings WHERE chef_id = users.id) AS reviewer_followers`)
  )
  // Optional: If you need a follower count alongside the array
  .count({ followers_count: 'followings.id' })
  .leftJoin('followings', 'followings.chef_id', 'users.id')
  // Filter to only reviews for the target recipe
  .where('reviews.recipe_id', +recipe_id)
  // Group by unique identifiers to avoid duplicate rows
  .groupBy(
    'reviews.id',
    'users.id'
  );

Key Breakdowns: #

1. Correlated Subqueries:

   • The reviewed subquery now explicitly references users.id from the outer query, so it counts only reviews written by the current reviewer.
   • COALESCE in the liked subquery ensures you get 0 instead of null when a review has no votes.

2. array_agg for Lists:

   • ARRAY_AGG with DISTINCT prevents duplicate entries (a common issue when joins create unintended Cartesian products).
   • json_build_object lets you shape array items into structured objects with exactly the fields you need—adjust the keys/fields to match your front-end requirements.

3. Avoiding Cartesian Products:

   • Instead of joining recipes and followings directly to the main query (which would multiply rows), we use subqueries to pull aggregated lists. This keeps the main result row count equal to the number of reviews for the target recipe.

4. Grouping:

   • We only group by reviews.id and users.id because all other aggregated fields are tied to either the specific review or the reviewer. This ensures each review appears exactly once in the output.

Quick Adjustments: #

• If you don't need both the follower count and the follower array, remove whichever you don't use to simplify the query.
• Tweak the fields inside json_build_object for reviewer_reviews and reviewer_recipes to include details like description or created_at as needed.
• Test with edge cases (e.g., reviewers with no reviews/recipes/followers) to ensure nulls are handled gracefully—COALESCE(ARRAY_AGG(...), '[]'::json[]) can turn empty arrays into valid JSON if needed.

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