如何在Left Join中将一对多关系的右表多行合并为单行

Hey there! I see you're trying to get all product options for an item in one row instead of separate entries—let's get this sorted for you.

The Issue Right Now #

Your current query returns a separate row for each product option because the LEFT JOIN matches each option entry in ordered_product_options to its parent product. To combine these into a single row per product, you'll need to use an aggregate function to concatenate the option names and values.

First, Fix the Typo in Your Join #

Looking at your table structure, the foreign key in ordered_product_options is ordered_produts_id (note the missing 'c' in 'products'—maybe that's a typo? If it's supposed to be ordered_products_id, adjust accordingly). Your current join uses ordered_products__id (double underscore), which is likely causing a mismatch. Let's correct that first.

Modified Query Code #

Here's the adjusted query using GROUP_CONCAT (assuming you're using MySQL, which supports this function) to combine options, plus grouping to ensure one row per product:

$this->orderProduct->leftJoin('ordered_product_options', 'ordered_products._id', '=', 'ordered_product_options.ordered_produts_id')
    ->join('orders', 'ordered_products.orders__id', '=', 'orders._id')
    ->select(
        'ordered_products._id as _id',
        'ordered_products.price as total_amount',
        'ordered_products.name as product_name',
        DB::raw("GROUP_CONCAT(ordered_product_options.name SEPARATOR ',') as option_names"),
        DB::raw("GROUP_CONCAT(ordered_product_options.value_name SEPARATOR ',') as option_values")
    )
    ->groupBy('ordered_products._id', 'ordered_products.price', 'ordered_products.name')
    ->get()
    ->toArray();

What This Does: #

• GROUP_CONCAT() takes all matching name/value_name entries for a product and joins them with commas.
• groupBy() ensures we group results by each unique product (we include price and name too to avoid grouping issues, depending on your SQL mode).
• I renamed the aliases to option_names and option_values (no spaces) to avoid potential syntax errors—you can adjust these if you prefer, but using spaces requires wrapping them in backticks.

Handling Products Without Options #

If a product has no options, GROUP_CONCAT() will return NULL. If you want to show an empty string instead, wrap it in COALESCE:

DB::raw("COALESCE(GROUP_CONCAT(ordered_product_options.name SEPARATOR ','), '') as option_names")

Expected Result #

After running this, you'll get exactly what you're looking for:

431 => array:5 [▼
    "_id" => 665
    "total_amount" => 300.0
    "product_name" => "PT TSHIRT"
    "option_names" => "Size,Color"
    "option_values" => "30,Yellow"
]

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