I have a situation where I need to be able to order on a joined column. I have two tables, Customer and CustomerType. Customer Type holds very little information, but I would like to present the type name in a table listing Customers rather than the id, and for that column to be optionally sortable.
<?php
class Customer extends CActiveRecord
{
public $id;
public $customerTypeId;
public $name;
public $email;
public function tableName()
{
return "customer";
}
public function relations()
{
return array("customerType" => array(self::BELONGS_TO, 'CustomerType', 'customerTypeId', 'aliasToken'=>'type'));
}
public static function model($className=__CLASS__)
{
return parent::model($className);
}
}
class CustomerType extends CActiveRecord
{
public $id;
public $customerType;
public $isAwesome;
public function tableName()
{
return "customertype";
}
public static function model($className=__CLASS__)
{
return parent::model($className);
}
}
?>
With the above AR definitions, I then try to use the following to retrieve the list:
<?php
$criteria = new CDbCriteria;
//$criteria->select = "id, name, type.customerType";
$criteria->order = "type.customerType";
$data = Customer::model()->with("customerType")->findAll($criteria);
?>
But it doesn't work. I get the following error:
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'type.customerType' in 'order clause'
The following query shows up in the mysql query log:
SELECT `customer`.`id` AS t0_c0, `customer`.`customerTypeId` AS t0_c1, `customer`.`name` AS t0_c2, `customer`.`email` AS t0_c3, t1.`id` AS t1_c0, t1.`customerType` AS t1_c1, t1.`isAwesome` AS t1_c2 FROM `customer` LEFT OUTER JOIN `customertype` t1 ON `customer`.`customerTypeId`=t1.`id` ORDER BY type.customerType
Is there a way to accomplish what I am trying to accomplish without having to declare the order clause against the relation in the model?
I also want to be able to limit the columns selected as well, but uncommenting the 'select' line in the criteria above yields this error instead:
Active record "BookingReferenceRecord" is trying to select an invalid column "source.booking_reference_source". Note, the column must exist in the table or be an expression with alias.
The error seems to suggest I should be able to use the alias to do the selecting, but in practise it doesn't work.