jeudi 9 juillet 2020

GroupBy not working in eager loaded function laravel 5.1

I am executing a query where i need to get some data from child table and sum them based on a column. here is my query:

        $studentPaymentDetails = Student::select(
                'student_id',
                'student_name',
                'system_generated_student_id'
            )
                ->with(
                    [
                        'received_payment' => function ($query) use ($dateFrom, $dateTo) {
                            $query->select(
                                'received_student_payment_id',
                                'student_id',
                                'received_amount_by_bank',
                                'received_time'
                                // DB::raw('sum(received_amount_by_bank) as received_amount_by_bank'),
                                // DB::raw("DATE_FORMAT(received_time,'%m') as monthKey")
                            )
                            ->whereBetween('received_time', [$dateFrom, $dateTo])
                            // ->groupBy('monthKey')
                            ->orderBy('received_time');
                        },
                        'received_payment_sub_head' => function($query) use ($dateFrom, $dateTo) {
                            $query->select(
                                'received_student_payment_sub_head_id',
                                'student_payment_id',
                                // 'payment_amount',
                                DB::raw('sum(payment_amount) as payment_amount'),
                                'student_id',
                                'create_time'
                            )
                            ->whereBetween('create_time', [$dateFrom, $dateTo])
                            ->groupBy('student_payment_id');
                        }
                    ]
                )
                ->if($Class, 'class_id', "=", $Class)
                ->if($Group, 'stu_group', "=", $Group)
                ->if($Section, 'section_id', "=", $Section)
                ->if($BranchShiftVersion, 'institute_branch_version_id', "=", $BranchShiftVersion)
                ->if($Branch, 'branch', "=", $Branch)
                ->if($Year, 'acc_year', "=", $Year)
                ->where('status', 1)
                ->orderBy('roll_no')
                ->get();

But when i am using Group by and sum a column it is showing blank/last row. In received_payment eager loaded model, when i use DB:raw line to sum received_amount_by_bank and then group them with monthKey, it is showing blank. As well as in received_payment_sub_head eager loaded model, it only showing the last row. I not getting any idea why it is acting like that. Can anyone point me out!

My application is developed in laravel 5.1



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire