Search from two columns in one input

I have query in my BlogSearch where i join relation with User. In my Column Rel_User I want to searchin User by his fullname. This is my code of BlogSearch.


$query->joinWith('relUser');//join relationship here


        $query->andFilterWhere([

            'Id' => $this->Id,

            'CreatedAt' => $this->CreatedAt,

        ]);


        $query->andFilterWhere(['like', 'Title', $this->Title])

             ->andFilterWhere(['like', 'Description', $this->Description])

             ->andFilterWhere(['like', 'UrUser.Name', $this->Rel_User]);//UrUser is table  where i have Name and Surname and there i searching Name but want to search Name and Surname

But now i can search only by user Name. I tried to do this:


$query->andFilterWhere(['like', 'Title', $this->Title])

             ->andFilterWhere(['like', 'Description', $this->Description])

             ->andFilterWhere(['like', 'UrUser.Name', $this->Rel_User])

             ->andFilterWhere(['like', 'UrUser.Surname', $this->Rel_User]);

But it not work for me. How can i create virtual column to connect Name and Surname in one column Search? I This Search is in CRUD in index in GridView

Ok, i write a similar example,you need a attribute name for name and surname.

[size="4"]

Class BlogNews[/size]




class BlogNews extends \yii\db\ActiveRecord

{

    public $NameCategory;



[size="4"]

Class BlogNewsSearch[/size]





class BlogNewsSearch extends BlogNews

{

    public $NameCategory;


    public function rules()

    {

        return [

            [['id', 'id_category', 'status'], 'integer'],

            [['title', 'description', 'body', 'date','NameCategory'], 'safe'],

        ];

    }

    public function search($params)

    {        

        $query = BlogNews::find()->select('blog_news.*,'

                . 'c.name as NameCategory ')->

                leftJoin('blog_category c', 'c.id=blog_news.id_category');


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);

        $dataProvider->setSort([

        'attributes' => [

            'id',

            'id_category',

            'tittle',

            'description',

            'body',

     		'date',

     		'img',

     		'status',

            'NameCategory']]);

        

        $this->load($params);


        if (!$this->validate()) {

            return $dataProvider;

        }

        $query->andFilterWhere([

            'id' => $this->id,

            'id_category' => $this->id_category,

            'date' => $this->date,

            'status' => $this->status,

        ]);


        $query->andFilterWhere(['like', 'tittle', $this->tittle])

            ->andFilterWhere(['like', 'description', $this->description])

            ->andFilterWhere(['like', 'name', $this->NameCategory])

            ->andFilterWhere(['like', 'body', $this->body]);


        return $dataProvider;

    }

}



In this example NameCategory is your name or surname, you need two atributes for the index , $Name and $SurName and write in the filter

If you need put the name of the table for the same field ,do it.

I can’t understand this query:


$query = BlogNews::find()->select('blog_news.*,'

                . 'c.name as NameCategory ')->

                leftJoin('blog_category c', 'c.id=blog_news.id_category');

i tried to used this in my case but it not work for me:




     $query = Blog::find()->select('UrBlog.*,'

                . 'c.Name as fullname ', 'c.Surname as fullname')->

                leftJoin('UrUser c', 'c.Id=UrBlog.Rel_User');


 $query->andFilterWhere(['like', 'Title', $this->Title])

        ->andFilterWhere(['like', 'Description', $this->Description])

   ->andFilterWhere(['like', 'c.Name', $this->fullname]);

And in index in gridView i have this code:


 <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'filterModel' => $searchModel,

        'columns' => [

            'Title',

            'Description',

            [

                'attribute' => 'Rel_User',

                'value' => function($model) { return $model->relUser->Name  . " " . $model->relUser->Surname; }

             ], 

            //'CreatedAt',

            // 'IsDeleted',


            ['class' => 'yii\grid\ActionColumn'],

        ],

    ]); ?>

I create public $fullname in my model Blog. I know i should select name and surname from user on this $query and used alias fullname. I am new in Yii2 and understand of sql queries is problem for me can u help me in this example?

You can do this in differents ways but the most simple is declared a new variable:


public $FullName;

in the model and in the search model,

in your case you not write correct :





$query = Blog::find()->select('UrBlog.*,'

                . 'c.Name as fullname ', 'c.Surname as fullname')->

                leftJoin('UrUser c', 'c.Id=UrBlog.Rel_User');

you not put fullname twice,the correct is this:





$query = Blog::find()->select('b.*,'

                . 'concat(c.Name," ",c.Surname) as Fullname')->from('UrBlog b')

                leftJoin('UrUser c', 'c.Id=b.Rel_User');

in the filter you need :





   ->andFilterWhere(['like', 'FullName', $this->FullName]);

In the gridView the column is the same





        'columns' => [

            'Title',

            'Description',

    	'FullName'






You need more information in yii2 and in the querys world, and please CamelCase or snake but not both

Ok thanks for answer but I cant check this becouse all column with FullName is not set. How i can now display name and surname of user in this column. I tried to do this:


<?= GridView::widget([

        'dataProvider' => $dataProvider,

        'filterModel' => $searchModel,

        'columns' => [

            'Title',

            'Description',

            [

                'attribute' => 'FullName',

                'value' => function($model) { return $model->relUser->Name  . " " . $model->relUser->Surname; }

             ], 

                     'FullName',

            //'CreatedAt',

            // 'IsDeleted',


            ['class' => 'yii\grid\ActionColumn'],

        ],

    ]); ?>

And in first colun of FullNAme i have name and surname of users but searching not work, in second column FullName i have alll valuse not set what is wrong now?

Delete first column FullName, you need only attr FullName for show name and surname and filter this column.

When you use concat in mysql it seem when you use value in the first column,

please delete your first attr FullName.

ok but it still not search me my expected data




// In search model

$query->andFilterWhere(

            [

                'like',

                'Concat(first_name," ", last_name)',

                $this->first_name 

            ]

        )



ok it work now Thanks guys

public function search($params)

{


    /** @var &#092;amnah&#092;yii2&#092;user&#092;models&#092;User &#036;user */


    /** @var &#092;amnah&#092;yii2&#092;user&#092;models&#092;Profile &#036;profile */





    // get models


    &#036;user         = Yii::&#036;app-&gt;getModule(&quot;user&quot;)-&gt;model(&quot;User&quot;);


    &#036;profile      = Yii::&#036;app-&gt;getModule(&quot;user&quot;)-&gt;model(&quot;Profile&quot;);


    &#036;userTable    = &#036;user::tableName();


    &#036;profileTable = &#036;profile::tableName();





    // set up query with relation to `profile.full_name`


    &#036;query = &#036;user::find();


    &#036;query-&gt;joinWith(['profile' =&gt; function(&#036;query) use (&#036;profileTable) {


        &#036;query-&gt;from(['profile' =&gt; &#036;profileTable]);


    }]);





    // create data provider


    &#036;dataProvider = new ActiveDataProvider([


        'query' =&gt; &#036;query,


    ]);





    // enable sorting for the related columns


    &#036;addSortAttributes = [&quot;profile.full_name&quot;];


    foreach (&#036;addSortAttributes as &#036;addSortAttribute) {


        &#036;dataProvider-&gt;sort-&gt;attributes[&#036;addSortAttribute] = [


            'asc'   =&gt; [&#036;addSortAttribute =&gt; SORT_ASC],


            'desc'  =&gt; [&#036;addSortAttribute =&gt; SORT_DESC],


        ];


    }





    if (&#33;(&#036;this-&gt;load(&#036;params) &amp;&amp; &#036;this-&gt;validate())) {


        return &#036;dataProvider;


    }





    &#036;query-&gt;andFilterWhere([


        &quot;{&#036;userTable}.id&quot; =&gt; &#036;this-&gt;id,


        'role_id'         =&gt; &#036;this-&gt;role_id,


        'status'          =&gt; &#036;this-&gt;status,


    ]);





    &#036;query-&gt;andFilterWhere(['like', 'email', &#036;this-&gt;email])


        -&gt;andFilterWhere(['like', &quot;{&#036;userTable}.create_time&quot;, &#036;this-&gt;create_time])


        -&gt;andFilterWhere(['like', &quot;{&#036;userTable}.update_time&quot;, &#036;this-&gt;update_time])


        -&gt;andFilterWhere(['like', 'ban_time', &#036;this-&gt;ban_time])


        -&gt;andFilterWhere(['like', 'profile.full_name', &#036;this-&gt;getAttribute('profile.full_name')]);





    return &#036;dataProvider;


}

Please check this function of UserSearchModel.It works fine.Hope this would be helpful.