CODEWITHSUNDEEP
phpyiimysql-error-1066
Adam
Adam

Reputation: 2031

Searching and sorting by related model in Yii with CGridView when Relation is a key in the same table

There is a comprehensive article about searching and sorting by related model in CGridView here: http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/ I've successfully implemented this recipe a number of times.

However, now I am trying to search and sort by a relation that is in the same table (it defines the parent_id). See 'parent' relation below:

public function relations()
{
    return array(
        'parent' => array(self::BELONGS_TO, 'Category', 'parent_id'),
        'children' => array(self::HAS_MANY, 'Category', 'parent_id'),
        'childCount' => array(self::STAT, 'Category', 'parent_id'),
        'author0' => array(self::BELONGS_TO, 'User', 'author'),
        'contents' => array(self::MANY_MANY, 'Content', 'content_category(content_id, category_id)'),
        'crsContents' => array(self::MANY_MANY, 'ContentCrs', 'content_category(content_id, category_id)'),
    );
}

public function defaultScope()
{
    return array(
        'alias'=>'cat',
        'order'=>"cat.name ASC",
    );
}

When I implement the method as specified by the wiki, I get the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'cat'. The SQL statement executed was: SELECT COUNT(DISTINCT `cat`.`id`) FROM `category` `cat` LEFT OUTER JOIN `category` `cat` ON (`cat`.`parent_id`=`cat`.`id`) WHERE (cat.parent_id <> 257)

How can I ensure that the LEFT OUTER JOIN uses a unique table alias such as parent for the relation so that I can properly define my CDbCriteria in search()

EDIT:

As requested by @tereško, here is my search() function. I know it is flawed due to the table alias parent specified when I have not defined it... I just don't know how!

public function search()
{
    $criteria=new CDbCriteria;
    $criteria->with = array('parent');

    $criteria->compare('id',$this->id,true);
    $criteria->compare('author',$this->author,true);
    $criteria->compare('name',$this->name,true);
    $criteria->compare('description',$this->description,true);
    $criteria->compare('parent_id',$this->parent_id,true);
    $criteria->compare('parent.name', $this->parent_search, true );
    $criteria->compare('type',$this->type,true);
    $criteria->compare('slug',$this->slug,true);
    $criteria->compare('created',$this->created,true);
    $criteria->compare('updated',$this->updated,true);

    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,
            'sort'=>array(
                'attributes'=>array(
                    'parent_search'=>array(
                        'asc'=>'parent.name',
                        'desc'=>'parent.name DESC',
                    ),
                    '*',
                ),
            ),
        )
    );
}

Upvotes: 0

Views: 2875

Answers (1)

GBD
GBD

Reputation: 15981

You can give alias to parent relation as below

$criteria->with = array(
   'parent'=>array(
      'alias'=>'parent'
    )
);

Upvotes: 2

Related Questions