Rails多表关联查询需求：基于邮编统计企业分类数量

Got it, let's walk through this step by step to build the solution you need. First, let's clarify the table relationships I'm assuming (adjust if your schema differs slightly):

• Postcodes: Has a unique postcode field (or postcode_id as primary key)
• CompaniesPostcode: Junction table linking company_id (foreign key to Companies) and postcode_id (foreign key to Postcodes)
• company_selected_categories: Maps companies to their categories, with company_id and category_id
• Categories: Stores category details, with category_id (primary key), name, and an image field like image_url

Step 1: Fetch Company IDs Matching the Input Postcode #

First, we need to pull all company_ids associated with the user's input postcode by joining the junction table to the Postcodes table:

SELECT cp.company_id
FROM CompaniesPostcode cp
JOIN Postcodes p ON cp.postcode_id = p.postcode_id
-- If your junction table stores raw postcodes instead of IDs, use:
-- JOIN Postcodes p ON cp.postcode = p.postcode
WHERE p.postcode = 'USER_INPUT_POSTCODE'; -- Replace with the user's input

Step 2: Count Category Occurrences for These Companies #

Next, we use those company_ids to count how many times each category_id appears in company_selected_categories:

SELECT 
  csc.category_id,
  COUNT(DISTINCT csc.company_id) AS partner_count -- DISTINCT ensures we count each company once per category
FROM company_selected_categories csc
WHERE csc.company_id IN (
  SELECT cp.company_id
  FROM CompaniesPostcode cp
  JOIN Postcodes p ON cp.postcode_id = p.postcode_id
  WHERE p.postcode = 'USER_INPUT_POSTCODE'
)
GROUP BY csc.category_id
ORDER BY partner_count DESC; -- Optional: Sort by most common categories first

Step 3: Add Category Names & Images for Final Rendering #

Finally, join the result to the Categories table to get the human-readable details you need for rendering:

SELECT 
  cat.category_id,
  cat.name AS category_name,
  cat.image_url AS category_image,
  COUNT(DISTINCT csc.company_id) AS partner_count
FROM Postcodes p
JOIN CompaniesPostcode cp ON p.postcode_id = cp.postcode_id
JOIN company_selected_categories csc ON cp.company_id = csc.company_id
JOIN Categories cat ON csc.category_id = cat.category_id
WHERE p.postcode = 'USER_INPUT_POSTCODE'
GROUP BY cat.category_id, cat.name, cat.image_url
ORDER BY partner_count DESC;

Quick Rendering Example (PHP) #

Once you run the final query, you can loop through the results to render the content. Here's a simple example:

// Assume $pdo is your database connection, $userPostcode is the input
$stmt = $pdo->prepare("
  SELECT 
    cat.category_id,
    cat.name AS category_name,
    cat.image_url AS category_image,
    COUNT(DISTINCT csc.company_id) AS partner_count
  FROM Postcodes p
  JOIN CompaniesPostcode cp ON p.postcode_id = cp.postcode_id
  JOIN company_selected_categories csc ON cp.company_id = csc.company_id
  JOIN Categories cat ON csc.category_id = cat.category_id
  WHERE p.postcode = ?
  GROUP BY cat.category_id, cat.name, cat.image_url
  ORDER BY partner_count DESC
");
$stmt->execute([$userPostcode]);
$categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Render each category card
foreach ($categories as $category) {
  echo "<div class='category-card'>";
  echo "<img src='{$category['category_image']}' alt='{$category['category_name']}' />";
  echo "<h3>{$category['category_name']}</h3>";
  echo "<p>合作企业数量: {$category['partner_count']}</p>";
  echo "</div>";
}

Notes #

• If your schema uses raw postcodes in CompaniesPostcode instead of postcode_id, adjust the JOIN conditions accordingly.
• The DISTINCT in the count is important to avoid overcounting if a company is linked to the same category multiple times in company_selected_categories.

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