2 months ago by Jonathan Freites

Relates to the lesson Laravel DataTables

Hi,

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": [
       "id",
       "user_id",
       "company",
      "rfc"
    ],
   "records": [
  {
     "id": 1,
     "user_id": 16,
     "company": "Empresa Cliente 1",
     "rfc": "jhk232342424243",
     "user": {
        "id": 16,
        "name": "John",
        "last": "Doe",
        "email": "[email protected]",
        "phone": "5544332211111"
     }
  }
 ]
}

Any ideas?

2 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')
            ->get($this->getDisplayableColumns());
    }

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 :)

2 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.

2 months ago by Aspex

solved

2 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

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