Searching and orderby with full name but first name and last name in seperate columns without using-CodePudding

I'm using Laravel 8 and I have a table with a combined column of first_name and last_name the problem is I can only search it by either the first_name only or last_name only, not with the full name, would be better if I can have an option to orderBy it by full name also. I'm trying to avoid the whereRaw as much as possible as I've researched to have very hard performance especially in large tables.

This is my search function (How to search using combined first_name and last_name)

    public function scopeSearch($query, $term)
    {
        $term = "%$term%";
        $query->where(function ($query) use ($term) {
                $query->where('type', 'LIKE', $term)
                    ->orWhere('name', 'LIKE', $term)
                    ->orWhereHas('level', function($query) use ($term) {
                        $query->where('name', 'LIKE', $term);
                        })
                    ->orWhereHas('employee', function($query) use ($term) {
                        $query->where('first_name', 'LIKE', $term);
                        $query->orWhere('last_name', 'LIKE', $term);
                        $query->orWhere('employee_number', 'LIKE', $term);
                        })
                    ->orWhereHas('status', function($query) use ($term) {
                        $query->where('name', 'LIKE', $term);
                        })
                    });
    }

And this is my table query (How to orderBy combined first_name and last_name)

            $models = Model::where('company_id', Auth::user()->company_id)
            ->search(trim($this->search))
            ->orderBy($this->column, $this->order)
            ->paginate($this->size);

CodePudding user response：

You can add

        SELECT CONCAT(`first_name`, ' ', `last_name`) as full_name FROM `users`;

However, you must make it in the subquery so that laravel can know what is full_name

Or you can append full_name attribute to you model and search it in the front end,

CodePudding user response：

Use a FULLTEXT index across all the columns. It will be much faster.