Maybe Bug: Sql Power Function Breaks Cdbcriteria ->With Parsing

Archives Bug Discussions
1

Hi, I think this may be a bug in parsing the POWER function for MySQL in a criteria->with statement when doing a select as (it works in ->having when select as not present) - also the workaround works fine - just manually performing the power function 2x:

    $criteria = new CDbCriteria;

/* this works - manually workaround repeating args */

    $criteria->with = array('place_geometry'=>array('select'=>'place_id,center,'.new CDbExpression('(3956 * 2 * ASIN(SQRT( (SIN(('.$lat.' -  Y(center)) * pi()/180 / 2)* SIN(('.$lat.' -  Y(center)) * pi()/180 / 2)) + COS('.$lat.' * pi()/180 ) * COS( Y(center) *  pi()/180) * (SIN(('.$lon.' - X(center)) *  pi()/180 / 2)*SIN(('.$lon.' - X(center)) *  pi()/180 / 2)) ))) as distance')));

/* this fails using POWER */

$criteria->with = array(‘place_geometry’=>array(‘select’=>‘place_id,center,’.new CDbExpression(’(3956 * 2 * ASIN(SQRT( (POWER( SIN((’.$lat.’ - Y(center)) * pi()/180 / 2),2) + COS(’.$lat.’ * pi()/180 ) * COS( Y(center) * pi()/180) * (SIN((’.$lon.’ - X(center)) * pi()/180 / 2)*SIN((’.$lon.’ - X(center)) * pi()/180 / 2)) ))) as distance’)));

    $criteria->having= "distance <3";


    $criteria->order = "distance ASC";





    $dataProvider = new CActiveDataProvider('Place', 


                        array('criteria' => $criteria,


                        'pagination' => array(


                            'pageSize' => 10,


                        ),


                    ));

  • Mac OS X 10.7

  • MAMP Apache/2.2.23 (Unix)

  • Safari/Chrome

  • Yii version v1.1.13

2

You can first evaluate the CDbExpression and then use it inside the criteria

3

Thanks - can you give an example? Note: ‘center’ is a column result in the query.

This was also meant to be more of a potential bug report - my workaround works but the POWER function wasn’t working in this for some reason.