5 months ago by Jonathan Freites

Relates to the lesson Laravel DataTables


I trying to implement what I learned in this course, but I cannot figure it out how handle related models. For example: if I can use the datatable component with my table "customers" whom if related with the "users" table (relation one to one) which one be the correct path to achieve the results?

"data": {
    "table": "customers",
    "displayable": [
   "records": [
     "id": 1,
     "user_id": 16,
     "company": "Empresa Cliente 1",
     "rfc": "jhk232342424243",
     "user": {
        "id": 16,
        "name": "John",
        "last": "Doe",
        "email": "john@doe.com",
        "phone": "5544332211111"

Any ideas?

5 months ago by Rob Francken

Hey Jonathan,

I'm quite new to Laravel, but I was also curious about this.

Since we are using the query builder to retrieve records (with getRecords()), we would need to extend the DataTable controller somehow to inform the builder that we wish to join to another table.

One option would be to override the getRecords method, since this is where the records are retrieved.

So in your case, in the UserController (presuming your users table contains a contact_id column):

    public function getRecords()
        return $this->builder
            ->join('contacts', 'contacts.id', '=', 'users.contact_id')

But, you need to be careful with the displayable columns, because if you do not specify the fully qualified name, you'll get a query exception.

So, something like this should work:

    public function getDisplayableColumns()
        return ['users.id as userId', 'users.name as userName', 'contact.id as contactId', 'contact.name as contactName'];

In practice, you might want to extend the DataTableController to handle the joins, rather than doing it in the UserController, but that should at least get you started :)

5 months ago by Jonathan Freites

Best answer

Hi Rob,

Pretty much your comment was my solution. But instead of modified the getRecords() method I changed the Model that was I using in the builder() method:

    public function builder()
        return Admin::query();

    public function getDisplayableColumns()
        return [
            'id', 'name', 'last', 'email', 'role.name'

So, the Admin Model is extending from User model, and in the boot method I have my query joining both tables. So the Datatable abstract class remains the same and all my relationship stuff are in the models.

I dont know if that is the a correct approach, but works for me! :)

Thank you so much for your comment.

5 months ago by Aspex


5 months ago by shujat khan

columns: [{data: 'id', name: 'posts.id'}, {data: 'user.name', name: 'user.name'}]

columns: [ {data: 'id', name: 'posts.id'}, {data: 'title', name: 'posts.title'}, {data: 'user.name', name: 'user.name'}, {data: 'user.email', name: 'user.email'}, {data: 'created_at', name: 'posts.created_at'}, {data: 'updated_at', name: 'posts.updated_at'} ]

More Information:- http://www.cetpainfotech.com/technology/MS-SQL-Server-Training

6 days ago by Paul P

Found another way (useful if you have conflicting column names in the two tables):

By introducing a new function (in my example with assets and categories table) :

    public function getSortableColumns()
        return [
            'assets.id', 'categories.name as category_name', 'assets.user_id', 'assets.name', 'assets.slug', 'assets.created_at', 'assets.active'

and using this method in the getRecords() method, replacing the getDisplayableColumns() call.

Sign in or create an account to reply to this topic.