Editor - HAVING clause with calculated column

JonathanTolhurst

Using Editor - I have a calculated column (calculates distance from the current position using the Haversine formula):

Field::inst( '( 3959 * acos( cos( radians(:Latitude) ) * cos( radians( asset.lat ) ) * cos( radians( asset.lng ) - radians(:Longitude) ) + sin( radians(:Latitude) ) * sin( radians( asset.lat ) ) ) )', 'asset.distance')->set( false )

For the WHERE clause I have:

    ->where( function ( $q ) {
         $q->bind( ':Latitude', $_REQUEST["lat"] );
         $q->bind( ':Longitude', $_REQUEST["lng"] );
         $q->where( 'asset.lat', null, '!=');
         $q->where( 'asset.lng', null, '!=');
    } ) 

This works fine but I only want to show the closest markers. As chronologically WHERE comes before SELECT (and therefore the calculated column does not exist when the SELECT is performed), I would need to use HAVING. Is there any way to do this using the EDITOR libraries?

If it wasn't a calculated column I would obviously just add:

$q->where( 'asset.distance', 0.25, '<');

allan

I would need to use HAVING. Is there any way to do this using the EDITOR libraries?

Currently no - sorry. The db abstraction Editor uses doesn't have a HAVING option. You could look at adding it, or another option would be to use a VIEW for the more complex read operation.

Allan