Laravel Eloquent查询获取BBI字段报错及实现方案咨询

Let's break down the issues in your original query and fix them step by step:

Key Issues in the Original Code #

1. Invalid Scalar Subquery: Your subquery was returning two columns (runs and wicket), but a subquery in the SELECT clause must return exactly one value.
2. Missing Parentheses: The subquery wasn't properly enclosed in parentheses before the AS bbi alias.
3. SQL Injection Risk: Directly inserting $request variables into the query string is unsafe and can cause syntax errors if variables contain special characters.
4. Incomplete ORDER BY: The ORDER BY runs clause was missing a direction—we need ASC to prioritize lower runs for the highest wickets, which gives the best bowling figure.

Corrected Query #

Here's the revised query that fixes all these issues:

$bowler = BowlerMaster::selectRaw(
    "tour_id, bowler_id, bowler_name, ball_team, 
     SUM(balls) AS balls, SUM(overs) AS overs,
     (SELECT CONCAT(runs, '/', wicket) 
      FROM bowler_master 
      WHERE tour_id = ? AND bowler_id = ? 
      ORDER BY wicket DESC, runs ASC 
      LIMIT 1) AS bbi",
    [$request->tour_id, $request->player_id]
)
->where($where_array)
->groupBy(['tour_id', 'bowler_id'])
->first();

What Changed? #

• Concatenated BBI Value: Used CONCAT(runs, '/', wicket) to combine the two columns into a single string (e.g., "9/3") that fits the scalar subquery requirement.
• Parameter Binding: Replaced direct variable interpolation with ? placeholders, passing request values as a second argument to selectRaw. This prevents SQL injection and handles special characters safely.
• Proper Subquery Enclosure: Wrapped the subquery in parentheses to ensure valid SQL syntax.
• Clear ORDER BY Direction: Added ASC to runs to get the best possible bowling figure (highest wickets, lowest runs).

Generating the Expected JSON Output #

Once you have the $bowler object, format the response like this:

return response()->json([
    'status' => 200,
    'message' => 'Success',
    'bowler' => $bowler
]);

This will produce valid JSON matching your desired format, with bbi as a string (e.g., "9/3").

Additional Notes #

• If bowler_name or ball_team aren't functionally dependent on bowler_id (i.e., multiple values exist for the same bowler_id), you may need to aggregate them (e.g., MAX(bowler_name)) to comply with MySQL strict mode.
• Using first() directly instead of get()->first() is more efficient, as it stops query execution after retrieving the first row.

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