Sorting & Limit Problem

Hello, I just want to list the “16 Newest Products” of my online shop (Im just ordering by t.‘product_id’ DESC), buy I want to be ablle to sort them by name or price from the list view. The problem is that even if I set the $limit parameter of criteria to 16 in the CActiveDataProvider, when I sort by price or name from the view, it gets all the products (not the limited ones) and sort among ALL the products, not the 16 newest. I tried with double sorting but obiously it doesnt work because product_id is unique. I want to get only that 16 newest product and order them in a clean way.

So, how can I sort the 16 newest products by name or price?

This is my current not working code:







$newestProducts = new CActiveDataProvider('Product', array(

                    'criteria' => array(

                        'limit' => 16,

                        'group' => '`t`.product_id',

                    ),

                    "sort" => array(

                        'attributes' => array(

                            'name',

                            'price',

                        ),

                        "defaultOrder" => array("product_id" => true), //TODO: Change to CSort::SORT_ASC with 1.1.10

                    ),

                    'pagination' => false,

                ));




You can use a subquery for collect the product:


$newestProducts = new CActiveDataProvider('Product', array(

                    'criteria' => array(

                        'group' => '`t`.product_id',

                        'condition'=>'t.product_id IN (SELECT product_id FROM product limit 16)'

                    ),

                    "sort" => array(

                        'attributes' => array(

                            'name',

                            'price',

                        ),

                    ),

                    'pagination' => false,

                ));

The where condotion (rearranged if there are mysql errors) will limit the search to the first 16 element (so you don’t have to use limit in the outside query).

In this situation, having exactly 16 elements in the subquery I think that the subquery is more efficent than using join

Many thanks Zaccaria,

it throws an error: “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’”

http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

If it doesn’t have to be always exactly 16 products, you could probably get away with something hideous like:




'criteria' => array(

    'group' => '`t`.product_id',

    'condition'=>'t.product_id > (SELECT MAX(product_id) FROM product) - 16',

),



No idea if that query would work, and I’m sure it would make the baby Jesus cry.




$criteria = new CDbCriteria();

$criteria->select = "product_id";

$criteria->order = "product_id desc";

$criteria->limit = 16;

$latestProducts = Product::model()->findAll($criteria);

$latestIds = array();

foreach($latestProducts as $p)

    $latestIds[] = $p->product_id;


$criteria2 = new CDbCriteria();

$criteria2->addInCondition('product_id', $latestIds);

$dataProvider = new CActiveDataProvider('Product', array(

    'criteria' => $criteria2,

    'sort' => array(

        'defaultOrder' => 'product_id desc',

    ),

    'paginatin' => false,

));



This seems a bit noobish solution, but I think it’s not too bad. :)

Dear Aleksdj

Would you please try this?

The whole idea is to use CArrayDataProvider

In controller




public function actionIndex()

	

	{  $criteria=new CDbCriteria;

	   $criteria->order='product_id Desc';

	   $criteria->limit=16;

	   $products=Product::model()->findAll($criteria);

		$dataProvider=new CArrayDataProvider($products,array(

		     'sort'=>array('attributes'=>array('price','name')),

		     'pagination'=>false,

		)

		);

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

			'dataProvider'=>$dataProvider,

		));

	}






In index.php




<?php $this->widget('zii.widgets.CListView', array(

	'dataProvider'=>$dataProvider,

	'itemView'=>'_view',

	'sortableAttributes'=>array('name','price'),

)); ?>