php - How to fetch records by 10 on backend side and then load each records using Laravel DataTables?

Question

Hi I am using DataTables when fetching data for users. But users have huge records in it and it is taking time to load. I want to fetch the data from database to only fetch 10 records each on the database. So the scenario would be when you go the users view it will only get 10 records in the database and then load another 10 records when you click next in the database.

UserController.php

// Show Users
    public function index()
    {
        return view('user-list');
    }

// Get Users Using Datatables
    public function getData()
    {

        $users = User::all()->get();

        return Datatables::of($users)
        // EditColumn User ID
        ->editColumn('user_id', function ($user) {
            return '<span class="text-red">$user->user_id</span>';
        })
        ->addColumn('btn_login', function ($user) {
            return $user->is_active . '<a class="btn btn-default" type="button" href="#">Sample</a>';
        })
        ->rawColumns(['btn_login'])
        ->make(true);
    }

web.php

Route::get('userlist', 'UserController@index')->name('user.list');
// Get Users Via DataTables
Route::get('/api/userlist', 'UsersController@getData')->name('api.user.list');

user-list.blade.php

<div class="box">
    <div class="box-header"></div>
    <div class="box-body">
        <div class="table-responsive">
            <table id="users-table" class="table table-hover table-condensed table-striped">
                <thead>
                    <tr>
                        <th>User ID</th>
                        <th>User Name</th>
                        <th>Email</th>
                        <th>Phone Number</th>
                        <th>Login</th>
                    </tr>
                </thead>

            </table>
        </div>
    </div>
    </div>
    <div class="box-footer"></div>
</div>

<script>
$('#users-table').DataTable({
    processing: true,
    responsive: true,
    serverSide: true,
    stateSave: true,
    order: [ [0, "desc"] ],
    ajax: '{{ route("api.user.list") }}',
    columns: [
        {data: 'user_id'},
        {data: 'name'},
        {data: 'email'},
        {
            data: 'phone_number',
            defaultContent: "---"
        },
        {data: 'btn_login'}
    ]
});
</script>

The problem was in this code:

$users = User::all()->get();

Because when I try to use dd($users) it will fetch all the data in users table. What I'm trying to achieve is to fetch only 10 records each and when you click next it will then fetch another 10 records on the back end side.

Is there any way to manipulate this?

I'm new to using Laravel Datatables though and it anybody could help is very much appreciated.

question from:https://stackoverflow.com/questions/65937575/how-to-fetch-records-by-10-on-backend-side-and-then-load-each-records-using-lara

Answer 1

Since your Datatables is serverside you can specify the record length by iDisplayLength option. Just add the following line in your options, like after processing: true,

'iDisplayLength': 10,

Now to make it work instead of getting all the rows and passing the collection you just construct the query and pass it to Datatables. Yes it accepts query without get() and it automatically decides which rows to get based on iDisplayLength and the position pointer which Datatables holds client-side. Also it would be nice to mention select so you don't go select *. So you do something like this:

$users = User::select(['user_id', 'name', 'email', 'phone_number', 'btn_login');

return Datatables::of($users)
            ...

Datatables pagination is on by default so this should work. Also if you want to fetch relations you need to manually join the tables and select specific data. And this is a must because loading relations through eloquent models affects you performance even worse. Lets assume you have a contacts table and the contacts are stored in that table, with user_id to make it belong to the user, but a user may happen not to have a contact so it should be a left join instead of inner join. Here is the query to make it work. You need to first import DB though.

use IlluminateSupportFacadesDB;

$query = DB::table('users as u')
    ->leftJoin('contacts as c', 'u.user_id', '=', 'c.user_id')
    ->select(['u.user_id', 'u.name', 'c.email', 'c.phone_number', 'u.btn_login');

return Datatables::of($query)
            ...

Also now for the table search and order to work you need to pass the correct name of column with their table selector as name to datatables, so the columns options will look like this:

columns: [
    {data: 'user_id', name: 'u.user_id'},
    {data: 'name', name: 'u.name'},
    {data: 'email', name: 'e.email'},
    {
        data: 'phone_number',
        name: 'c.phone_number',
        defaultContent: "---"
    },
    {data: 'btn_login', name: 'u.btn_login'}
]