php - Laravel GroupBy 使用数据库表列值

Question

我的数据库表中有以下表结构:

+---------+----------------+--------------------------------+---------------------+
| 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 |
+---------+----------------+--------------------------------+---------------------+

我想做的是我只想以表格格式显示上面的内容，但是通过将 payment_type 列分组 NOT HAVING deduction，相同 user_id 的 credit。

输出应该是这样的:

+---------+----------------+--------------------------------+---------------------+
| 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 |
+---------+----------------+--------------------------------+---------------------+

到目前为止我尝试过的代码:

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'));
}

查看:

<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

我如何实现这一点？

编辑 1:有一个 id 列作为主键。

附注:我知道上面的代码不是获取所需结果的正确方法。我还在学习阶段，需要一些帮助。请帮助我实现这一目标。

非常感谢任何帮助。谢谢。

Answer 1

这会让你开始在 SQL 中得到你想要的结果，不幸的是，映射到 Laravel 不是我的强项:)

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)

基本上，它以直接的方式获取值(payable_amount 的总和，与 MAX 聚合的支付类型，因为它未按 中的原样使用>GROUP BY 和最小创建日期)。

然后它按付款类型分组，除非是“扣除”或“信用”，或者如果是其中之一，则按“id”分组。这将使所有的扣除额和抵免额分开，并将其他的合并在一起。

An SQLfiddle to test with .