Product Search - Sorting by Dynamic Model Attribute using a CActiveDataProvider

Hi all,

I currently have a search results page that compares the search string against the name and description of a Product model. A CActiveDataProvider is passed back to my results view and is utilised by a CListView.

I’ve added sorting for the name and dateAdded attributes of the Product model but I also want to add an option to sort using the Product “from price”.

The issue I’m having is the “from price” is a calculated attribute accessed via a method, getFromPrice(), in the Product model. There’s no way of using any columns in the MySQL database to generate the same from price, due to the price variables being fetched from a secondary SQL database.

Short of caching each Product “from price” against each record (something I don’t really want to do as the from price is changeable) is there any way of achieving this sort using the getFromPrice() method?

Here’s my code to return the CActiveDataProvider:




	// create a new criteria object

	$criteria = new CDbCriteria();

	

	// add the comparison items to the criteria

	$criteria->compare('name', $query, true, 'OR');

	$criteria->compare('description', $query, true, 'OR');

	$criteria->compare('orderID', 0, false, 'AND');

	$criteria->compare('enabled', 1, false, 'AND');


	// return the data provider

	return new CActiveDataProvider($this, array(

			'criteria' => $criteria,

			'pagination' => array(

				'pageSize' => 10,

			),

			'sort' => array(

				'defaultOrder' => array(

					'name' => CSort::SORT_ASC,

				),

				'attributes' => array(

					'name',

					'dateAdded',

				),

			),

		)

	);



Any help would be appreciated.

Thanks

Hi

You will probably have to go with CArrayDataprovider.

Have a look at this post

CActiveDataProvider (and CSqlDataProvider) can sort by a certain attribute of a model only when it can be translated to an ‘ORDER BY’ clause.

  1. It may be possible to construct such an ‘ORDER BY’ clause for the ‘fromPrice’.

But it might be virtually impossible when it is very complicated.

It may be reasonably fast, or incredibly slow. It depends.

  1. CArrayDataProvider will work fine when the total number of the query result is not so big.

You have to give ALL the records to it, not just only the records for the current page.

  1. IMO, denormalizing the db schema is worth considering when the sorting on ‘fromPrice’ is very important.

Hi Softark

I’m just curious. What is the situation regarding this in Yii2?

Hi Gerhard,

As far as I understand, it’s just the same for Yii 2.0 in this respect.

Database handling of Yii 2 looks much more "naked" than in Yii 1.1 to me. The gap between the DAO and the ActiveRecord (AcitveDataProvider) is narrower.

@lcham

If you are calculating ‘fromPrice’ with something like:




select min(price) from item where item.product_id = product_id



then all you have to do is include it as a sub query in the query of Product.




    // sub query to retrieve the min price

    $item_table = Item::model()->tableName();

    $min_price_sql = "(select min(price) from $item_table it where it.product_id = t.id)";

 

    // select

    $criteria->select = array(

        '*',

        $min_price_sql . " as fromPrice",

    );



The following wiki is for "count(*)", but is easily adopted for "min(something)".

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

Thank you all for your responses.

@Gerhard

Thanks

I did come across a few examples of using CArrayDataprovider instead, but I did think performance would suffer, hence me asking the question about whether using the fromPrice method is possible first.

I will take a look though and see what the performance is like.

@softark

Thanks for your detailed reply.

Unfortunately the link to grab the price from the SQL database is variable, so it’s not as simple as joining directly to the table. There’s also a lot of business logic in other related models, that are variable, from Product option selections. It’s all a bit complicated really!

As an aside, I do cache the from price using Yii’s built in cache. Do you know if that’s usable in this scenario?

If not I will try using a CArrayDataprovider instead and if that doesn’t give me the performance I require I think I’ll have to cache the price against each of the products and have it refresh every 30 minutes or so.

Hi Icham

If you have the from-price available, you could customize the query.

Have a look at these examples

Thanks Gerhard, but I used a CArrayDataProvider in the end and it didn’t have a detrimental effect on performance.