Laravel subquery

Question

I want to write this query in laravel 5.2

SELECT b.id,
       TotalP,
       b.booking_amount
FROM booking b
LEFT JOIN
  (SELECT sum(amount) AS TotalP,
          booking_id
   FROM payment
   GROUP BY booking_id) AS T ON b.id = T.booking_id
WHERE COALESCE(TotalP, 0) < b.booking_amount

My Question is related to this post. I wrote a query after searching and studying but It is not working and need more constraint

 $result = DB::table('my_booking')
        ->select('booking_name')
        ->leftJoin(DB::raw('(SELECT booking_id,sum(amount) as TotalP FROM `my_payment` GROUP BY booking_id) TotalPayment'), function($join)
        {
            $join->on('my_booking.id', '=', 'TotalPayment.booking_id');
        })
        ->get();

Sql query to get data diffrence of total in 2 tables

Vinoth Kumar · Accepted Answer · 2016-04-07 06:07:57Z

1

You can try this,

$booking_payments = Booking::with('Payment')->find(1);

$total = 0;
foreach($booking_payments->payment as $booking_payment){
$total += $booking_payment->amount;
}

if($booking_payments->booking_amount == $total){
// if the total and booking_amount is equal
}

answered Apr 7, 2016 at 6:07

Vinoth Kumar

4236 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DragonSpirit · Accepted Answer · 2016-09-02 16:34:07Z

1

This should work in Laravel and give you the same exact result as your MySQL query. I moved COALESCE into the subquery select area so that you don't have to write a raw DB where statement in Laravel.

$sql_subquery = "(SELECT COALESCE(SUM(amount),0) AS TotalP,
                          booking_id
                   FROM payment
                   GROUP BY booking_id) AS T";


$result = DB::table('booking AS b')
            ->leftJoin(DB::raw($sql_subquery), 'b.id', '=', 'T.booking_id')
            ->where('T.TotalP','<', 'b.booking_amount')
            ->select('b.id','T.TotalP','b.booking_amount')
            ->get();

answered Sep 2, 2016 at 16:34

DragonSpirit

614 bronze badges

Collectives™ on Stack Overflow

Laravel subquery

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related