We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Phalcon query builder unable to use alias in having

I am trying to reproduce the following query using the QueryBuilder so I can use the paginator:

SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM table HAVING distance < $radius ORDER BY distance;

This is my attempt:

$builder = $this->modelsManager->createBuilder()
        ->columns(['*', 'distance' => '(6371 * acos(cos(radians(' . $lat . ')) * cos(radians(lat)) * cos(radians(lon) - radians(' . $lon . ')) + sin(radians(' . $lat . ')) * sin(radians(lat))))'])
        ->from('Multiple\Backend\Models\Posts')
        ->having('distance < ' . (int) $radius)
        ->where('module_id = ' . (int) $moduleId)
        ->orderBy('id DESC');

This is the error it produces:

Fatal error: Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Column 'distance' doesn't belong to any of the selected models (1), when preparing: SELECT COUNT(*) [rowcount] FROM [Multiple\Backend\Models\Posts] WHERE module_id = 21 HAVING distance < 100' in /Applications/MAMP/htdocs/click_v2/apps/webservice/controllers/PostsController.php on line 78

If I comment the line ->having('distance < ' . (int) $radius) then it works fine and calculates the correct distance but does not filter the results.

How do I use the distance alias in that having or where clause?

edited Apr '16
->columns(['*', 'distance' => '(6371 * acos(cos(radians(' . $lat . ')) * cos(radians(lat)) * cos(radians(lon) - radians(' . $lon . ')) + sin(radians(' . $lat . ')) * sin(radians(lat))))'])

What is this ? From where you got this syntax? I not sure you can type it like that. Why you not using binding ?!

It shoud look like this:

$this->modelsManager->createBuilder()
        ->columns(['*', '(6371 * acos(cos(radians(:lat:)) * cos(radians(lat)) * cos(radians(lon) - radians(:lon:)) + sin(radians(:lat:)) * sin(radians(lat)))) as distance'])
        ->from('Multiple\Backend\Models\Posts')
        ->having('distance < :radius:')
        ->where('module_id = :moduleId:')
        ->orderBy('id DESC')
        ->execute(['lat'=>$lat,'lon'=>$lon,'radius'=>radius,'moduleId'=>$moduleId);

Also with bind params and metadata phalcon will automatically figure out bind types. No reason to cast to int.

I don't think anything of what you said answers my question.

Here's the complete code if it helps. I don't know how to use bindings with the paginator. Anyway, that's not the issue.

$builder = $this->modelsManager->createBuilder()
        ->columns(['*', '(6371 * acos(cos(radians(' . $lat . ')) * cos(radians(lat)) * cos(radians(lon) - radians(' . $lon . ')) + sin(radians(' . $lat . ')) * sin(radians(lat)))) AS distance'])
        ->from('Multiple\Backend\Models\Posts')
        ->having('distance < ' . (int) $radius)
        ->where('module_id = ' . (int) $moduleId)
        ->orderBy('id DESC');

    $paginator = new PaginatorQueryBuilder(
        array(
            "conditions" => "active = 1",
            "builder" => $builder,
            "limit"   => $limit,
            "page"    => $this->request->hasQuery('p') ? $this->request->getQuery('p') : 1
        )
    );

    $page = $paginator->getPaginate();
edited Apr '16

And with as is not working ? Well it's one of reason why im not using paginator(not supporting bind parameteres, im just typing offset and limit myself to dont have variables in selects).



125.8k
Accepted
answer

What about not aliasing the column, and re-doing the calculations in the having clause. Terribly inefficient I know - but just try it to see if it works.

You should and absolutely can use binding with the Query Builder. Just pass the tokens & values in an array as the 2nd parameter. For example, your where clause can be:

->where('module_id = :module:',['module'=>(int)$moduleId])

Oh well, in this case it should work as you write above, i think i had same problem in past. You can either use raw queries(then you can use alias) or calculate value again. It's the common problem with PHP ORMs(at least with doctrine is the same).

Re-doing the calculations in the having clause did not work but in the where clause did. Also, thanks for the binding explanation.

What about not aliasing the column, and re-doing the calculations in the having clause. Terribly inefficient I know - but just try it to see if it works.

You should and absolutely can use binding with the Query Builder. Just pass the tokens & values in an array as the 2nd parameter. For example, your where clause can be:

->where('module_id = :module:',['module'=>(int)$moduleId])

What mean did not work ? What error you have ? For me redoing calculations in having clause did the work.

Well for some reason calling lat or lon fields in having doesn't work. It says posts.lat or posts.lon is not found.

What mean did not work ? What error you have ? For me redoing calculations in having clause did the work.

Well. Im guessing that you would need to use full namespace. Try to use alias for namespace:

->from(['Posts'=>'Multiple\Backend\Models\Posts'])

And then try Posts.lon and Posts.lat in having