Laravel-datatables: Issue for Relationship searching and sorting

[aleczzzzz]

Summary of problem or feature request

i have a Posts table with created_by and editted_by columns which links to the Users Table
sorting and searching for created_by and editted_by has an error
"Column not found: 1054 Unknown column 'created_by.firstname' in 'order clause' (SQL: select * from posts where posts.deleted_at is null order by created_by.firstname asc limit 10 offset 0)"

but when i change the relation to user for created_at it works fine

Code snippet of problem

    public function query(Post $model)
    {
        return $model->newQuery()->with('category', 'createdBy');
    }

    protected function getColumns()
    {
        return [
            'category.name' => ['title' => 'Category'],
            'title',
            'excerpt',
            'publish_date',
            'status',
            'created_by.firstname' => ['title' => 'Created By'],
            // 'editted_by.firstname' => ['title' => 'Editted By']
        ];
    }

System details

• Operating System
• PHP Version 7.0
• Laravel Version 5.7
• Laravel-Datatables Version

[yajra]

Can you inspect the ajax response? If I remember correctly, I think it should be something like:

'createdBy.firstname' => [
  'data' => 'created_by.firstname',
  'title' => 'Created By',
]

Where createdBy.firstname is the relation and will map to name attribute on js.
While created_by.firstname will be the json response that will map to data attribute on js.

[aleczzzzz]

changed it to

'createdBy.firstname' => [
  'data' => 'created_by.firstname',
  'title' => 'Created By',
]

same error appears
for sorting :

"Exception Message:↵↵SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created_by.firstname' in 'order clause' (SQL: select * from `posts` where `posts`.`deleted_at` is null order by `created_by`.`firstname` asc limit 10 offset 0)"

for searching

"Exception Message:↵↵SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created_by.firstname' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `posts` where (exists (select * from `categories` where `posts`.`category_id` = `categories`.`id` and LOWER(`categories`.`name`) LIKE %ad% and `categories`.`deleted_at` is null) or LOWER(`posts`.`title`) LIKE %ad% or LOWER(`posts`.`excerpt`) LIKE %ad% or LOWER(`posts`.`publish_date`) LIKE %ad% or LOWER(`posts`.`status`) LIKE %ad% or LOWER(`created_by`.`firstname`) LIKE %ad%) and `posts`.`deleted_at` is null) count_row_table)"

category works fine tho both are belongsTo relations

[yajra]

Does created_by table exists? I think it should be users. How did you define the relationship?

[aleczzzzz]

FIXED

'createdBy.firstname' => [
'name' => 'createdBy.firstname',
'data' => 'created_by.firstname',
'title' => 'Created By',
]

added 'name'

can i ask another question how can i make it fullname?

[yajra]

You can use ->addColumn('fullname', ...) then use it as the data for it.

'createdBy.firstname' => [
'name' => 'createdBy.firstname',
'data' => 'fullname',
'title' => 'Created By',
]

Or if you have mutators on the relationship, you can use the append property and map it using data => created_by.fullname.

[aleczzzzz]

yes i have a fullname accessor so i can't search fullname? or is there a way?

'createdBy.firstname' => ['name' => 'createdBy.firstname', 'data' => 'created_by.fullname', 'title' => 'Created By'],
        ];

[yajra]

No, you can't search on accessor. What you can do to search on lastname is to add a hidden column.

[
  'name' => 'createdBy.firstname',
  'data' => 'fullname',
  'title' => 'Created By',
],
[
  'name' => 'createdBy.lastname',
  'visible' => false
]

[aleczzzzz]

WORKED THANK YOUUU!!!!!