如何将Estate关联表查询结果合并为单条记录？数据库/程序端方案咨询

Great question! When dealing with joined tables returning duplicate rows for the same parent record (your estate entries here), both database-side and application-side merging have their place. Let’s break down the options, their tradeoffs, and actionable application-layer approaches.

Database-Side Merging #

If your use case is simple—like just displaying comma-separated risk descriptions or related estate IDs—doing the merge directly in SQL is efficient. Most databases offer aggregate functions to concatenate related values into a single string:

• MySQL/MariaDB: Use GROUP_CONCAT()
• PostgreSQL/SQL Server: Use STRING_AGG()

Here’s an example query for MySQL:

SELECT 
  e.estate_id,
  e.name,
  GROUP_CONCAT(DISTINCT er.risk_desc SEPARATOR ', ') AS risk_descriptions,
  GROUP_CONCAT(DISTINCT re.related_estate_id SEPARATOR '; ') AS related_estates
FROM estate e
LEFT JOIN estate_risk er ON e.estate_id = er.estate_id
LEFT JOIN related_estate re ON e.estate_id = re.estate_id
WHERE [your search filters]
GROUP BY e.estate_id, e.name;

Pros of Database-Side Merging #

• Reduces the amount of data transferred from the database to your application (no duplicate parent rows)
• Minimal application code needed for merging
• Faster for simple string concatenation use cases

Cons #

• Database-specific syntax (you’ll need to adjust functions if switching between MySQL and PostgreSQL, for example)
• Limited flexibility if you need to do more than just string concatenation (like formatting each risk item with custom UI elements, or filtering related entries dynamically)
• Length limits (e.g., MySQL’s group_concat_max_len has a default cap that you’ll need to adjust for large datasets)

Application-Side Merging #

If you need to work with the related data as structured lists (not just strings)—like rendering each risk as a separate UI card, or sorting/filtering related estates on the fly—merging in your application is the better choice. Here are two reliable approaches:

Approach 1: Batch Query + Hash Map Mapping #

This is the most efficient application-side method, especially for large datasets:

1. First, fetch all matching estate records from the main table
2. Extract all estate_ids from those records, then batch-fetch all related data from estate_risk and related_estate using IN() clauses
3. Use a hash map (dictionary) to map each estate_id to its related data lists
4. Merge the mapped data back into the original estate records

Example pseudocode (Python):

from collections import defaultdict
import your_db_library

# Step 1: Fetch main estate data matching search criteria
estates = your_db_library.query("""
    SELECT estate_id, name, address 
    FROM estate 
    WHERE [your search filters]
""")

# Step 2: Extract estate IDs and batch-fetch related data
estate_ids = [e["estate_id"] for e in estates]
if not estate_ids:
    final_results = []
else:
    # Fetch risks
    risks = your_db_library.query("""
        SELECT estate_id, risk_desc, risk_level 
        FROM estate_risk 
        WHERE estate_id IN (%s)
    """ % ",".join(["%s"] * len(estate_ids)), tuple(estate_ids))
    
    # Fetch related estates
    related_estates = your_db_library.query("""
        SELECT estate_id, related_estate_id, relation_type 
        FROM related_estate 
        WHERE estate_id IN (%s)
    """ % ",".join(["%s"] * len(estate_ids)), tuple(estate_ids))
    
    # Step 3: Build mapping dictionaries
    risk_map = defaultdict(list)
    for risk in risks:
        risk_map[risk["estate_id"]].append({
            "desc": risk["risk_desc"],
            "level": risk["risk_level"]
        })
    
    related_map = defaultdict(list)
    for rel in related_estates:
        related_map[rel["estate_id"]].append({
            "id": rel["related_estate_id"],
            "type": rel["relation_type"]
        })
    
    # Step 4: Merge data into main estate records
    final_results = []
    for estate in estates:
        merged_estate = estate.copy()
        merged_estate["risks"] = risk_map.get(estate["estate_id"], [])
        merged_estate["related_estates"] = related_map.get(estate["estate_id"], [])
        final_results.append(merged_estate)

Approach 2: Merge Raw Joined Results #

If you prefer to run a single LEFT JOIN query first, you can merge the duplicate rows in your application:

1. Execute the full LEFT JOIN query to get raw results with duplicate estate rows
2. Iterate through the raw results, using a dictionary to track already processed estate_ids
3. For each row, either create a new entry in the dictionary or append related data to the existing entry

Example pseudocode (Python):

import your_db_library

# Step 1: Run the LEFT JOIN query
raw_results = your_db_library.query("""
    SELECT 
        e.estate_id, e.name, e.address,
        er.risk_desc, er.risk_level,
        re.related_estate_id, re.relation_type
    FROM estate e
    LEFT JOIN estate_risk er ON e.estate_id = er.estate_id
    LEFT JOIN related_estate re ON e.estate_id = re.estate_id
    WHERE [your search filters]
""")

# Step 2: Merge duplicate rows
merged = {}
for row in raw_results:
    estate_id = row["estate_id"]
    if estate_id not in merged:
        # Initialize a new entry for this estate
        merged[estate_id] = {
            "estate_id": estate_id,
            "name": row["name"],
            "address": row["address"],
            "risks": [],
            "related_estates": []
        }
    
    # Add risk data if it exists and isn't already in the list
    if row["risk_desc"] is not None:
        risk_entry = {"desc": row["risk_desc"], "level": row["risk_level"]}
        if risk_entry not in merged[estate_id]["risks"]:
            merged[estate_id]["risks"].append(risk_entry)
    
    # Add related estate data if it exists and isn't already in the list
    if row["related_estate_id"] is not None:
        rel_entry = {"id": row["related_estate_id"], "type": row["relation_type"]}
        if rel_entry not in merged[estate_id]["related_estates"]:
            merged[estate_id]["related_estates"].append(rel_entry)

# Convert the dictionary values to a list for your UI
final_results = list(merged.values())

Pros of Application-Side Merging #

• Full flexibility to process related data (format, sort, filter, or transform it for your UI)
• Database-agnostic code (no need to adjust for SQL function differences)
• Easier to extend later (e.g., adding new related tables just requires updating the batch queries or merge logic)

Cons #

• Transfers more data from the database (duplicate parent rows)
• Requires more application code to handle merging
• For very large datasets, you’ll need to implement pagination to avoid memory issues

Final Recommendation #

• Go database-side if you only need simple string concatenation for display, and you’re working with a single database type.
• Go application-side if you need structured access to related data (e.g., rendering individual risk items) or need cross-database compatibility.

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