0

This is my first post on this site, so please be kind.

I have no trouble making this work using the find() method, but when I try to run it using the paginate component, no luck. I am simply trying to run a Haversine formula in the fields options to calculate distance. In the Cake Book, it says "You can also include other find() options, such as fields:". There is no caveat mentioned.

So, this is what I am trying to run:

$this->paginate = array(
    'joins' => array(
        array(
            'table' => 'ride_types',
            'alias' => 'RideTypes',
            'type' => 'inner',
            'conditions'=>array('RideTypes.id = Rides.ride_type')
        )
    ),
    'group' => array(
        "Rides.id HAVING distance < $filter_radius AND ride_time >= '$filter_earliest_time' AND ride_time <= '$filter_latest_time' AND ride_date >= '$filter_earliest_date' AND ride_date <= '$filter_latest_date'"
    ),
    'fields' => array(
        "( 3959 * acos( cos( radians($filter_lat) ) * cos( radians( Rides.lat ) ) * cos( radians( Rides.lng ) - radians($filter_lng) ) + sin( radians($filter_lat) ) * sin( radians( Rides.lat ) ) ) ) AS distance"
    ),
    'limit' => 5,
    'page' => $paginate_page,
    'order' => array('Rides.start_time DESC'),
);

$rides = $this->paginate('Rides',$options['conditions']);

I get this error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'

SQL Query: SELECT COUNT(*) AS count FROM velobuddies.rides AS Rides inner JOIN velobuddies.ride_types AS RideTypes ON (RideTypes.id = Rides.ride_type) WHERE 1 = 1 GROUP BY Rides.id HAVING distance < 5

Again, if I apply all these exact same options to the find('all') method, it works perfectly, so I know the logic is correct... or at least workable.

So what is going on here?

daniel l
  • 3
  • 3
  • Have you tried prefixing 'distance', 'ride_time', etc. with the table name? Eg. HAVING Rides.distance < $filter_radius AND Rides.ride_time >=... – joshua.paling Jun 25 '12 at 22:53

2 Answers2

0

Daniel this post may have the answer although it is based on CakePHP 1.3. It involves binding the Models based on the aggregation.

http://cakephp.1045679.n5.nabble.com/Pagination-with-HasMany-and-SQL-Aggregation-td1307450.html

Leo
  • 1,521
  • 12
  • 18
0

It looks like you may have an issue with your model name. If your application follows the correct CakePHP conventions, then you should be using Ride.field instead of Rides.Field. In CakePHP queries you want to reference the Model and not the table name.

CakePHPaul
  • 1