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,
),
));
}
}
?>