Hi,
I have 2 database tables OrderStatuses and Shipments in a database called OrderStatus.
OrderStatuses primary key:
ordPurchaseOrderCode - varchar(60)
ordSalesOrderNumber - int(11)
Shipments primary key:
ordSalesOrderNumber - int(11)
shpTrackingNumber - varchar(25)
The storage engine for both tables is MyISAM, so there are no foreign keys between the tables.
When I find OrderStatuses with Shipments (see controller code below), instead of joining the tables with ordSalesOrderNumber, it is matching OrderStatus’ ordPurchaseOrderCode with Shipments’ ordSalesOrderNumber (see output code below).
Here is the relations function in the OrderStatuses model:
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(
'shipments'=>array(self::HAS_MANY,'Shipments','ordSalesOrderNumber'),
);
}
Here is the action in the controller:
public function actionDailyOrderTracking()
{
$criteria=new CDbCriteria;
$criteria->addCondition("ordSalesOrderNumber=525701");
$criteria->limit=1;
foreach(OrderStatuses::model()->with('shipments')->findAll($criteria) as $orderStatus)
{
var_dump($orderStatus->attributes);
$shipments=$orderStatus->shipments;
foreach($shipments as $shipment)
var_dump($shipment->attributes);
}
}
Here is the output of the script:
Order status:
array
'ordCustomerNumber' => string '141800' (length=6)
'ordPurchaseOrderCode' => string '2159' (length=4)
'ordSalesOrderNumber' => string '525701' (length=6)
'ordStatus' => string '41' (length=2)
'ordShipDate' => string '2006-07-26 00:00:00' (length=19)
'ordLastUpdated' => string '2009-05-07 02:58:26' (length=19)
Shipments:
array
'ordSalesOrderNumber' => string '2159' (length=4)
'shpTrackingNumber' => string '1Z34657401623xxxxx' (length=18)
'shpService' => string 'Next Day Air' (length=12)
'shpDate' => string '2006-05-09 00:00:00' (length=19)
'shpWeight' => string '1.30' (length=4)
'shpNetCharge' => string '32.29' (length=5)
'shpZipCode' => string '48084' (length=5)
'shpLastUpdated' => string '2009-05-07 03:12:25' (length=19)
array
'ordSalesOrderNumber' => string '2159' (length=4)
'shpTrackingNumber' => string '0337426573xxxxx' (length=15)
'shpService' => string 'FEDX Ground 988' (length=25)
'shpDate' => string '2009-01-08 00:00:00' (length=19)
'shpWeight' => string '2.30' (length=4)
'shpNetCharge' => string '4.98' (length=4)
'shpZipCode' => string '33156' (length=5)
'shpLastUpdated' => string '2009-09-15 08:05:57' (length=19)
As you can see, the shipments’ ordSalesOrderNumbers do not match the order status’ ordSalesOrderNumber; they match the order status’ ordPurchaseOrderCode.
Am I doing something wrong, or is this a bug? Let me know if you need anything else.