Help Sorting One-to-Many

Yii 1.1 General Discussion
1

I have a table "business". Each business has many businessPromos (a one-to-many relationship). A businessPromo has a createtime. How can I order a query for businesses by the createtime of the first businessPromo of each business? (I have the businessPromos for each business ordered by createtime DESC)

Thanks!

2

I would suggest this:

  1. make a new statistical relation ‘firstPromo’ to get the first businessPromo for each business (http://www.yiiframework.com/doc/guide/1.1/en/database.arr#statistical-query)

  2. use that relation in the query


$business=Business::model()->with('firstPromo')->findAll()
3

This works well for fetching and displaying the date of first businessPromo but you probably can’t order the result by stat relation.

It seems to be far from trivial but findAllBySql() may work (not tested):




$model->findAllBySql(

	'SELECT * FROM business t1

	LEFT JOIN (

		SELECT t2.business_id, t2.createtime

		FROM businessPromo t2

		LEFT JOIN businessPromo t3

		ON t2.business_id = t3.business_id AND t2.createtime > t3.createtime

		WHERE t3.id IS NULL

	) t4 ON t1.id = t4.business_id

	ORDER BY t4.createtime'

);

I don’t know if it can be done by CDbCriteria.

4

You can simply add the sort in the criteria:


$criteria->sort="t4.createtime";

Just check in the log that the alias t4 is given to the right table.