Strange relational behavior

I have a Company model (with table ‘company’) which has a many-to-many relation with the Extra model through table ‘company_extra’.

I also have a difficult query to get nearby companies with given coordinates. I do this by:




<?php

$criteria->select = "name, address, t.id, lat, lng, ROUND((GLENGTH( LINESTRING( (POINTFROMWKB( POINT( ".$coords['lat'].", ".$coords['lng']." ) ) ) , (POINTFROMWKB( POINT( t.lat, t.lng ) ) )))) *100) AS distance";

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

$criteria->order = "distance ASC";


Company::model()->active()->findAll($criteria);

?>

This gives the following SQL query:


SELECT name, address, t.id, lat, lng,

ROUND((GLENGTH( LINESTRING( (POINTFROMWKB( POINT( 51.6869459, 5.1377510 ) )

) , (POINTFROMWKB( POINT( t.lat, t.lng ) ) )))) *100) AS distance FROM

`company` `t` WHERE isActive = 1 HAVING distance <= 20 ORDER BY distance

ASC LIMIT 10

Which is giving the right results and is working totally fine… until i set the relation with ‘Extra’:


<?php

$so = array(4,6,8,9);


$criteria->select = "name, address, t.id, lat, lng, ROUND((GLENGTH( LINESTRING( (POINTFROMWKB( POINT( ".$coords['lat'].", ".$coords['lng']." ) ) ) , (POINTFROMWKB( POINT( t.lat, t.lng ) ) )))) *100) AS distance";

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

$criteria->order = "distance ASC";

$criteria->with = 'extras';

$criteria->compare('extraId', $so);


Company::model()->active()->findAll($criteria);

?>

I get the following error:

Why do i get this error? I tried the query (with the relation AND the ‘distance’ select by itself in phpmyadmin and that’s working ok.

Try displaying the errors on your page, in real time.

/protected/config/‘whatever your config file is called’


// uncomment the following to show log messages on web pages

				

//				array(

//					'class'=>'CWebLogRoute',

//				),

It might help you understand. A quick look suggest to me that you may need to put quotes around ‘ROUND((GLENGTH( LINESTRING( (POINTFROMWKB( POINT( “.$coords[‘lat’].”, “.$coords[‘lng’].” ) ) ) , (POINTFROMWKB( POINT( t.lat, t.lng ) ) )))) *100)’

It looks a little complex so more info might help others help you.

doodle

See this thread. Looks like a very similar case (problem occurs when adding with).

/Tommy

Yeah, i already use CWebLogRoute, but it doesn’t give me much information (is also doesn’t show the whole query which it tries to execute).

Thanks! When i use a array in my select criteria it seems to work fine! :)

Do you know the reason behind this?

As far as I know, the array should always be used if the column expression has commas in it. So it’s a bit strange it worked before.

/Tommy