1

I am unable to display mysql rows over 10,000 from mysql within Laravel.

I have read about using chunk but i am unable to get this working, i thought i could be done using the datatable javascript, this is displaying a maximum 10 records per page which is what i want, however it tries to load the whole 10,000 records first before placing them in sets of 10. i have placed my code below.

Thanks in advance

My controller for the page is like this: 

    public function index($bzname)
{
    $user = Auth::guard('business')->user();
    $business_admin= BusinessAdmin::where('user_id', $user->id)->first();
    $business = Business::where('business_name', $bzname)->first();

    $users = User::join('business_users', 'users.id', '=', 'business_users.user_id')
        ->leftJoin('user_login_times', 'users.id', '=', 'user_login_times.user_id')
        ->leftJoin('social_logins', 'users.id', '=', 'social_logins.user_id')
        ->where('business_users.business_id', $business_admin->business_id)
        ->select('users.*', 'social_logins.provider', 'social_logins.social_id', 'user_login_times.login_time')
        ->orderBy('login_time', 'desc')->get();

    $users = $users->unique('id');

    // Get User Roles
    $roles = Role::all();

    $current_year = date('Y');

    $data = [
        'user' => $user,
        'users' => $users,
        'roles' => $roles,
        'bzname' => $bzname,
        'current_year' => $current_year,
        'bzadmin' => $business->owner_name,
    ];

    return View('pages.business_admin.users.show-users', $data);
}

With my user page displaying as:

  <tbody id="users_table">
    @foreach($users as $user)
     <tr>
      <td>{{$loop->index + 1}}</td>
       <td><img src="@if($user->image_url) {{ $user->image_url }} @else /images/faces/empty.png @endif" class="mr-2" alt="image"></td>
        <td class="hidden-xs">{{$user->first_name}}</td>
        <td class="hidden-xs">{{$user->last_name}}</td>
        <td class="hidden-xs" width="100px"><a href="mailto:{{ $user->email }}"          title="email {{ $user->email }}">{{ $user->email }}</a>
         </td>
         <td style="text-transform: capitalize;">{{ $user->gender }}</td>
          <td>{{ $current_year - $user->year }}</td>
          <td style="text-transform: capitalize;">{{ $user->country }}</td>
          <td>
          @if($user->provider == null)
          Email
          @else
         {{ $user->provider }}
         @endif
         </td>
         <td>{{ $user->login_time }}</td>


         <td class="actions" width="180px">
         <a class="btn btn-sm btn-success" href="{{ URL::to('/business/'.$bzname.'/admin/users/' . $user->id) }}"
                                           data-toggle="tooltip" title="View">
                                          {!! trans(('usersmanagement.buttons.show'))!!}
                                        </a>



                                    </td>
                                </tr>
                            @endforeach
                            </tbody>




<script type="text/javascript">
    $(function() {
        $('#user-listing').dataTable({
            dom: 'Bfrtip',
            buttons: [
                'copyHtml5', 'excelHtml5', 'pdfHtml5', 'csvHtml5'
            ],
            "iDisplayLength": 10,
        });
        $('#user-listing_filter').attr('style', 'display: none');
    });
</script>
bax
  • 71
  • 7
  • 1
    play with pagination: https://stackoverflow.com/questions/15229303/is-there-a-way-to-limit-the-result-with-eloquent-orm-of-laravel – ZiTAL Dec 11 '18 at 09:06

4 Answers4

1

Don't.

10k rows in a table itself can crash a browser.

Especially in a table. Reflow itself will take forever!

Your server may run out of memory.

If you get several requests per second, each next request will take longer, and longer.

Paginate https://laravel.com/docs/5.4/pagination

Instead of displaying 10k rows, display 10 and either reload the page on pagination, or work with datatables (for example) to get ajax-pagination.

Unamata Sanatarai
  • 6,475
  • 3
  • 29
  • 51
  • This is what i have been trying to do, i am using datatable javascript to load 10 per page but it tries to pull it all from the database first. also yes it does crash the browser – bax Dec 11 '18 at 09:29
  • can you show your code where you are trying to go this – sumit sharma Dec 11 '18 at 10:06
  • – bax Dec 11 '18 at 10:36
  • @bax You need to go through [documentation](https://datatables.net/manual/ajax) thoroughly and test AJAX option within your code. As represented, you are not using AJAX currently. – Tpojka Dec 11 '18 at 11:03
0

You can use chunking like this :

DB::table('users')->orderBy('id')->chunk(10000, function ($users) {
    foreach ($users as $user) {
        //
    }
});

More information in: Chunking Results in Laravel

mohammad asghari
  • 1,817
  • 1
  • 16
  • 23
0

Here for that much records you should pagination and limit the records.

$users = $users->paginate(50);

or 

$users = $users->simplePaginate(50);

And in blade simply manually create pagination

{{ $users->links() }}

More details for pagination

Remove get() here:

 $users = User::join('business_users', 'users.id', '=', 'business_users.user_id')
->leftJoin('user_login_times', 'users.id', '=', 'user_login_times.user_id')
->leftJoin('social_logins', 'users.id', '=', 'social_logins.user_id')
->where('business_users.business_id', $business_admin->business_id)
->select('users.*', 'social_logins.provider', 'social_logins.social_id', 'user_login_times.login_time')
->orderBy('login_time', 'desc')->paginate(50);

Hope this helps.

kshitij
  • 642
  • 9
  • 17
  • Tried this but get the error: Method Illuminate\Database\Eloquent\Collection::paginate does not exist. – bax Dec 11 '18 at 09:49
  • I think you are using ->get() which makes it a collection and it does not work with collection. So kindly use paginate without get(). @bax – kshitij Dec 11 '18 at 09:55
  • ok thanks, this is not displaying the error now and is loading 50, but not how to load the rest of the data ? – bax Dec 11 '18 at 10:02
  • Are you viewing data in blade? it is loading all just showing first 50. Go to your view page and use this {{ $users->links() }} then you can check the rest records. – kshitij Dec 11 '18 at 10:04
  • if i use this code ->orderBy('login_time', 'desc')->paginate(50); i only see 8 records. – bax Dec 11 '18 at 10:08
  • if i use this $users = $users->paginate(50); i see all 50 – bax Dec 11 '18 at 10:08
  • Can you check what this gives you . $users->total(); @bax – kshitij Dec 11 '18 at 10:15
  • Method Illuminate\Database\Query\Builder::total does not exist. – bax Dec 11 '18 at 10:24
  • Are you using simplePaginate()? use it without paginate() and check that you see all records. – kshitij Dec 11 '18 at 10:29
  • ->orderBy('login_time', 'desc')->paginate(); $users = $users->total(); – bax Dec 11 '18 at 10:35
  • Invalid argument supplied for foreach() – bax Dec 11 '18 at 10:36
  • Do one things use $users as $users = User::join('business_users', 'users.id', '=', 'business_users.user_id') ->leftJoin('user_login_times', 'users.id', '=', 'user_login_times.user_id') ->leftJoin('social_logins', 'users.id', '=', 'social_logins.user_id') ->where('business_users.business_id', $business_admin->business_id) ->select('users.*', 'social_logins.provider', 'social_logins.social_id', 'user_login_times.login_time') ->orderBy('login_time', 'desc'); – kshitij Dec 11 '18 at 10:41
  • Then apply these operations on the results here. If want to see total go for $users->total(); – kshitij Dec 11 '18 at 10:42
  • If still get error, i have to check to check the code or visit that link provided in answer. – kshitij Dec 11 '18 at 10:44
0

I think laratables which are useful if you are trying to use datatable with server side processing

Installation

composer require freshbitsweb/laratables

Create a route just for fetching data

public function mydata()
{
  return Freshbitsweb\Laratables\Laratables::recordsOf(YOUR_Model);
}

for more details laratables:

sumit sharma
  • 698
  • 5
  • 16