I am a new in yii2 framework, when i trying to join more than two tables than it is not loading in gridview.
I am trying fetch language name from language table which i can get by userlanguage table which has a two field as below. So as per the userID I want to fetch the language details of user. show language name in my
My table structute is:
Users table
from this table i will get userID which is join with userID of userlanguage table and a userlanguage table contains two field
userID
languageID
By getting languageID from userlanguage table I have to show languageName from language table, which contains two field:
languageID
languageName
A usertable contains:
userID
userName
etc.
UsersSearch.php(MySearchModel)
<?php
namespace backend\models;
use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use backend\models\Users;
use backend\models\Userlanguage;
use backend\models\Language;
use yii\db\Query;
class UsersSearch extends Users
{
public $languageID;
public function rules()
{
return [
[['userID', 'countryId', 'userHomeCityID', 'userCurrentCityID', 'userStars', 'userFans', 'userLike', 'userNutural', 'userDontLike'], 'integer'],
[['userEmail', 'userPassword', 'userName', 'userMobile', 'userCountryCode', 'userAbout', 'userBirthDate', 'userGender', 'userHeightUnit', 'userWeightUnit', 'userBodyColour', 'userEyesColour', 'userHairColour', 'userAdditionalInformation', 'userProfilePicture', 'userCoverPhoto', 'userLastLogin', 'userCreatedDate', 'userStatus', 'userDeviceType', 'userDeviceID', 'userType'], 'safe'],
[['userHeight', 'userWeight'], 'number'],
];
}
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
public function search($params)
{
$query = Users::find();
// add conditions that should always apply here
$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->joinWith(["user"=>function($a){$a->from("users user");}]);
$query->joinWith(["languages"=>function($c){$c->from("language languages");}]);
$query->andFilterWhere([
'userID' => $this->userID,
'countryId' => $this->countryId,
'userBirthDate' => $this->userBirthDate,
'userHomeCityID' => $this->userHomeCityID,
'userCurrentCityID' => $this->userCurrentCityID,
'userHeight' => $this->userHeight,
'userWeight' => $this->userWeight,
'userStars' => $this->userStars,
'userFans' => $this->userFans,
'userLike' => $this->userLike,
'userNutural' => $this->userNutural,
'userDontLike' => $this->userDontLike,
'userLastLogin' => $this->userLastLogin,
'userCreatedDate' => $this->userCreatedDate,
]);
$query->andFilterWhere(['like', 'userEmail', $this->userEmail])
->andFilterWhere(['like', 'userPassword', $this->userPassword])
->andFilterWhere(['like', 'userName', $this->userName])
->andFilterWhere(['like', 'userMobile', $this->userMobile])
->andFilterWhere(['like', 'userCountryCode', $this->userCountryCode])
->andFilterWhere(['like', 'userAbout', $this->userAbout])
->andFilterWhere(['like', 'userGender', $this->userGender])
->andFilterWhere(['like', 'userHeightUnit', $this->userHeightUnit])
->andFilterWhere(['like', 'userWeightUnit', $this->userWeightUnit])
->andFilterWhere(['like', 'userBodyColour', $this->userBodyColour])
->andFilterWhere(['like', 'userEyesColour', $this->userEyesColour])
->andFilterWhere(['like', 'userHairColour', $this->userHairColour])
->andFilterWhere(['like', 'userAdditionalInformation', $this->userAdditionalInformation])
->andFilterWhere(['like', 'userProfilePicture', $this->userProfilePicture])
->andFilterWhere(['like', 'userCoverPhoto', $this->userCoverPhoto])
->andFilterWhere(['like', 'userStatus', $this->userStatus])
->andFilterWhere(['like', 'userDeviceType', $this->userDeviceType])
->andFilterWhere(['like', 'userDeviceID', $this->userDeviceID])
->andFilterWhere(['like', 'userType', $this->userType])
->andFilterWhere(['like', 'userslanguage.userID', $this->userID])
->andFilterWhere(['like', 'languages.languageID', $this->getAttribute('languages.languageID')])
->andFilterWhere(['like', 'languages.languageName', $this->getAttribute('languages.languageID')]);
return $dataProvider;
}
}
When i echo my query it works fine and return me the data as per i require.
SELECT `user`.`userID`, `user`.`userEmail`, `languageName`, `l`.`languageID`, `l`.`languageName`, `user`.`userName`, `user`.`userMobile`, `user`.`userGender`, `user`.`userStatus` FROM `users` `user` LEFT JOIN `userlanguage` `ul` ON user.userID = ul.userID LEFT JOIN `language` `l` ON ul.languageID = l.languageID
Users.php(MyModel)
<?php
namespace backend\models;
use Yii;
class Users extends \yii\db\ActiveRecord
{
public $languageID;
public static function tableName()
{
return 'users';
}
public function rules()
{
return [
[['userEmail', 'userPassword', 'userName', 'userMobile', 'userCountryCode', 'countryId', 'userHomeCityID', 'userCurrentCityID', 'userHeight', 'userHeightUnit', 'userWeight', 'userWeightUnit', 'userBodyColour', 'userEyesColour', 'userHairColour', 'userAdditionalInformation', 'userProfilePicture', 'userCoverPhoto', 'userStatus', 'userDeviceType', 'userDeviceID', 'userType'], 'required'],
[['countryId', 'userHomeCityID', 'userCurrentCityID', 'userStars', 'userFans', 'userLike', 'userNutural', 'userDontLike'], 'integer'],
[['userBirthDate', 'userLastLogin', 'userCreatedDate','languageID','languageName'], 'safe'],
[['userHeight', 'userWeight'], 'number'],
[['userStatus', 'userType'], 'string'],
[['userEmail', 'userPassword', 'userProfilePicture', 'userCoverPhoto'], 'string', 'max' => 100],
[['userName', 'userHeightUnit', 'userWeightUnit'], 'string', 'max' => 20],
[['userMobile', 'userGender', 'userDeviceType'], 'string', 'max' => 10],
[['userCountryCode'], 'string', 'max' => 5],
[['userAbout', 'userDeviceID'], 'string', 'max' => 500],
[['userBodyColour', 'userEyesColour', 'userHairColour'], 'string', 'max' => 50],
[['userAdditionalInformation'], 'string', 'max' => 4000],
[['userEmail'], 'unique'],
[['userName'], 'unique'],
[['userMobile', 'countryId'], 'unique', 'targetAttribute' => ['userMobile', 'countryId'], 'message' => 'The combination of User Mobile and Country ID has already been taken.'],
];
}
public function attributes(){
return array_merge(parent::attributes(), ['languages.languageID']);
}
public function attributeLabels()
{
return [
'userID' => 'User ID',
'userEmail' => 'User Email',
'userPassword' => 'User Password',
'userName' => 'User Name',
'userMobile' => 'User Mobile',
'userCountryCode' => 'User Country Code',
'countryId' => 'Country ID',
'userAbout' => 'User About',
'userBirthDate' => 'User Birth Date',
'userGender' => 'User Gender',
'userHomeCityID' => 'User Home City ID',
'userCurrentCityID' => 'User Current City ID',
'userHeight' => 'User Height',
'userHeightUnit' => 'User Height Unit',
'userWeight' => 'User Weight',
'userWeightUnit' => 'User Weight Unit',
'userBodyColour' => 'User Body Colour',
'userEyesColour' => 'User Eyes Colour',
'userHairColour' => 'User Hair Colour',
'userAdditionalInformation' => 'User Additional Information',
'userProfilePicture' => 'User Profile Picture',
'userCoverPhoto' => 'User Cover Photo',
'userStars' => 'User Stars',
'userFans' => 'User Fans',
'userLike' => 'User Like',
'userNutural' => 'User Nutural',
'userDontLike' => 'User Dont Like',
'userLastLogin' => 'User Last Login',
'userCreatedDate' => 'User Created Date',
'userStatus' => 'User Status',
'userDeviceType' => 'User Device Type',
'userDeviceID' => 'User Device ID',
'userType' => 'User Type',
'languageID' => \Yii::t('app', 'Langauge ID'),
'email' => \Yii::t('app', 'Language Name'),
];
}
public function getUser(){
return $this->hasOne(Users::className(), ['userID' => 'userID']);
}
public function getLanguages(){
return $this->hasMany(Language::className(), ['languageID' => 'languageID'])
->viaTable('userlanguage', ['userID' => 'userID']);
}
}
index.php(My Gridview)
<?php
use yii\helpers\Html;
use yii\grid\GridView;
use yii\bootstrap\Modal;
use yii\helpers\Url;
$this->title = 'Users';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="users-index">
<h1><?= Html::encode($this->title) ?></h1>
<?php // echo $this->render('_search', ['model' => $searchModel]); ?>
<p>
<?= Html::a('Create Users', ['create'], ['class' => 'btn btn-success']) ?>
</p>
<?= GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'columns' => [
['class' => 'yii\grid\SerialColumn'],
'userID',
'userEmail:email',
'languages.languageID',
'languages.languageName',
'userName',
'userMobile',
'userGender',
'userStatus',
['class' => 'yii\grid\ActionColumn','header' => 'Action','template' => '{view} {update} {delete} {my_button}',
'buttons' => [
'my_button' => function ($url, $model, $key) {
if($model->userStatus == 'Active'){
return Html::a('Make It Inactive', ['changestatus', 'id'=>$model->userID]);
}else{
return Html::a('Make It Active', ['changestatus', 'id'=>$model->userID]);
}
},
]],
['class' => 'yii\grid\ActionColumn'],
});
?>
</div>
It's return this array in $dataprovider
yii\data\ActiveDataProvider Object
(
[query] => yii\db\ActiveQuery Object
(
[sql] =>
[on] =>
[joinWith] => Array
(
[0] => Array
(
[0] => Array
(
[users] => Closure Object
(
[static] => Array
(
[callback] =>
[alias] => user
)
[this] => yii\db\ActiveQuery Object
*RECURSION*
[parameter] => Array
(
[$query] =>
)
)
)
[1] => 1
[2] => LEFT JOIN
)
[1] => Array
(
[0] => Array
(
[0] => language
)
[1] => 1
[2] => LEFT JOIN
)
)
[select] =>
[selectOption] =>
[distinct] =>
[from] =>
[groupBy] =>
[join] =>
[having] =>
[union] =>
[params] => Array
(
)
[_events:yii\base\Component:private] => Array
(
)
[_behaviors:yii\base\Component:private] => Array
(
)
[where] =>
[limit] =>
[offset] =>
[orderBy] =>
[indexBy] =>
[modelClass] => backend\models\Users
[with] =>
[asArray] =>
[multiple] =>
[primaryModel] =>
[link] =>
[via] =>
[inverseOf] =>
)
[key] =>
[db] =>
[id] =>
[_sort:yii\data\BaseDataProvider:private] =>
[_pagination:yii\data\BaseDataProvider:private] =>
[_keys:yii\data\BaseDataProvider:private] =>
[_models:yii\data\BaseDataProvider:private] =>
[_totalCount:yii\data\BaseDataProvider:private] =>
[_events:yii\base\Component:private] => Array
(
)
[_behaviors:yii\base\Component:private] =>
)
Finally i got the answer of my question of Yii Forum:
Follow this below link: