problem after sorting many-to-many relation model that have same column name

[MehranLabour]

i have below tables

users

id
name
last_name

categories

id
name

category_user

user_id
category_id

user can have many categories, i mean they have Many to Many relationship.

in UserController.php i have below code

public function GetUsersData(){
$users = User::with(['categories'])->select('users.*');
 return Datatables::of($users)
 ->addColumn('name_of_user_category', function (User $user) {
     return $user->categories->map(function($category) {
         return $category->name;
     })->implode('<br>');
 })
 ->toJson();
}

and in users.balde.php i have below javascript code:

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

after first loading data table every thing works fine but when i wanna sort table by name_of_user_category column, the other column that contains name of user, it fills with name of category too, some things goes wrong here, because user and cateegory have a column with same name this problem will happen after sorting the column that contain name of category. i had hard time to find a way to sort column for many to many relation ship column but now i have new problem.

i tried to share my problem as clear as i can, if you need more info please let me know.

thanks in advance

System details

• Operating System : Ubuntu
• PHP Version:7.2
• Laravel Version: 6.2
• Laravel-Datatables Version:"^9.10"

[yajra]

Thanks for reporting, was able to replicate the issue.

[yajra]

ATM, you should disable ordering for this relation. If sorting is required, you would need to use join statements query and write it manually.

[yajra]

I just recalled now. Sorting on BelongsToMany is not yet fully supported. I tried supporting it but it will still yield to a wrong output due to SQL behavior. For this instance, if a user has 2 categories, it will yield to 2 entries since we are sorting on a many to many relationship.

Initial load will give 2 results.

Sorting with category name will give us 3

This is the part where I paused on supporting many-to-many relationship since I don't have a use case for it yet and the output would still be wrong. If you can, please do not hesitate to submit a PR.

[yajra]

To give you an idea how my example code works, just update https://github.com/yajra/laravel-datatables/blob/9.0/src/EloquentDataTable.php#L158

$lastQuery->addSelect($table . '.' . $relationColumn);

to

$lastQuery->addSelect($table . '.' . $relationColumn . ' as ' . $table . '_' . $relationColumn );

[yajra]

Lastly, just a random tips :)

->addColumn('name_of_user_category', function (User $user) {
  return $user->categories->implode('name', '<br>');
})

Another way is via render:

{data: 'categories', name: 'name_of_user_category.name', render: "[<br>].name"}

[MehranLabour]

Aha! thank you @yajra for your tips.

[codewise-nicolas]

I just recalled now. Sorting on BelongsToMany is not yet fully supported. I tried supporting it but it will still yield to a wrong output due to SQL behavior. For this instance, if a user has 2 categories, it will yield to 2 entries since we are sorting on a many to many relationship.

Thanks for this. I was going crazy trying to get a belongsToMany to work for sorting. I will just turn it off for now. Possibly a debug log message if it encounters such a relation to warn the developer that its not supported (Ive found maybe 4 or 5 different issues here for the same problem)