"not Unique Table/alias" On Cgridview With Relations

Hello fellow yii-ers!

I can’t quite troubleshoot this one. I am trying to search from the PO_LineItem model, which has many parent relations.

It seems that I can either have search turned on for a PROJECT search path OR a VENDOR_CATEGORY search path, but not both!

It is telling me from Mysql:“1066 Not unique table/alias: ‘PO1’.” (see below)

You can see I am experimenting with the “alias” attribute, but I don’t know where to put it? I think it is getting carried with the “through” and getting duplicated up the search paths?

Any Thoughts on how I can get around this?

Thanks!

–MrJ

Model Diagram:




CompanyCategory (id, category...)

|

CompanyCategoryLink (company_id, company_category_id)for MANY_MANY link

|

Company (id...)        

|             Project (id...)

|______________|

|

PO (id, company_id, project_id ...)

|

PO_LineItem (id, po_id ...)




Now, from the PO_item, I am able to search (in CGridview), but I get the error when loading the CGridView:




CDbException


CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'PO1'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `phdb_po_lineitem` `t` LEFT OUTER JOIN `phdb_po` `PO1` ON (`t`.`po_id`=`PO1`.`id`) LEFT OUTER JOIN `dx_projects` `Project` ON (`PO1`.`project_id`=`Project`.`id`) AND (`PO1`.`id`=`Project`.`id`) LEFT OUTER JOIN `phdb_po` `PO1` ON (`t`.`po_id`=`PO1`.`id`) LEFT OUTER JOIN `company` `vendor_company` ON (`PO1`.`_contact_id_vendor_address`=`vendor_company`.`id`) LEFT OUTER JOIN `company_category_link` `vendor_categoryLink` ON (`vendor_company`.`id`=`vendor_categoryLink`.`company_id`) LEFT OUTER JOIN `company_category` `vendor_category` ON (`vendor_categoryLink`.`company_category_id`=`vendor_category`.`id`) WHERE ((Project.id=:ycp0) AND (t.DBstatus>0)) 



Here is my PO_LineItem model:




<?php class PO_LineItem extends CActiveRecord

{

  /**

   * Search Variables

   */

  public $search_project_id; // Searches by project id

  public $search_vendor_company_id; // Searches vendor companies

  public $search_vendor_company_category_id; // Searches vendor company categories

...

  /**

   * @return array relational rules.

   */

  public function relations()

  {

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.

    return array(

      // The purchase order this lineitem belongs to

      'PO' => array(

        self::BELONGS_TO,

        'PO',

        'po_id',

        'alias'=>'PO1',

      #'with' => 'Project',

      #'together' => true

      ),

      // The project this Line Item's PO belongs to

      'Project' => array(

        self::HAS_ONE,

        'Projects',

        array('project_id', 'id'),

        'through' => 'PO'),

      //

      // Vendor Company (for address)

      'vendor_company' => array(

        self::HAS_ONE,

        'Company',

        array('_contact_id_vendor_address' => 'id'),

        'through' => 'PO',

        'with' => 'category'

      ),

      // Intermediate relation for vendor_category

      'vendor_categoryLink' => array(

        self::HAS_MANY,

        'CompanyCategoryLink',

        array('id' => 'company_id'),

        'through' => 'vendor_company'),

      // the categories that the vendor company belongs to

      'vendor_category' => array(

        self::HAS_MANY,

        'CompanyCategory',

        array('company_category_id' => 'id'),

        'through' => 'vendor_categoryLink',),

    );

...

  /**

   * Retrieves a list of models based on the current search/filter conditions.

   * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

   */

  public function search($pagination = 10)

  {

    // Warning: Please modify the following code to remove attributes that

    // should not be searched.


    $criteria = new CDbCriteria;

    $criteria->with = array(

      'PO',

      'Project',

      'vendor_company',

      'vendor_category',

    );


    $t = self::getTableAlias();


    $criteria->together = true;

    $criteria->compare('Project.id', $this->search_project_id);

    $criteria->compare('vendor_company.id', $this->search_vendor_company_id);

    $criteria->compare('vendor_category.id', $this->search_vendor_company_category_id);


    // native table parameters

    $criteria->compare("$t.id", $this->id);

    $criteria->compare("$t.po_id", $this->po_id);

    $criteria->compare("$t.SortNum", $this->SortNum);

    $criteria->compare("$t.ItemNo", $this->ItemNo);

...

    $criteria->addCondition("$t.DBstatus>0", 'AND');


    return new CActiveDataProvider($this, array(

      'criteria' => $criteria,

      'pagination' => array(

        'pageSize' => $pagination,

      ),

    ));

  }

}

?>