如何将自定义SQL内连接语句转换为Laravel Eloquent语法？

Absolutely! Let's break down your SQL query and translate it step by step into clean Laravel Eloquent syntax. Your original query selects rows from the krs table where each kode_matakuliah has the highest bobot value for the specified nim (133341043).

Here's the equivalent Eloquent implementation using the query builder:

$targetNim = 133341043;

// Build the subquery to get max bobot per kode_matakuliah
$maxBobotSubquery = DB::table('krs')
    ->select('kode_matakuliah', DB::raw('MAX(bobot) as bobot_max'))
    ->where('nim', $targetNim)
    ->groupBy('kode_matakuliah');

// Join the main table with the subquery and fetch results
$results = DB::table('krs as a')
    ->joinSub($maxBobotSubquery, 'b', function ($join) {
        $join->on('a.bobot', '=', 'b.bobot_max')
             ->on('a.kode_matakuliah', '=', 'b.kode_matakuliah');
    })
    ->where('a.nim', $targetNim)
    ->select('a.*')
    ->get();

Quick Explanation: #

• Subquery: We first create the inner query that calculates the maximum bobot for each kode_matakuliah tied to your target nim.
• Join: Using joinSub, we link the main krs table (aliased as a) to our subquery (aliased as b) using the two matching conditions from your original SQL.
• Fetch: Finally, we filter by the target nim, select all columns from the main table, and retrieve the results with get().

If you have an Eloquent model for the krs table (e.g., Krs), you can rewrite this using the model for a more expressive syntax:

$targetNim = 133341043;

$maxBobotSubquery = Krs::select('kode_matakuliah', DB::raw('MAX(bobot) as bobot_max'))
    ->where('nim', $targetNim)
    ->groupBy('kode_matakuliah');

$results = Krs::from('krs as a')
    ->joinSub($maxBobotSubquery, 'b', function ($join) {
        $join->on('a.bobot', '=', 'b.bobot_max')
             ->on('a.kode_matakuliah', '=', 'b.kode_matakuliah');
    })
    ->where('a.nim', $targetNim)
    ->select('a.*')
    ->get();

You can verify the generated SQL matches your original query by appending ->toSql() instead of get() to either query chain.

内容的提问来源于stack exchange，提问作者Moch Diki Widianto