Laravel GroupBy using database table column values

Question

I am having the following table structure in my database table:

+---------+----------------+--------------------------------+---------------------+
| user_id | payable_amount | payment_type                   | created_at          |
+---------+----------------+--------------------------------+---------------------+
|      10 |         450.00 | order_payment                  | 2016-03-28 08:21:14 |
|       3 |          14.00 | moderator_commission           | 2016-03-28 08:21:14 |
|      10 |          17.00 | principal_moderator_commission | 2016-03-28 08:21:14 |
|       4 |          28.00 | affiliate_commission           | 2016-03-28 08:21:14 |
|      10 |         700.00 | order_payment                  | 2016-03-28 08:21:14 |
|       3 |          22.00 | moderator_commission           | 2016-03-28 08:21:15 |
|      10 |          26.00 | principal_moderator_commission | 2016-03-28 08:21:15 |
|       4 |          44.00 | affiliate_commission           | 2016-03-28 08:21:15 |
|      10 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|       8 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|      11 |         150.00 | shipping                       | 2016-03-28 08:21:17 |
|       7 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|      10 |         500.00 | deduction                      | 2016-03-28 09:59:22 |
|      10 |         200.00 | deduction                      | 2016-03-28 10:46:39 |
|      10 |        2500.00 | credit                         | 2016-03-28 10:54:32 |
+---------+----------------+--------------------------------+---------------------+

What I am trying to do is I want to display the above in tabular format only but by grouping the column payment_type NOT HAVING the value of deduction, credit of the same user_id.

Output should be like this:

+---------+----------------+--------------------------------+---------------------+
| user_id | payable_amount | payment_type                   | created_at          |
+---------+----------------+--------------------------------+---------------------+
|      10 |        1150.00 | order_payment                  | 2016-03-28 08:21:14 |
|       3 |          36.00 | moderator_commission           | 2016-03-28 08:21:14 |
|      10 |          43.00 | principal_moderator_commission | 2016-03-28 08:21:14 |
|       4 |          72.00 | affiliate_commission           | 2016-03-28 08:21:14 |
       10 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|       8 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|      11 |         150.00 | shipping                       | 2016-03-28 08:21:17 |
|       7 |          75.00 | shipping                       | 2016-03-28 08:21:17 |
|      10 |         500.00 | deduction                      | 2016-03-28 09:59:22 |
|      10 |         200.00 | deduction                      | 2016-03-28 10:46:39 |
|      10 |        2500.00 | credit                         | 2016-03-28 10:54:32 |
+---------+----------------+--------------------------------+---------------------+

The code that I have tried so far:

Controller:

public function fetchUser($userCode)
{
    $member = User::where('code', $userCode)->first();


    $allOrderUserPayments = OrderUserPayment::where('user_id', $member->id)
                            ->groupBy('payment_type')->get();

    return view('admin.orders.payments.user', compact('allOrderUserPayments'));
}

View:

<tr>
     <th>Details</th>
     <th>Total Wt</th>
     <th>Pay Wt</th>
     <th>Non - Pay Wt</th>
     <th>Total Amt</th>
     <th>Pay Amt</th>
     <th>Non - Pay Amt</th>
     <th>Credit</th>
     <th>Deduction</th>
     <th>Payment</th>
     <th>Balance</th>
     <th>Notes</th>
     <th>Created At</th>
</tr>
@foreach($allOrderUserPayments as $key => $order)
    <?php $credits = $balance = 0.00; ?>

    @if($order->payment_type === 'order_payment')
        <?php
        $totalOrderPaymentAmount = 0.00;
        $ordersOrderPayment = App\OrderUserPayment::where('order_code', $order->order_code)
                  ->where('user_id', $order->user_id)
                  ->where('payment_type', 'order_payment')
                  ->selectRaw('*, SUM(payable_amount) AS totalAmount')
                  ->first();

        $ordersPayableAmount = App\OrderUserPayment::where('order_code', $order->order_code)
                  ->where('user_id', $order->user_id)
                  ->where('payment_type', 'order_payment')
                  ->where('payment_payability_status', '!=', 'Non-Payable')
                  ->selectRaw('*, SUM(payable_amount) AS totalPayableAmount')
                  ->first();
         ?>
         <tr>
             <td>{{ $order->order_code }} / Order Payment</td>
             <td></td>
             <td></td>
             <td></td>
             <td>{{ $ordersOrderPayment->totalAmount }}</td>
             <td>{{ $ordersPayableAmount->totalPayableAmount }}</td>
             <td>{{ number_format($ordersOrderPayment->totalAmount - $ordersPayableAmount->totalPayableAmount, 2) }}</td>
             <td>{{ $ordersOrderPayment->totalAmount }}</td>
             <td></td>
             <td></td>
             <td>{{ $totalCredits += $ordersOrderPayment->totalAmount }}</td>
             <td></td>
             <td></td>
         </tr>
     @endif

     @if($order->payment_type === 'shipping')
         <?php
         $invoicer = App\OrderInvoicerShipping::where('invoicer_id', $order->user_id)->where('order_code', $order->order_code)->first();
         ?>
         <tr>
             <td>{{ $order->order_code }} / Shipping</td>
             <td>{{ $invoicer !== null ? $invoicer->weight : '' }}</td>
             <td></td>
             <td></td>
             <td>{{ $order->payable_amount }}</td>
             <td>{{ $order->payable_amount }}</td>
             <td></td>
             <td><?php $totalCredits += ($credits += $order->payable_amount); ?>{{ $credits = $order->payable_amount }}</td>
             <td></td>
             <td></td>
             @if($order->payment_payability_status !== 'Non-Payable')
                 <td>{{ $balance += $totalCredits }}</td>
             @else
                 <td></td>
             @endif
             <td></td>
             <td>{{ $order->payment_updated_at !== null ? $order->payment_updated_at->timezone('Asia/Kolkata') : '' }}</td>
         </tr>
     @endif

     @if($order->payment_type === 'principal_moderator_commission')
         <?php
         $ordersOrderPaymentPM = App\OrderUserPayment::where('order_code', $order->order_code)
                  ->where('user_id', $order->user_id)
                  ->where('payment_type', 'principal_moderator_commission')
                  ->selectRaw('*, SUM(payable_amount) AS totalAmount')
                  ->first();

          $ordersPayableAmountPM = App\OrderUserPayment::where('order_code', $order->order_code)
                  ->where('user_id', $order->user_id)
                  ->where('payment_type', 'principal_moderator_commission')
                  ->where('payment_payability_status', '!=', 'Non-Payable')
                  ->selectRaw('*, SUM(payable_amount) AS totalPayableAmount')
                  ->first();
           ?>
           <tr>
               <td>{{ $order->order_code }} / Principal Moderator</td>
               <td></td>
               <td></td>
               <td></td>
               <td>{{ $ordersOrderPaymentPM->totalAmount }}</td>
               <td>{{ $ordersPayableAmountPM->totalPayableAmount }}</td>
               <td>{{ number_format($ordersOrderPaymentPM->totalAmount - $ordersPayableAmountPM->totalPayableAmount, 2) }}</td>
               <td>{{ $ordersOrderPaymentPM->totalAmount }}</td>
               <td></td>
               <td></td>
               <td>{{ $totalCredits += $ordersOrderPaymentPM->totalAmount }}</td>
               <td></td>
               <td></td>
           </tr>
       @endif

       @if($order->payment_type === 'deduction')
           <tr>
              <td>Deduction</td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
              <td>{{ $order->payable_amount }}</td>
              <td></td>
              <td>{{ $totalCredits -= $order->payable_amount }}</td>
              <td></td>
              <td></td>
           </tr>
       @elseif($order->payment_type === 'credit')
           <tr>
               <td>Credit</td>
               <td></td>
               <td></td>
               <td></td>
               <td></td>
               <td></td>
               <td></td>
               <td>{{ $order->payable_amount }}</td>
               <td></td>
               <td></td>
               <td>{{ $totalCredits += $order->payable_amount }}</td>
               <td></td>
               <td></td>
           </tr>
       @endif
   @endforeach

How do I achieve this ?

EDIT 1: There is a id column in as a primary key.

P.S.: I know the above code is not the correct way to do fetch the desired result. I am still at the learning stage and I need some help. Kindly help me out in achieving this.

Any help is highly appreciated. Thank You.

Oppss.. I forgot to add that in my table code above... Yes, it is there - the id column in the table.. — Saiyan Prince
– Saiyan Prince, Commented Mar 28, 2016 at 11:42
Would an SQL only solution to get the desired result be interesting? Or just a complete laravel solution? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 28, 2016 at 11:46
Preferrable is Laravel solution, but if you can give me the desired solution with SQL only, I will be able to try to tweak it on my own.. I would like to see your solution.. Please give. — Saiyan Prince
– Saiyan Prince, Commented Mar 28, 2016 at 11:49

Joachim Isaksson · Accepted Answer · 2016-03-28 11:54:39Z

1

This will get you started with your desired result in SQL, the mapping to Laravel is unfortunately not my strong suit :)

SELECT user_id, SUM(payable_amount) payable_amount, 
       MAX(payment_type) payment_type, MIN(created_at) created_at
FROM orderuserpayment
GROUP BY user_id, CASE WHEN payment_type IN ('deduction', 'credit') 
                       THEN id ELSE payment_type END
ORDER BY MIN(id)

Basically, it gets the values in a straight forward manner (the sum of payable_amount, the payment type aggregated with MAX since it's not used as is in the GROUP BY and the minimum created date).

It then groups by payment type unless it's 'deduction' or 'credit', or by 'id' if it is one of them. That will keep all the deductions and credits separate and pool the others together.

An SQLfiddle to test with.

answered Mar 28, 2016 at 11:54

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

1 Comment

Saiyan Prince Over a year ago

Thank you for the solution.. It worked like a charm... (Y)

Collectives™ on Stack Overflow

Laravel GroupBy using database table column values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related