Conflict with id in related data search yii2

Yii 2.0 General Discussions
1

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

thank you

2

You should user orFilterWhere() not andFilterWhere() to achive what you need. hope this will help you http://www.yiiframework.com/doc-2.0/yii-db-queryinterface.html#orFilterWhere()-detail

3

Thank you for your replay

but i cant do that as on the top i am already filtering the data with id like this




 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