Yii Search Model - how to search multiple fields?

Question

Yii

Quickly, I couldn't find a multi-field search anywhere. Tell me, please, what did I do wrong? I need to filter request from one form. In this case, you need to find a person by last name (fullName). If you enter the first or last name separately, everything works. And if there is a space, the request stops.

public $fullName;

[['fullName'], safe]
....
/**
         * Настройка параметров сортировки
         * Важно: должна быть выполнена раньше $this->load($params)
         */
        $dataProvider->setSort([
            'attributes' => [
                'id',
                'fullName' => [
                    'asc' => ['last_name' => SORT_ASC, 'name' => SORT_ASC],
                    'desc' => ['last_name' => SORT_DESC, 'name' => SORT_DESC],
                    'label' => 'Full Name',
                    'default' => SORT_ASC
                ],
                'country_id'
            ]
        ]);
....
$query->andFilterWhere(['like', 'last_name', $this->fullName])
            ->orFilterWhere(['like', 'name', $this->fullName])
            ->orFilterWhere(['like', 'middle_name', $this->fullName]);

Answer 1

The solution could be like this using "CONCAT_WS":

$query = new Query();
            $query->select(['profile.user_id as id, CONCAT_WS(" ", profile.last_name, profile.name, profile.middle_name) AS text'])
                ->from('profile')
                ->leftJoin('user','profile.user_id = user.id','user.id = 10')
                ->leftJoin('auth_assignment','auth_assignment.user_id = profile.user_id')
                ->where('CONCAT_WS(" ", last_name, name, middle_name) LIKE :search')
                ->params([':search' => '%' . $search . '%'])
                ->orderBy(['last_name' => 'SORT_ASC', 'name' => 'SORT_ASC', 'middle_name' => 'SORT_ASC'])
                ->andWhere(['auth_assignment.item_name'=>'organizer'])
                ->andWhere(['user.status'=>\common\models\User::STATUS_ACTIVE])
                ->limit(20);
            $command = $query->createCommand();
            $data = $command->queryAll();

Answer 2

Presumably, it is necessary to $this->fullNamesplit by space and get the first name, last name and patronymic separately.
The request does not work because in the last lines $this->fullNameis substituted into all three fields in like.
But simply breaking the entered string by space is not entirely correct - there are too many degrees of freedom (for example, you don’t know in what order the user entered the first and last name, how many fields he entered, and if there is only one word, this is the first name or last name, etc.). d.). Therefore, in the form for these purposes, you need to make three different fields and substitute data from each of them into the request separately.