Conixs's avatar

Laravel pagination as load more button

Hello everyone,

I'm trying to use DataTables CDN to display my data without pagination, but with a load more button. Thus, I found out about it on website and tried to implement it as follow:

<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
<script type="text/javascript" src="https://gyrocode.github.io/jquery-datatables-pageLoadMore/1.0.1/js/dataTables.pageLoadMore.min.js"></script>

<script type="text/javascript">
    $(document).ready(function (){
        var table = $('.table-data2').DataTable({
          dom: 'frt',
          ajax: '{{route('users.index')}}',
          drawCallback: function(){
             if($('.au-btn-load').is(':visible')){
                $('html, body').animate({
                   scrollTop: $('.au-btn-load').offset().top
                }, 1000);
             }

             $('.au-btn-load').toggle(this.api().page.hasMore());
          }
        });

        $('.au-btn-load').on('click', function(){
          table.page.loadMore();
        });
    });
</script>

However, it is showing Invalid JSON error. Therefore, I tried to build a resource to send back the data as JSON without success.

class UserResource extends JsonResource
{
    /**
     * Transform the resource into an array.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return array
     */
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'email' => $this->email,
        ];
    }
}

class UserController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return Response
     */
    public function index()
    {
        $contributors = Contributor::with('user')->withTrashed()->get();
        return view('users.index', ['contributors' => UserResource::collection($contributors)]);
    }
}

Could someone help me?

0 likes
7 replies
bobbybouwmann's avatar
bobbybouwmann
7 years ago
Best Answer
Level 88

Looking into the documentation of DataTables I see that they expect a different format in JSON than Laravel is sending back by Laravel. Probably because of that it's not working.

So your current response is this:

{
    "data": [
        {
            "id": 1,
            "name": "Eladio Schroeder Sr.",
            "email": "[email protected]",
        },
        {
            "id": 2,
            "name": "Liliana Mayert",
            "email": "[email protected]",
        }
    ]
}

However datatables expects this

{
    "data": [
        {
            1,
            "Eladio Schroeder Sr.",
            "[email protected]",
        },
        {
            2,
            "Liliana Mayert",
            "[email protected]",
        }
    ]
}

So basically no keys and just enough lines for the table to fill.

So you can update your UserResource to this and it should work

public function toArray($request)
{
    return [
        $this->id
        $this->name
        $this->email
    ];
}

Let me know if that works for you!

1 like
Conixs's avatar

@BOBBYBOUWMANN - I was able to achieve the result without using a resource.

public function index(Request $request)
    {
        if($request->ajax()) {
            $model = Contributor::with('user')->withTrashed();
            return $this->dataTable
                ->eloquent($model)
                ->addColumn('name', function (Contributor $contributor) {
                    return '<div class="table-data__info"><h6>'.$contributor->name.'</h6><span><a>'.$contributor->enrollment.'</a></span></div>';
                })
                ->addColumn('company', function (Contributor $contributor) {
                    return $contributor->company->name;
                })
                ->addColumn('city', function (Contributor $contributor) {
                    return $contributor->company->address->city;
                })
                ->addColumn('role', function (Contributor $contributor) {
                    if($contributor->role == 'Admin')
                        return '<span class="role admin">'.$contributor->role.'</span>';
                    elseif($contributor->role == 'Client')
                        return '<span class="role client">'.$contributor->role.'</span>';
                    elseif($contributor->role == 'User')
                        return '<span class="role user">'.$contributor->role.'</span>';
                    else
                        return '<span class="role member">'.$contributor->role.'</span>';
                })
                ->addColumn('status', function (Contributor $contributor) {
                    if($contributor->trashed())
                        return '<span class="status--denied">INACTIVE</span>';
                    return '<span class="status--process">ACTIVE</span>';
                })
                ->addColumn('edit', function (Contributor $contributor) {
                    return '<div class="table-data-feature"><a href="'.route('users.edit', ['contributor' => $contributor]).'" class="item" data-toggle="tooltip" data-placement="top" title="" data-original-title="Edit"><i class="zmdi zmdi-edit"></i></a></div>';
                })
                ->rawColumns(['name', 'role', 'status', 'edit'])
                ->make(true);
        }
}

However, the filters and the search are not working! And I can't found a way to achieve my goal.

Conixs's avatar

@BOBBYBOUWMANN - I added the filter to the query, but it didn't work.

->filter(function ($query) {
    if (request()->has('name')) {
        $query->where('name', 'like', "%" . request('name') . "%");
    }
})
bobbybouwmann's avatar

"It didn't work"? I really can't do anything with that. Do you submit a name field?

I have zero experience with this package, but the examples from the docs are easy to copy and paste. You should be able to get it working from the examples.

Conixs's avatar

@BOBBYBOUWMANN - I'm thinking that the problem is with the relationship Contributor::with('user'). Now, it is showing a SQLSTATE Error - Unknown column 'company.name' in 'where clause'

It is my first time using this package in Laravel.

Conixs's avatar

I changed the model to $model = Contributor::with('user')->withTrashed()->select('contributors.*'); and now it is working for 3 columns, need to find out how to make it works for company->name and city!

Thank you for the help.

Please or to participate in this conversation.