如何将含多连接与子查询的SQL语句转换为Laravel ORM及解决字段重复覆盖问题

Hey there, let's work through your Laravel issues one by one.

1. Converting Your SQL Query to Laravel ORM (Including the Subquery Join) #

The tricky part here is handling that subquery in the left join, but Laravel's query builder has a perfect method for this: joinSub(). Here's how to rewrite your query safely and cleanly (note: I fixed the SQL injection risk from your original code by using parameter binding):

$user = Auth::user();

// First, create the subquery that calculates the sum for each kvit_id
$operationsSum = DB::table('operations')
    ->selectRaw('kvit_id, sum(amount * price) as total_sum')
    ->groupBy('kvit_id');

// Now build the main query with all joins
$datas = DB::table('kvits as k')
    // Left join the subquery aliased as 's'
    ->leftJoinSub($operationsSum, 's', function ($join) {
        $join->on('k.id', '=', 's.kvit_id');
    })
    // Add the other left joins with table aliases
    ->leftJoin('users as u', 'k.user_id', '=', 'u.id')
    ->leftJoin('users as h', 'k.hamkor_id', '=', 'h.id')
    ->leftJoin('stores as s2', 'k.store_id', '=', 's2.id')
    // Filter by the authenticated user
    ->where('k.user_id', $user->id)
    // Explicitly select columns with aliases to fix overwrite issues
    ->select([
        'k.id as kvit_id',
        'k.date',
        'u.id as user_id',
        'u.name as user_name',
        'h.id as hamkor_id',
        'h.name as hamkor_name',
        's2.id as store_id',
        's2.name as store_name',
        's2.notes',
        's.total_sum'
    ])
    ->get();

This replicates your original SQL logic entirely, but uses Laravel's ORM methods which are easier to maintain and less error-prone.

2. Fixing the Column Overwrite Problem #

Why this happens: #

When you use select('*') (like your original query does), multiple tables have columns with the same name (most commonly id). Since the result is an associative array/collection, each key can only hold one value. So the last table's id (from stores in your case) overwrites all previous id values from kvits, users, etc.

How to fix it: #

The solution is to explicitly select the columns you need and alias any conflicting names to ensure uniqueness. That's exactly what the select() clause in the code above does. By giving each table's id a unique alias (like kvit_id, user_id, hamkor_id), you make sure all columns are preserved in the result.

If you need additional columns from any table, just add them to the select() array. For example, if both the user and hamkor have an email column, you'd add u.email as user_email and h.email as hamkor_email to avoid conflicts.

内容的提问来源于Stack Exchange，提问作者Hayrulla Melibayev