I have got a table shop and shop_prices.
table shop: id, name. Table shop_prices: id, shop_id, car, price.
How can i select the minimum price for each shop.
In car model i have defined criteria
$criteria=new CDbCriteria;
$criteria->with=array('carsprices');
Then i render it via dataprovider.
Question: 1. how to select minimal prices for each shop? 2. and then i want to limit it, ie I will set maximun price and then limit minimal prices via it.
You should GROUP BY shop_id, SELECT shop_id, MIN(price) AS min_price, and finally add “HAVING min_price < [your_value_here]”. Fortunately CDbCriteria got all these properties 
I trie this one:
$criteria=new CDbCriteria;
$criteria->with=array('carprices'=>array('select'=>'shop_id, MIN(price) as minprice'));
$criteria->addCondition('minprice < 10000');//here is wrong
То что хочу получить
I know how to write my query in SQL:
SELECT shop_id, MIN(price) as minprice FROM shop_prices GROUP BY shop_id HAVING minprice<5000
The problem is in relation with dataProvider:
$criteria=new CDbCriteria;
$criteria->with=array('shopprices'=>array(
'select'=>'shop_id,MIN(price) as minprice',
'group'=>'shop_id',
'having'=>'minprice<5000');
$criteria->addCondition('city_id=1');
The code above returns what i need, and i can access it in view:
array(
'name'=>'minprice',
'type'=>'raw',
'value'=>'$data->shopprices[0]->price',//it's not exactly what i want
),
The question now is: how can i access cut dataprovider data and get sortable column called minprice?
Try to add a public $minprice to your model.
pulbic $minprice exists;
I want minprice column to be displayed. And i want to sort it like other columns.
As i said before, i dont want access it like this: $data->shopprices[0]->price.
Below DataProvider code.
<?php
...........
$provider=$model->searchAdv();
$provider->pagination->pageSize=20;
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'shop-grid',
'dataProvider'=>$provider,
'columns'=>array(
array(
'name'=>'shop',
'type'=>'html',
'value'=>'CHtml::link(CHtml::encode($data->shop), array("shop/view","id"=>$data->id))',
),
'adress',
array(
'name'=>'minprice',
'type'=>'raw',
'value'=>'$data->minprice',
),
..............
)); ?>
Code sims to be fine. Whats may be wrong?
Guys, lets solve it. People will find this topic through search engines and will be disappointed by not receiving an answer 
To get minprice sorted you could use this
<?php
...........
$sort = new CSort();
$sort->attributes = array('shop','adress','minprice');
$provider=$model->searchAdv();
$provider->pagination->pageSize=20;
$provider->sort = $sort;
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'shop-grid',
'dataProvider'=>$provider,
'columns'=>array(
array(
'name'=>'shop',
'type'=>'html',
'value'=>'CHtml::link(CHtml::encode($data->shop), array("shop/view","id"=>$data->id))',
),
'adress',
array(
'name'=>'minprice',
'type'=>'raw',
'value'=>'$data->minprice',
),
..............
)); ?>
Tell me please, why Yii firstly makes this query:
Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `shop` `t` LEFT OUTER
JOIN `shop_prices` `prices` ON (`prices`.`shop_id`=`t`.`id`) WHERE
(city_id=1)
and then this:
Querying SQL: SELECT `t`.`id` AS `t0_c0`, `prices`.`shop_id` AS `t1_c1`,
MIN(price) as minprice, `prices`.`id` AS `t1_c0` FROM `sho` `t` LEFT OUTER
JOIN `shoppricess` `prices` ON (`prices`.`shop_id`=`t`.`id`) WHERE
(`t`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,
18, 19, 20)) GROUP BY shop_id HAVING (minprice<5000)
I think: it must make this two in one query. What wrong?
Just for test i wrote this code:
$criteria=new CDbCriteria;
$criteria->select='MIN(price) as minprice';
$criteria->join='LEFT OUTER JOIN `shopprices` `prices` ON (`prices`.`shop_id`=`t`.`id`)';
$criteria->group='shop_id';
$criteria->having='minprice<5000';
$criteria->addCondition('city_id='.$this->city_id);
$criteria->addCondition('id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,18, 19, 20)');
But SQL log return this:
Querying SQL: SELECT COUNT(DISTINCT shop_id) FROM `shop` `t` LEFT OUTER
JOIN `shopprices` `prices` ON (`prices`.`shop_id`=`t`.`id`) WHERE
(city_id=1) AND (id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17,18, 19, 20)) GROUP BY shop_id HAVING minprice<5000
Where have my "select" expression lost?
does not react to any changes of “select” expression 
Problem was in this line:
...
$criteria->having='minprice<5000';
...
whats wrong with it?
instead of it i used:
$criteria->addCondition('`prices`.`price` BETWEEN 1 AND 4000');
But dataprovider, dont let me sort the minprice table. When i’ve used this code:
$provider->sort->attributes=array('minprice');
clicking on columns header popups a new window with raw html. In firefox popups windows alert: memory can’t be read 
having works if only used as:
$criteria->having='MIN(price) < 5000';
The pager needs to know the total number of results (query 1) and then query only the results for the current page (query 2).
Thanks. Can you answer to latest questions?
Uhm, you posted many questions. What is your latest one?
Most of your problems are rather MySQL (or SQL) related than Yii related. So maybe check out the MySQL reference first.
i check my queries in SQL and then i’m trying to convert it to YII.
One of the question is: Why i can’t use
$criteria->having='minprice < 5000';
and only can use:
$criteria->having='MIN(price) < 5000';
?
For example: i use query:
3956000 * 2 * ASIN(SQRT(
POWER(SIN((latitude - abs(55.75168113348126)) * pi()/180 / 2),
2) + COS(latitude * pi()/180 ) * COS(abs(55.75168113348126) *
pi()/180) * POWER(SIN((longitude- 37.5655871629715) *
pi()/180 / 2), 2) )) as distance';
and i can’t use it in having, such as:
$criteria->having='distance < 5000';
Does it work in pure SQL?
Oh, and please always give an error message, instead of only “it doesn’t work”.