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.