CODEWITHSUNDEEP
selectyiimaxrelational
xiaohan2012
xiaohan2012

Reputation: 10332

Yii fail to retrieve max column value

I have two models, one is Auction, the other is Bid.

An Auction has many Bids. they are associated by foreign key auction_id in Bid

Now, I want to find the max value of the Bid's price for each Auction.

$dataProvider = new CActiveDataProvider('Auction', array('criteria' => array(
                    'with' => array(
                        'bids' => array(
                            'alias'=>'b',
                            'group' => 'auction_id',
                            'select' => 'max(b.price) as maxprice'
                        )
                   )
             )
        )
);

And I have defined a maxprice property in Auction's model class.

However, if I try to retrieve the maxprice property, it returns NULL.

To be more specific, I render the $dataprovider to a view page, it fails to get the maxprice property.

PS:

I executed the query in mysql, the query result turns out to be correct.

So, there must be something wrong with the Yii code

SQL code:

SELECT `t`.`id` , max(b.price) as maxprice

FROM `auction` `t` 

LEFT OUTER JOIN `bid` `b` ON (`b`.`auction_id`=`t`.`id`)  GROUP BY auction_id

Upvotes: 0

Views: 2990

Answers (1)

ldg
ldg

Reputation: 9402

Put the value you want before the relation, like so:

$dataProvider = new CActiveDataProvider('Auction', array('criteria' => array(
    'select' => 't.*, max(b.price) as maxprice',
    'with' => array(
        'bids' => array(
            'alias'=>'b',
            'group' => 'auction_id',
            'together'=>true,
        )

You can replace the "t.*" with specific field names if you like.

OR you can simply use the select, join and group attributes on your Auction model and skip the relation altogether.

Upvotes: 1

Related Questions