Using Self Join In Grid View

Yii 2.0 General Discussions
1

But how do I use Self JOIN in a Grid View

In my Model I have used


public function getParent()

        {           

            return $this->hasOne(self::className(), ['categories_id'=>'parent_id'])->from(self::tableName().' parent');

        }

And in my ModelSearch I have added


$query->joinWith(['parent']);

But when I try to filter any data it throws an exception like :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘categories_id’ in where clause is ambiguous The SQL being executed was: SELECT COUNT(*) FROM categories LEFT JOIN categories parent ON categories.parent_id = parent.categories_id WHERE categories_id=‘23’

Where do I put the table alias for categories_id in the WHERE statement.

Thnx

2

The same place where you do your filtering.

3

Thnx Orey,

The Grid View is created by Gii-CRUD. I do not have a clue where the filtering is done.

Can you plz guide me into this.

Thnx

4

Haven’t worked with it yet, but I suppose you should be looking at models folder. There must be some model that retrns dataprovider for the grid.

5

@dannythebestguy… I just see you also reported this on my posted wiki for gridview filtering.

Just check the search() function in your model that returns the dataProvider. I have not personally tried this. But can you check and let know?

  1. First we need to alias your existing table query. Not sure how to do this with find(). Can you change to using findBySQL instead of find (to alias your table name - e.g. child).



$sql = 'SELECT * FROM tbl_person AS child';

$query = Person::findBySQL($sql);

$dataProvider = new ActiveDataProvider([

    'query' => $query,

]);
  1. You may need to change all attribute columns to have aliases. Therefore change your addCondition statements:



$this->addCondition($query, 'child.categories_id');

// any other conditions
6

No Bueno, I get this error

[b]Unknown Property – yii\base\UnknownPropertyException

Getting unknown property: app\models\search\CategoriesSearch::child.categories_id

[/b]

7

On cross checking — this was so simple 8).

The error is in your model relation. Just change this and it should work (you need to alias your categories_id column as shown below).




public function getParent()

{           

    return $this->hasOne(self::className(),

        ['parent.categories_id' => 'parent_id'])->

        from(self::tableName().' AS parent');

}

No need to change the other functions.

8

No Kartik, it still throws the error when I put some value on the Grid filter.

[b]SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘categories_id’ in where clause is ambiguous

The SQL being executed was: SELECT COUNT(*) FROM categories LEFT JOIN categories parent ON categories.parent_id = parent.categories_id WHERE categories_id=‘15’[/b]

9

The only hack which worked was


protected function addCondition($query, $attribute, $partialMatch = false)

	{		

                $value = $this->$attribute;


                $attribute = "categories.$attribute";


		if (trim($value) === '') {

			return;

		}

		if ($partialMatch) {

			$query->andWhere(['like', $attribute, $value]);

		} else {

			$query->andWhere([$attribute => $value]);

		}
10

Right! Will record this scenario in the wiki and update.

11

Ok the wiki is updated with a complete example for self-join. Refer scenario # 3.

12

Absolutely Great Job KArtik, Thanx for the Self Join Update.