CakePHP - Virtual field issue using Paginator Helper

Question

I have a Listings table with lat/long fields. I'm using the Haversine Formula to calculate the distance (as an alias/virtual field) between an origin point (33.987339, -81.036819) and the lat/long of each Listing and returning the listings with a distance within 10 miles of the origin point.

The following SQL query in phpMyAdmin returns exactly what I expect:

SELECT *, round(3959 * acos(cos(radians(33.987339)) * cos(radians(Listing.lat)) * cos(radians(Listing.long) - radians(-81.036819)) + sin( radians(33.987339)) * sin(radians(Listing.lat)))) 
AS distance, `Listing`.`id` 
FROM `preview_site`.`listings` AS `Listing` 
LEFT JOIN `preview_site`.`users` AS `User` ON (`Listing`.`user_id` = `User`.`id`) 
LEFT JOIN `preview_site`.`categories` AS `Category` ON (`Listing`.`category_id` =   `Category`.`id`) 
LEFT JOIN `preview_site`.`states` AS `State` ON (`Listing`.`state_id` = `State`.`id`) 
WHERE `Listing`.`status` = 'Active' 
HAVING distance < 10 
ORDER BY `distance` ASC LIMIT 20

After attempting (and failing several ways) to get the CakePHP code to correctly generate the above SQL, I used this tool to generate the following CakePHP controller code (it gave both Model and Controller options) from the SQL:

$this->Paginator->virtualFields = array(
'distance' => 'round(3959 * acos(cos(radians(33.987339)) * cos(radians(Listing.lat )) * cos(radians(Listing.long) - radians(-81.036819)) + sin(radians(33.987339)) * sin(radians(Listing.lat))))');
$this->Paginator->settings = array(
'fields' => array(
    'Listing.*',
    'Listing.distance',
    'Listing.id',
    'Category.*',
    'State.*',
    'User.*',
),
'joins' => array(

    array(
        'conditions' => array(
            'Listing.user_id = UserJoin.id',
        ),
        'table' => 'users',
        'alias' => 'UserJoin',
        'type' => 'left',
    ),
    array(
        'conditions' => array(
            'Listing.category_id = CatJoin.id',
        ),
        'table' => 'categories',
        'alias' => 'CatJoin',
        'type' => 'left',
    ),
    array(
        'conditions' => array(
            'Listing.state_id = StateJoin.id',
        ),
        'table' => 'states',
        'alias' => 'StateJoin',
        'type' => 'left',
    ),
),
'conditions' => array(
    'Listing.status' => 'Active',
),
'order' => array(
    'distance' => 'asc',
),
'limit' => '5',
'having' => array(
    'distance <' => '10',
),
'contain' => array(
    'User',
    'Category',
    'State',
),
);
$data = $this->Paginator->paginate('Listing');
$this->set('listings', $data);

If I use this code, I get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Listing.distance' in 'field list'

If I change $this->Paginator->virtualFields to $this->Listing->virtualFields (as I could not find any documentation on Paginator actually using the virtualFields method), I don't get any errors and the pagination works fine, but the returned results are not limited by the distance (all Listing records are returned). Here's a snippet of the generated SQL with the distance alias:

SELECT `Listing`.*, `Listing`.`id`, `Category`.*, `State`.*, `User`.*, (round(3959 *  acos(cos(radians(33.987339)) * cos(radians(`Listing`.`lat` )) *  cos(radians(`Listing`.`long`) - radians(-81.036819)) + sin(radians(33.987339)) *  sin(radians(`Listing`.`lat`))))) 
AS `Listing__distance` 
FROM `preview_site`.`listings` AS `Listing`

Does anyone have any suggestions for how to make this work correctly? ANY help would be greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

I think where your problem is coming from is CakePHP does not recognize "Having", I believe. Since you don't seem to have a Group By, you can just use a regular WHERE and get the same results, in this case, array('conditions' => array('distance <' => 10)) If you do have a Group By though, see the below:

CakePHP: How can I use a "HAVING" operation when building queries with find method?

edited May 23 '17 at 12:28

Community

1
1

answered Dec 06 '13 at 00:06

Kai

3,803
1
16
33

Ha! Earlier in the process, I did have the "Having" operation in with the "Group by" line, but when I removed "Group by," I didn't realize I could just add it in with my other WHERE conditions. I added it there and it works perfectly now, thanks so much! – user1114191 Dec 06 '13 at 16:14

mark · Answer 2 · 2013-12-06T00:22:52.787

There are quite a few open tickets regarding virtual fields. This might well be one of them.

Even though your initial binding to paginator looks off. You should add virtual fields to the current model, so Listing.

$this->Listing->virtualFields['distance'] = ...

For me, in those scenarios where I could not easily use the virtual field, it helped to manually use the aliased field, so Listing__distance ASC in your order or more importantly in your having clause. It will also reuse the already calculated field instead of doing it again (even though I don't know if there is a speed improvement here this way). See this.

Also note that it might be cleaner to leverage a behavior to avoid repeating that for other queries (and to keep it DRY):

$this->Listing->setDistanceAsVirtualField($lag, $lng);

And I usually use conditions to limit the distance (no need for having, is there?).

You're right, there is no need for Having, it works fine as a condition. I will look into using a behavior in the future. Thank you for your response! If this was Reddit, I'd give you and Kai gold :) — user1114191, Dec 06 '13 at 16:31

CakePHP - Virtual field issue using Paginator Helper

2 Answers2