Problem with filter in GRIDVIEW
Hello community I am trying to filter and sort by the created_by username, updated_by.
The error that gives me is:
Duplicate alias: 7 ERROR: The table name "user" was specified more than once
The SQL being executed was: SELECT COUNT(*) FROM "anno" LEFT JOIN "user" ON "anno"."created_by" = "user"."id" LEFT JOIN "user" ON "anno"."updated_by" = "user"."id"
SQLSTATE[42712]: Duplicate alias: 7
Here I leave the model and the search model. I would greatly appreciate any help they can offer me.
MODEL:
<?php
namespace app\models;
use Yii;
use yii\behaviors\BlameableBehavior;
use yii\db\Expression;
use yii\db\ActiveRecord;
/**
-
This is the model class for table "anno".
-
@property integer $id
-
@property string $created_at
-
@property string $updated_at
-
@property integer $created_by
-
@property integer $updated_by
-
@property User $createdBy
-
@property User $updatedBy
*/
class Anno extends \yii\db\ActiveRecord
{
/**
* @inheritdoc
*/
public static function tableName()
{
return 'anno';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['id', 'created_at', 'created_by'], 'required'],
[['id', 'created_by', 'updated_by'], 'integer'],
[['created_at', 'updated_at'], 'safe'],
[['created_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['created_by' => 'id']],
[['updated_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['updated_by' => 'id']],
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'id' => 'ID',
'created_at' => 'Created At',
'updated_at' => 'Updated At',
'created_by' => 'Created By',
'updated_by' => 'Updated By',
];
}
/**
* @return \yii\db\ActiveQuery
*/
public function getCreatedBy()
{
return $this->hasOne(User::className(), ['id' => 'created_by']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getUpdatedBy()
{
return $this->hasOne(User::className(), ['id' => 'updated_by']);
}
}
SEARCH MODEL:
<?php
namespace app\models\Search;
use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\Anno;
/**
- AnnoSearch represents the model behind the search form about
app\models\Anno.
*/
class AnnoSearch extends Anno
{
/**
* @inheritdoc
*/
public function rules()
{
return [
[['id'], 'integer'],
[['created_at', 'updated_at','created_by', 'updated_by'], '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)
{
$query = Anno::find();
$query->joinWith('createdBy')
$query->joinWith('updatedBy');
$dataProvider = new ActiveDataProvider([
'query' => $query,
'pagination' => [
'pageSize' => 10,
],
'sort' => [
'defaultOrder' => [
'anno' => SORT_DESC,
],
'attributes' => [
'anno',
'created_by' => [
'asc' => ['user.username' => SORT_ASC],
'desc' => ['user.username' => SORT_DESC],
],
'updated_by' => [
'asc' => ['user.username' => SORT_ASC],
'desc' => ['user.username' => SORT_DESC],
],
'created_at',
'updated_at'
]
]
]);
$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_at' => $this->created_at,
'updated_at' => $this->updated_at
]);
$query->andFilterWhere(['like','user.username', $this->created_by])
->andFilterWhere(['like','user.username', $this->updated_by]);
return $dataProvider;
}
}