CSort() for relational query

Archives Yii 1.0.x Discussions
1

Hi, I’ve created a relation so that I can perform a relational query. I’m trying to sort on the ID field of the resultset but it gives me an error.

Model:


public function relations()

{

	return array(

		'app_status'=>array(self::HAS_MANY, 'Audit', '', 'on'=>'application.id = audit.app_id AND application.application_status = audit.action_id', 'order'=>'audit.id desc', 'alias'=>'audit'),

		'app_date'=>array(self::BELONGS_TO, 'Audit', '', 'on'=>'application.id = audit2.app_id AND audit2.action_id=001', 'alias'=>'audit2'),

	);

}

Controller:


$criteria1=new CDbCriteria;

$criteria1->addCondition("case_assigned_to = ''");


$pages1=new CPagination(Application::model()->count($criteria1));

$pages1->pageSize=self::PAGE_SIZE;

$pages1->applyLimit($criteria1);


$sort1=new CSort('Application');

$sort1->applyOrder($criteria1);


$models1=Application::model()->with('app_date')->findAll($criteria1);

View:


<?php echo $sort1->link('id'); ?>

The error message I am getting is:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in order clause is ambiguous

application.id is a PK and audit.id is a FK.

2

see this http://www.yiiframework.com/forum/index.php?/topic/6475-sort-based-on-other-parent-table/

3

Hi Horacio,

I cannot seem to get this working. Can you have a look at my particular issue and provide a sample solution?

I believe the problem may be to with the relation, this is the one in question:


'app_date'=>array(self::BELONGS_TO, 'Audit', '', 'on'=>'application.id = audit2.app_id AND audit2.action_id=001', 'alias'=>'audit2'),

This is in my Application Model.

4

I’ve given up on this - I don’t think I’ll ever get it to work because I’m also using multiple pagers on the same page - so that’s just asking for trouble!

What I would like to do however is specify a default sort order.

I my view I output the data as follows:


<?php echo CHtml::encode($model->app_status[0]->action_date); ?>

Here is the relation:


'app_status'=>array(self::HAS_MANY, 'Audit', '', 'on'=>'application.id = audit.app_id AND application.application_status = audit.action_id', 'order'=>'audit.id desc', 'alias'=>'audit'),

I specify an ‘order’ parameter because I need to get the most recent entry (accessed in the view using the [0] element)

How can I make CSort()->defaultOrder to order the criteria results by app_status[0]->action_date?


$criteria=new CDbCriteria;

$criteria->addCondition("application_status = '009'");

		

$pages=new CPagination(Application::model()->count($criteria));

$pages->pageSize=self::PAGE_SIZE;

$pages->applyLimit($criteria);


$sort=new CSort('Application');

//$sort->defaultOrder = "application_status desc";

$sort->applyOrder($criteria);

		

$models=Application::model()->with('app_status')->findAll($criteria);
5

Anyone able to help? (please don’t vote this -1 and if you do, at least show your self!)