Laravel多表查询：如何在获取订单及订单总数时指定订单颜色？

Hey there! Let's figure out how to tweak your existing order query to add color filtering while keeping track of total piece counts. First, I'll make a safe assumption about your database setup (since you didn't share full schema details): I'm guessing the color field lives in the order_piece table—since individual line items in an order usually have their own color. If it's actually in the order table instead, just swap the table name in the examples below!

1. Scenario: Filter orders that contain the specified color, and count total pieces per order #

If your goal is to find all orders that have at least one item of the specified color, and calculate the total number of pieces for the entire order (not just the colored items), you'll need to use a WHERE EXISTS subquery. This avoids filtering out other color items in the order while still ensuring the order meets your color requirement:

$targetColor = $request->input('color'); // Grab the color parameter from the request

$orders = Order::select('order.*', DB::raw('SUM(order_piece.piece) AS total_pieces'))
    ->join('order_piece', 'order_piece.order_id', '=', 'order.id')
    ->where('order.collection_id', $request->model_id)
    // Add color filter: check that the order has at least one piece of the target color
    ->whereExists(function ($query) use ($targetColor) {
        $query->select(DB::raw(1))
            ->from('order_piece')
            ->whereColumn('order_piece.order_id', 'order.id')
            ->where('order_piece.color', $targetColor);
    })
    ->groupBy('order.id') // Note: For Laravel 5.7+, you may need to add all non-aggregated order fields here if strict SQL mode is enabled
    ->get();

2. Scenario: Filter only the specified color pieces, and count those per order #

If you want to only calculate the total number of pieces of the specified color per order (ignoring other colors in the same order), you can add a direct WHERE clause to filter the order_piece table:

$targetColor = $request->input('color');

$orders = Order::select('order.*', 'order_piece.color', DB::raw('SUM(order_piece.piece) AS color_specific_pieces'))
    ->join('order_piece', 'order_piece.order_id', '=', 'order.id')
    ->where('order.collection_id', $request->model_id)
    ->where('order_piece.color', $targetColor) // Filter directly for the target color
    ->groupBy('order.id', 'order_piece.color')
    ->get();

Key Notes #

• Strict SQL Mode Handling: If your Laravel app uses strict database mode (default for newer versions), the groupBy clause must include all non-aggregated fields from your select statement. Instead of using order.*, consider listing specific fields (like order.id, order.order_number) to avoid errors.
• Parameter Validation: Always add a check for the color parameter before using it—this prevents invalid or empty queries:

  if (!empty($targetColor)) {
      // Apply your color filtering logic here
  }
  

• Order-Level Color: If the color is a property of the entire order (stored in the order table), replace the color filter with ->where('order.color', $targetColor) instead of using a subquery.

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