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

Question

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 ?

Possible duplicate of How to use pagination with laravel DB::select query — Ankit Das
– Ankit Das, Commented Sep 5, 2019 at 10:08
yes mate, but that is simple query, i need help to convert my query into eloquent or query builder on laravel — Medhy Pradana Putra
– Medhy Pradana Putra, Commented Sep 5, 2019 at 10:10

Prafulla Kumar Sahu · Accepted Answer · 2019-09-05 10:09:30Z

2

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

answered Sep 5, 2019 at 10:09

Prafulla Kumar Sahu

9,75911 gold badges78 silver badges116 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related