- operating system
Win7
- Web server
Xampp
- browser type
Chrome or QQBrowser(IE)
- Yii version
Yii1.1.15
Background:
My code run normal in Yii1.1.14. When I upgrade framework to 1.1.15 I find below issue.
Issue:
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: ‘scott: the table which duplicated’
Root cause:
After double check, I should find out the root cause: The through & with not handle correctly.
Need Qian and team help to correct it ASAP. Or else, I have to stay at 1.1.14 version with risk
You can duplicate issue as below:
- In the model -> relations, I have define some relation with through
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(
'platform' => array(self::BELONGS_TO, 'NPIPlatform', 'Platform_Id'),
'lob' => array(self::BELONGS_TO, 'NPILob', array('LOB_Id' => 'id'), [b]'through' => 'platform'[/b]),
'milestone' => array(self::BELONGS_TO, 'NPIMilestone', array('Milestone_id' => 'id'), [b]'through' => 'platform'[/b]),
'sGCM' => array(self::BELONGS_TO, 'NPIUser', 'SGCM_Id'),
'partRegion' => array(self::HAS_MANY, 'NPIPartRegion', 'Part_Id'),
'vendorPrimary' => array(self::BELONGS_TO, 'NPIVendor', 'Vendor_Primary'),
'commodity' => array(self::BELONGS_TO, 'NPICommodity', 'Commodity_Id'),
'commCode' => array(self::BELONGS_TO, 'NPICommCode', 'Comm_Code_Id'),
'platformGSM' => array(self::HAS_ONE, 'NPIUser', array('Platform_GSM_Id' => 'id'), [b]'through' => 'platform')[/b],
);
}
- In the model -> search, I use with to eager load data
$criteria->with = array(
'platform' => array(
'select' => 'Platform_Name,Status,RTS_Date,Note'
),
[font=“Impact”] ‘platformGSM’ => array(
'select' => 'username'
),
'milestone',[/font]
'lob' => array(
'select' => 'LOB_Name'
),
'commodity' => array(
'select' => 'Commodity',
),
'commCode' => array(
'select' => 'Comm_Code',
),
'sGCM' => array(
'select' => 'username'
),
'vendorPrimary' => array(
'select' => 'Name'
)
);
- Then the issue happen, since duplicate npi_platform left out join auto generated.
If I turn of platformGSM, milestone in with, the code will be ok and can run.
I think the yii1.1.15 not handle through and with correctly. Please double check. thanks.
Below is the auto generated SQL code:
Due to duplicated left outer join, it should be handle by Yii team:)
[font="Comic Sans MS"]CDbCommand::fetchColumn() failed: SQLSTATE[42000]: Syntax error or access
violation: 1066 Not unique table/alias: ‘platform’. The SQL statement
executed was: SELECT COUNT(DISTINCT t.id) FROM npi_part t [b]LEFT
OUTER JOIN npi_platform platform ON (t.Platform_Id=platform.id)[/b]
LEFT OUTER JOIN tbl_users platformGSM ON
(platform.Platform_GSM_Id=platformGSM.id) [b]LEFT OUTER JOIN
npi_platform platform ON (t.Platform_Id=platform.id)[/b] LEFT
OUTER JOIN npi_milestone milestone ON
(milestone.id=platform.Milestone_id) [b]LEFT OUTER JOIN
npi_platform platform ON (t.Platform_Id=platform.id)[/b] LEFT
OUTER JOIN npi_lob lob ON (lob.id=platform.LOB_Id) LEFT OUTER
JOIN npi_commodity commodity ON (t.Commodity_Id=commodity.id)
LEFT OUTER JOIN npi_comm_code commCode ON
(t.Comm_Code_Id=commCode.id) LEFT OUTER JOIN tbl_users sGCM ON
(t.SGCM_Id=sGCM.id) LEFT OUTER JOIN npi_vendor vendorPrimary
ON (t.Vendor_Primary=vendorPrimary.id) [/font]
