Laravel-datatables: Relationship 1:1 (inheritance)

[joanebrown]

Hi Yajra,

There is any way to including relationships inside a dataTable? I have a datatable with users, but some of the data of the users are inside a "person" table. (1:1 relationship)

There is any way to get this work:

$users = User::with("person")->select(['user.id', 'person.name', 'person.lastname', 'user.username']);

return Datatables::of($users)

MORE INFO:

• I'm using the latest version of your package: v6.22.7
• The relationship are polymorphic

[yajra]

It's possible on 1:1 relations like on this demo. However, it is not yet fully tested if it works with 1:1 polymorphic relationship. But this works on morph to many relationship so I guess it should work with 1:1? Will verify this too when I had the chance. Thanks!

[joanebrown]

I think doesn't work on 1:1 polymorphic relationship. I used as follow:

// person is the name of the relationships and persons is the name of  the table´s database
$users = User::with("person")->select(['users.id', 'persons.name']);

But I get this error:

exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'persons.name' in 'field list''

Is like this try to get the column persons.name of users insteaf persons table, no?

[yajra]

You need to use:

$users = User::with("person")->select(['users.*']);

And on your js to something like:

 $('#users-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '',
        columns: [
            {data: 'id', name: 'users.id'},
            {data: 'person.name', name: 'person.name'},
        ]
    });

[joanebrown]

Hi Yajra, Is not possible to define the columns on server-side?

BTW, I'm using Laravel 5.2

[yajra]

It's possible if you're using join statements. But when using eager loading, the code above should be followed.

[joanebrown]

And... with eager loading there is any problem with use ->filter? (overriding global filter) (I never used eager loading before).

And another question: With join statements I must have to specify the id and the type of the polymorphic relationship, no?

And, with eager loading I will need to use ->blacklist(['password'])?

What you recommends me?

[yajra]

I think using filter will skip searching on eager loaded models since it will override the global search.

And another question: With join statements I must have to specify the id and the type of the polymorphic relationship, no?

Maybe we can avoid join statements by combining filterColumn api with whereHas.

[joanebrown]

Maybe filter with eager loading should work well.. Are you agree?

Do you recommend mi this approach? The only difference I appreciate is that the definition of the columns is on the client side, right?

[yajra]

Maybe filter with eager loading should work well.. Are you agree?

I think it will work if you will pass true as 2nd arg. filter(func(), true) which enables global search along with custom filter.

The only difference I appreciate is that the definition of the columns is on the client side, right?

Yes, eager loading columns are defined on the client side and the package will handle the necessary query when eager loading is detected.

[joanebrown]

I will try :-)

data format is relationShipName.attribute and in name databaseTable.column :

columns: [
    {data: 'id', name: 'users.id'},
    {data: 'person.name', name: 'persons.name'},
],

No? And inside my filter:

->filter(function ($query) use ($request) {

    if ($request->has('idUser')) {
        $query->whereRaw("users.id like ?", ["%{$request->get('idUser')}%"]);
    }  

    if ($request->has('namePerson')) {
       $query->whereRaw("persons.name like ?", ["%{$request->get('namePerson')}%"]);
    }

}, true)

(Specifying the table in each query)

[joanebrown]

I think using eager loading isn't more secure... The client can manipule the JS code to show the columns he want

[netwrkx]

Hello, please I need help. I'm having difficulty displaying my data in dataTable column.
In using morphTo and morphOne relationships inside dataTable. I can see the complete data on the console but i cant just seems to figure out how to display it.

Model User

    public function userable()
    {
        return $this->morphTo();
    }

Model Admin

    public function user()
    {
        return $this->morphOne('App\Models\Users\User', 'userable');
    }

When I tried

return $dataTableQuery = User::with('userable')     
->select([
 'users.*',
]);

->addColumn('firstName', function ($users) {
   return $users->userable->firstName;
})

columns: [
   {title: 'firstName', data: 'userable.firstName', name: 'userable.firstName'},
]

QLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'users' (SQL: selectusers.* fromusersleft joinusersonusers.userable_id=users.`` where (role= SUPERADMIN_USER) andstatus= 1 andusers.deleted_atis null order byusers.firstNameasc limit 10 offset 0)