Transform SQL Query into Laravel Eloquent Format

Data and Databases
1

I am currently working with multiple tables that involve a complex relationship, which I am attempting to streamline while simultaneously needing to generate reports through Laravel. I can retrieve data using the following MySQL query:

SELECT
    customers.id,
    customers.customer_name,
    SUM(shipments.balance) AS totalBalance
FROM customers
LEFT JOIN shipments 
    ON customers.id = shipments.bill_to
    AND shipments.balance > (SELECT IFNULL(SUM(payments_distribution.amount), 0)
                             FROM payments_distribution
                             WHERE payments_distribution.shipment_id = pro_number)
GROUP BY customers.id, customers.customer_name
ORDER BY totalBalance DESC
LIMIT 5;

I’m uncertain how to accurately convert it into Laravel Eloquent’s whereRaw or DB::raw constructs because my previous efforts have not succeeded. Here’s the closest attempt I’ve made:

DB::table('customers')
    ->select('customers.id', 'customers.customer_name', DB::raw('SUM(shipments.balance) AS totalBalance'))
    ->leftJoin(
        'shipments', 
        function($join) {
            $join->on('customers.id', '=', 'shipments.bill_to')
                ->where('shipments.balance', '>', 
                    DB::raw('(SELECT IFNULL(SUM(payments_distribution.amount), 0)
                              FROM payments_distribution
                              WHERE payments_distribution.shipment_id = shipments.pro_number)')
                );
        }
    )
    ->groupBy('customers.id', 'customers.customer_name')
    ->orderBy('totalBalance', 'DESC')
    ->limit(5)
    ->get();

However, when using this code, I encountered:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'on clause'...

Removing the problematic join condition gets me partial results but not in the desired order. Is there additional information I should provide to help resolve this issue effectively?

2

Hey! It might be related to how you’re referring to the shipments.pro_number inside DB::raw. Try adding the table name prefix in your subquery: payments_distribution.shipment_id = shipments.pro_number. Sometimes, eloquent needs clear references to avoid column alias confusions. give it a shot and see if dat helps!