using your own mysql functions

Yii 1.1 General Discussion
1

hi there,

i wrote my own mysql funtion, and i want to run it from in side yii. but it keeps telling me:


Active record "Daytrip" is trying to select an invalid column "geodistkm(53.2095685969". Note, the column must exist in the table or be an expression with alias.

here is the code

model:




    public function relations() {

        return array(

            'rCity' => array(self::BELONGS_TO, 'City', 'CityId'),

            'rCategory' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)'),

            'rTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)'),

            'rTag2' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',

                'select' => array('GROUP_CONCAT(Tag SEPARATOR ", ") AS gTags'),

                'group' => 'DaytripId'

            )

        );

    }

    

    public function scopes()

    {

        return array(

            'distance' => array(

                'limit' => 100,

                'select' => "geodistkm($this->latitude, $this->longitude, rCity.Latitude, rCity.Longitude) AS distance"

            )

        );

    }

controller:




$daytrips = Daytrip::model()->distance()->with('rCity')->findAll();

print_r($daytrips);
2

CDbExpression is your friend :)

3 

    public function scopes()

    {

        return array(

            'distance' => array(

                'limit' => 10,

                'select' => array(new CDbExpression("geodistkm($this->latitude, $this->longitude, rCity.Latitude, rCity.Longitude) AS distance"))

            )

        );

    }

wil give me the following query:




SELECT geodistkm(53.2095685969, 6.56982421875,

rCity.Latitude, rCity.Longitude) AS afstand, `Daytrips`.`IdDaytrip` AS

`t0_c0`, `rCity`.`IdCity` AS `t1_c0`, `rCity`.`CountryId` AS `t1_c1`,

`rCity`.`Province` AS `t1_c2`, `rCity`.`Municipality` AS `t1_c3`,

`rCity`.`City` AS `t1_c4`, `rCity`.`Latitude` AS `t1_c5`,

`rCity`.`Longitude` AS `t1_c6`, `rCity`.`GoogleMatch` AS `t1_c7` FROM

`Daytrips`  LEFT OUTER JOIN `Cities` `rCity` ON

(`Daytrips`.`CityId`=`rCity`.`IdCity`) LIMIT 10

now it was giving me the error: Property “Daytrip.distance” is not defined. so i created a public field named $distance in the model but when i print the distance in the view it’s will remain empty…

view:


<?php echo $daytrip->distance; ?>
4

I am quite new working like that, but :

SELECT geodistkm(53.2095685969, 6.56982421875, rCity.Latitude, rCity.Longitude) AS afstand

afstand should be distance ?

5 

'select' => array('Daytrip', new CDbExpression("geodistkm($this->Latitude, $this->Longitude, rCity.Latitude, rCity.Longitude) AS Distance")),

I had to use an array.

Another question is the select from the scope overwriting the select from findAll()