Conflict with id in related data search yii2

Question

Ok here is my problem

i am displaying 2 related columns in a gridview both from same foreign table i am displaying first name and lastname as fullname and email as another column all those 3 data are from same table

 'columns' => [
        ['class' => 'yii\grid\CheckboxColumn'],
        [

            'attribute' => 'event_id',
            'label' => 'Event Title',
            'value' => 'event.title'
        ],
        [
            'attribute' => 'user_id',
            'label' => 'Name',
            'value' => 'users.fullname',
        ],
        [
            'attribute' => 'user_id',
            'label' => 'Email',
            'value' => 'users.email',
        ],

As you can see i have to give attribute as user_id if i want to make it searchable here is my search model

$query->joinWith(['event', 'users']);
$query->andFilterWhere(['like', 'event.title', $this->event_id]);
    $query->andFilterWhere(['like', 'user.firstname', $this->user_id]);
    $query->andFilterWhere(['like', 'user.email', $this->user_id]);

Now the problem is search on email works fine i mean when i enter any data in searchbox of email columns it renders the user_id fine but it automatically creates query like where email=blah blah AND firstname=blah blah In fact i havent entered any data in fullname column search box

When i enter any data in fullname column search it doesnt evet find the user_id of that table

how should i resolve this conflict??? Ohh and my database structure is something like this this gridview is of checkin table in which i am displaying all the users who checked in to particular event so there are 2 more tables event and users

 CREATE TABLE IF NOT EXISTS `event` (
  `id` int(11) NOT NULL,
  `organiser_id` int(11) NOT NULL,
  `interest_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(500) NOT NULL,
  `location` varchar(255) NOT NULL,
  `is_active` bit(1) NOT NULL,

) 

 CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `dob` datetime DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `area_intrest` varchar(250) NOT NULL,
)

CREATE TABLE IF NOT EXISTS `checkin` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `user_type` enum('competitor','fan') NOT NULL,
)

Here is my Search function code

class CheckinSearch extends Checkin

{

public $fullName;
/**
 * @inheritdoc
 */
public function rules()
{
    return [
        [['id', 'created_by', 'updated_by'], 'integer'],
        [['user_type', 'user_id', 'event_id', 'created_date', 'updated_date','fullName'], 'safe'],
    ];
}

/**
 * @inheritdoc
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params)
{

    if(!isset($_GET['id'])){
        $id='';
    }
    else{
        $id=$_GET['id'];
    }

    $query = Checkin::find()->where(['event_id'=> $id]);

    $query->joinWith(['event', 'users']);


    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

    $query->andFilterWhere([
        'id' => $this->id,
        'created_date' => $this->created_date,
        'created_by' => $this->created_by,
        'updated_date' => $this->updated_date,
        'updated_by' => $this->updated_by,
    ]);

    $query->andFilterWhere(['like', 'event.title', $this->event_id]);
    $query->andFilterWhere(['like', 'user.firstname', $this->fullName]);
    $query->andFilterWhere(['like', 'user.lastname', $this->fullName]);
    $query->andFilterWhere(['like', 'user.email', $this->user_id]);
    $query->andFilterWhere(['like', 'user_type', $this->user_type]);

    return $dataProvider;
    }
}

If i add both firstname and lastname filter it generates query like this

   SELECT COUNT(*) FROM `checkin` LEFT JOIN `event` ON `checkin`.`event_id` = `event`.`id` 
LEFT JOIN `user` ON `checkin`.`user_id` = `user`.`id` WHERE 
((`event_id`='11') AND (`user`.`firstname` LIKE '%text%')) AND 
(`user`.`lastname` LIKE '%text%')

I want to generate query like this using the AND or OR filter

SELECT * FROM `checkin` LEFT JOIN `event` ON `checkin`.`event_id` = `event`.`id` 
LEFT JOIN `user` ON `checkin`.`user_id` = `user`.`id` WHERE 
((`event_id`='11') AND (`user`.`firstname` LIKE '%text%')) OR 
((`event_id`='11') AND (`user`.`lastname` LIKE '%text%'))

thank you

Answer 1

Thank you for your help guys but i came accross a question on stackoverflow and solved it here is what i did

$query->andFilterWhere(['or',
        ['like','user.firstname',$this->fullName],
        ['like','user.lastname',$this->fullName]]);

this query will look in both firstname or lastname seems to solve my problem now

Answer 2

create one more variable for full_name in your model, add that variable to safe search & use that variable for searching & also while displaying full name.

Suppose your new variable is "full_name"

Then in your search function add following line

$query->andFilterWhere(['like', 'user.firstname', $this->full_name]);

In view

[
    'attribute' => 'full_name',
    'label' => 'Name',
    'value' => 'users.fullname',
],