CODEWITHSUNDEEP
mysqlsqldatabaselaravellaravel-query-builder
Medhy Pradana Putra
Medhy Pradana Putra

Reputation: 25

How to use pagination on DB::select(query) - Laravel

i have some query and i just know how to query on mysql (phpmyadmin)

i got information if using DB::select we cannot to use paginate, so we need change to DB::table(some query) to using pagination.

but the problem is i am confuse how to convert my query into DB::table(some query)

Here is my code 

$daily = DB::select("
            SELECT
                employees.employee_name,
                COUNT(DISTINCT DATE(attendance.attendance_datetime)) as jumlah,
                locations.location_name,
                TIME(MIN(attendance.attendance_datetime)) as check_in,
                CASE
                    WHEN ISNULL(TIME(MIN(attendance.attendance_datetime))) THEN attendance_absence.remarks
                    WHEN TIME(MIN(attendance.attendance_datetime)) > '08:05:00' THEN (SELECT TIMEDIFF('08:05:00', MIN(TIME(attendance_datetime))))
                    WHEN TIME(MIN(attendance.attendance_datetime)) <= '08:05:00' THEN 'Good'
                    ELSE 'No Record'
                END as detail_telat,
                attendance_absence.remarks as remarks
            FROM
                employees
            LEFT JOIN attendance ON employees.employee_name = attendance.attendance_name AND DATE(attendance.attendance_datetime) = '$date'
            LEFT JOIN locations ON employees.location_id = locations.id
            LEFT JOIN attendance_absence ON  attendance_absence.employee_name = employees.employee_name AND attendance_absence.absences_date = '$date'
                WHERE locations.location_name LIKE '%'
            GROUP BY employees.employee_name
            ORDER BY employees.employee_name
        ")->paginate(3);

please help me to convert my query into eloquent or query builder, or any suggestion ?

Upvotes: 1

Views: 2521

Answers (1)

Prafulla Kumar Sahu
Prafulla Kumar Sahu

Reputation: 9693

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Check documentation

Upvotes: 2

Related Questions