ORDER BY in ONE-TO-ONE

Archives Yii 1.0.x Discussions
1

Hello,

I realize this question has been asked before in this forum, but none of the solutions I’ve found have worked.

Using Yii 1.0.10

I’ve got two tables: Teacher and Contact. Contact contains the contact information (including first and last name) of the teachers. What I want to do is query Teachers, joining on Contact, sorting by Contact.LastName.

In the Teacher model, I set up this relation:




	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(

			'contact'=>array(self::HAS_ONE, 'Contact', 'ID'),

		);

	}

in the Teacher controller, i set up this query:




	public function actionAdmin()

	{

		$this->processAdminCommand();


		$criteria=new CDbCriteria;

		$criteria->order = 'contact.LastName ASC';

		

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

		$pages->pageSize=self::PAGE_SIZE;

		$pages->applyLimit($criteria);


		$sort=new CSort('Teacher');

		$sort->applyOrder($criteria);


		$models=Teacher::model()->with('contact')->findAll($criteria);


		$this->render('admin',array(

			'models'=>$models,

			'pages'=>$pages,

			'sort'=>$sort,

		));

	}

shouldn’t this work?

I’ve tried various possibilities here, including “??” instead of contact, setting up the order by clause in the relation declaration in the model, setting up an “alias” for contact and using that, setting the order in the “with” clause - none of them work.

what is the right way to sort a recordset by a column in a related table in a one-to-one relationship?

I’m mystified. Seems like such a simple common thing to want to do.

-Charlie

2

I think (and someone please confirm this) that $criteria->order is used to sort the rows of the related table, and can’t be used to sort the rows of the primary table. However, CSort seems to do the trick…

I was able to solve this by doing the following:

  1. add an alias to my relation in the Teacher 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(

			'contact'=>array(self::HAS_ONE, 'MailListStudent', 'StudentID', 'alias'=>'contact')

                );

        }

  1. in the Teacher Controller…

    a. don’t set $criteria->order - it will return an “unknown column” error (I have no idea why)

    b. instead set the "defaultOrder" of the CSort object

    c. make sure you use the "with" syntax in your query to include the relation…




	public function actionAdmin()

	{

		$this->processAdminCommand();


		$criteria=new CDbCriteria;

				

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

		$pages->pageSize=self::PAGE_SIZE;

		$pages->applyLimit($criteria);


		$sort=new CSort('Teacher');

		$sort->defaultOrder='contact.LastName ASC';

		$sort->applyOrder($criteria);


		$models=Teacher::model()->with('contact')->findAll($criteria);


		$this->render('admin',array(

			'models'=>$models,

			'pages'=>$pages,

			'sort'=>$sort,

		));

	}

I would really love it if some kind soul could take the time out to explain why use CSort works, and setting the order in the criteria does not.

-Charlie

3

I had the same question when I want to sort by related table field.

But my solution is using together() method.

Following document will help you to know what the really SQL query is.

together() method

The with() method will generate several separate SQL query, not one query. That is why the order property of CDbCriteria not to work.