Laravel-datatables: Search in relation with Concat Columns

[salihklc]

Summary of problem or feature request

Hello Yajra I'm using your super awesome package. I'm also using service implementation. Everything works like charm but I'm stuck in concat column search which came from relation (with method of Eloquent). Example : I have created_by column in all table which is related with users table, I get the data from users whose are first_name and last_name (with concat) everything fine until the search. When I try to search by created_by error say there is no full_name column in users table. I know there isn't but I write mutator for this in the User model set-getfullNameAttribute functions still not working. Also tried this example got same error, only difference is unknown users.first_name column. This problem probably not caused by your package but I'm here :)
Thanks.

Code snippet of problem

 public function query()
    {
        return $this->applyScopes(OneOfMyModel::with(
            [
                "updatedBy" => function ($table) {
                    return $table->select(["id", "first_name", "last_name"]);
                },
                "category" => function ($table) {
                    return $table->select(["id", "name"]);
                }
            ]
        ));
    }

 /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
. . . . ...
            ["title" => "Updated By", "data" => "updated_by.full_name", "name" => "updatedBy.full_name"],
        ];
    }


class User extends EloquentUser implements UserInterface
{
    use Notifiable, MorphicRelationship, SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'first_name', 'last_name', 'email', 'password', 'reset_password_code', 'slug'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password'
    ];

    protected $appends = ["full_name"];


    public function getFullNameAttribute()
    {
        return $this->attributes['first_name'] . " " . $this->attributes["last_name"];

    }

    public function setFullNameAttribute($value)
    {
        $this->attributes["full_name"] = $this->attributes['first_name'] . " " . $this->attributes["last_name"];
    }

}

• Operating System linux 16.04
• PHP Version 7.0~
• Laravel Version 5.3
• Laravel-Datatables Version 6.0~

[rzb]

I had the same use case here. That's how I solved it:

In your service class:

public function ajax()
{
    return $this->datatables
                       ->eloquent($this->query())
                       // overriding global search due to 'fullname' dynamic attribute
                       ->filter(function($query) {
                       if ($this->request()->has('search.value')) {
                           $query
                               // you'll have to declare your other filters here so that they are used in conjunction
                               ->where(function($q) {
                                   $keyword = $this->request()->input('search.value');
                                   $q
                                       ->where('foo', 'like', "%{$keyword}%")
                                       ->orWhereHas('relationship.with.user', function($q) use ($keyword) {
                                           $q->whereRaw("CONCAT(firstname,' ',lastname) like ?", ["%{$keyword}%"]);
                                       })
                                       ->orWhereHas('some.other.foo', function($q) use ($keyword) {
                                           $q->where('bar', 'like', "%{$keyword}%");
                                       });
                               });
                       }
                   })
                   ->make(true);
    }

public function query()
{
    $query = Model::with('relationship.with.user');

    return $this->applyScopes($query);
}

Hope it helps.

[salihklc]

I solve it but not very satisfied. I add

$datatables->filterColumn('full_name', function($query, $keyword) {
                $query->whereRaw("(select count(1) from users where users.id = updated_by and CONCAT(users.first_name,' ',users.last_name) like ?) >= 1", ["%{$keyword}%"]);
            }) ..... 

to ajax section. And also remove mutators from User model and change

 /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
. . . . ...
            ["title" => "Updated By", "data" => "updated_by.full_name", "name" => "updatedBy.full_name"],
        ];
    }

to

 /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
. . . . ...
            ["title" => "Updated By", "data" => "updated_by.full_name", "name" => "full_name","orderable"=>false],
        ];
    }

[salihklc]

Should I close the issue or not ? :sweat_smile:

[rzb]

I'd leave it open. Maybe the owner has a cleaner way to suggest. :)

2016-12-29 13:01 GMT-02:00 Salih KILIÇ [e-mail]:

Would I close the issue or not ? 😅

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/yajra/laravel-datatables/issues/911#issuecomment-269642061,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AB3dLplWzgRX3-wU5EW727Aa_qKbH1Taks5rM8tBgaJpZM4LXhMb
.

--
Renato Zuma Bange

[yajra]

Glad you sorted this out guys. I think both solutions are correct but I guess the cleaner way to go about this is via filterColumn api.

@salihklc I think you can also merged @rzb solution if you prefer. Maybe something like below but note that this is not tested.

<?php

$datatables->filterColumn('full_name', function($query, $keyword) {
    $query->whereHas("user", function($query) use ($keyword) {
        $query->whereRaw('users.id = updated_by')
            ->whereRaw('CONCAT(users.first_name,' ',users.last_name) like ?) >= 1', ["%{$keyword}%"]);
    });
});

Thanks!

[rudashi]

When I try use this, I always get error:

->filterColumn('manager', function($query, $keyword) {
                $query->whereHas('managers', function($query) use ($keyword) {
                    $query->whereRaw("CONCAT(firstname,' ',lastname) like ?", ["%{$keyword}%"]);
                });
            })

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has' in 'where clause' (SQL: select count(*) as aggregate from (select '1' asrow_countfromuserswhere (has= managers) andusers.deleted_atis null andusers.deleted_atis null) count_row_table)

I'm using the leatest version 7* and Laravel 5.4

[yajra]

@rudashi it's because filter column query uses Illuminate\Database\Query\Builder and whereHas does not exists on that instance.

However, this was fixed on v8 where Eloquent\Builder is preserved when using filterColumn.

[rudashi]

@yajra after update to v8 the problem has dissapear it, but it isn't looking in managers relationship but in main table. Unfortunetly, the main table is the same as manager (users). How to directly refer to this relationship?

[yajra]

@rudashi, unfortunately, there is still a known issue #880 for self join relationship and it's not yet fixed.

[michaelovo]

This solved the problem:

filterColumn('fullname', function($query, $keyword) {
$sql = "CONCAT(users.first_name,'-',users.last_name) like ?";
$query->whereRaw($sql, ["%{$keyword}%"]);
})
->toJson();