Findall, Order By Count Using Stat Relation?

Yii 1.1 General Discussion
1

Hi

I know there’s quite a lot topics on this subject, but i still havent managed to find a solution that works for me…

First of all I want to show what I would want/wish to do (happens in my model):


$findMerchants=Merchants::model()->findAll(array(

	'condition'=>'status = :status',

	'params'=>array(':status'=>'Active'),

        'with'=>array(

		'merchantsubscription'=>array(

			'select'=>false,

			'condition'=>'merchantsubscription.subscription != :subscription AND merchantsubscription.subscriptionStatus = :subscriptionStatus',

			'params'=>array(':subscription'=>'Cotton',':subscriptionStatus'=>'Active'),

		),

		'merchantRankScore'=>array(),

	),

	'order'=>'merchantRankScore DESC',

));





'merchantRankScore' => array(self::STAT, 'Reviewsrating', 'merchantId','select'=> 'SUM(totalscore)', 'condition'=>'status="Active"'),

As I’ve read, this cannot be done this way - something with stat relations work in another way…

But there must be some kind of a work-around for this…???

I’ve read these:

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

http://www.yiiframework.com/forum/index.php/topic/8015-order-by-count-using-stat-relation/

among others, but still havent been able to produce a solution that works for me… :confused:

Can anybody help with a work-around solution for this one? :)

// Casper

2

None at all…? :(

Really hope someone can help me with this one…

3

Hi casperskou,

Thank you for reading my wiki article.

I would write something like the following:




    $criteria=new CDbCriteria;

 

    // sub query to retrieve the rank score

    $review_table = Reviewsrating::model()->tableName();

    $rank_score_sql = "

(SELECT SUM(totalscore)

FROM $review_table rt

WHERE rt.merchantid = t.id 

AND rt.status = \"Active\")

";


    // select

    $criteria->select = array(

        '*',

        $rank_score_sql . " as rank_score",

    );


    // join

    $criteria->with = array(

        'merchantsubscription' => array(

            'select' => false,

        ),

    );

    

    // condition

    $criteria->compare('t.status', 'Active');

    $criteria->compare('merchantsubscription.subscription', '<>'.'Cotton');

    $criteria->compare('merchantsubscription.subscriptionStatus', 'Active');

    

    // order

    $criteria->order = 'rank_score DESC';


    $merchants = Merchants::model()->findAll($criteria);
4

Wow, your a genius!!

Seems to work like a charm - I now have a working ranking system :D

Thank you VERY much softark!

If anyones interested, this is my full code:


$criteria=new CDbCriteria;

 

// sub query to retrieve the rank score

$review_table = Reviewsrating::model()->tableName();

$rank_score_sql = "

	(SELECT SUM(totalscore)

	FROM $review_table rt

	WHERE rt.merchantid = t.id 

	AND rt.status = \"Active\")

";


// select

$criteria->select = array(

        '*',

        $rank_score_sql . " as rank_score",

);


// join

$criteria->with = array(

       	'merchantsubscription' => array(

            	'select' => false,

        ),

);

    

// condition

$criteria->compare('t.status', 'Active');

$criteria->compare('merchantsubscription.subscription', '<>'.'Cotton');

$criteria->compare('merchantsubscription.subscriptionStatus', 'Active');

    

// order

$criteria->order = 'rank_score DESC';


$findMerchants = Merchants::model()->findAll($criteria);

				

$i = 1;

foreach($findMerchants as $merchant)

{

	if($merchant->id == $this->id)

	{

		$this->_rankMerchants = $i;

	}

        $i++; 

}