Paginate Query Builder request

Can I paginate a Query Builder request like below ?

Route::get('users/duplicates/names', fn () => DB::connection('myconn')->select(
    'SELECT
        LOWER(unaccent(first_name)) AS fn,
        LOWER(unaccent(last_name)) AS ln,
        COUNT(*) as count,
        ARRAY_AGG(id) AS ids
    FROM myschema.users
    WHERE (last_name = \'\') IS FALSE
    GROUP BY
        LOWER(unaccent(first_name)),
        LOWER(unaccent(last_name))
    HAVING count(*)>1'
));

tykus

4 years ago

Best Answer

Level 104

Convert to a Query Builder

Route::get('users/duplicates/names', function () {
	return DB::connection('myconn')
        ->table('myshema.users')
        ->selectRaw("LOWER(unaccent(first_name)) AS fn, LOWER(unaccent(last_name)) AS ln, COUNT(*) as count, ARRAY_AGG(id) AS ids")
        ->whereRaw("last_name = \'\') IS FALSE")
        ->groupByRaw("LOWER(unaccent(first_name)), LOWER(unaccent(last_name))")
        ->havingRaw("count(*)>1")
        ->paginate();
});

(I haven't checked the SQL expressions)

1 like

devondahon

4 years ago

Level 3

@tykus Thanks a lot !

Route::get(
    'users/duplicates/names',
    fn () => DB::connection('myconn')
        ->table('myschema.users')
        ->selectRaw('
            LOWER(unaccent(first_name)) AS fn,
            LOWER(unaccent(last_name)) AS ln,
            COUNT(*) as count,
            ARRAY_AGG(id) AS ids
        ')
        ->whereRaw("(last_name = '') IS FALSE")
        ->groupByRaw('
            LOWER(unaccent(first_name)),
            LOWER(unaccent(last_name))
        ')
        ->havingRaw('count(*)>1')
        ->paginate()
);

tykus

4 years ago

Level 104

@devondahon 👍

Aside, you don't have to use short-closures just because you can!

devondahon

4 years ago

Level 3

@tykus Why not ?

tykus

4 years ago

Level 104

@devondahon readability.

Please or to participate in this conversation.