Phalcon query builder unable to use alias in having

Question

Phalcon query builder unable to use alias in having

Miguel Nunes Apr '16

Created Apr '16	Last Reply Apr '16	Replies 6	Views 848	Votes 0

Miguel Nunes 10.4k

Apr '16

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?

Dylan Anderson
125.8k

Accepted
answer

Apr '16

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])

Wojciech Ślawski · Answer 1 · 2016-04-28T11:00:31-07:00

->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.

Miguel Nunes · Answer 2 · 2016-04-28T13:05:15-07:00

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();

Wojciech Ślawski · Answer 3 · 2016-04-28T14:07:11-07:00

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).

Wojciech Ślawski · Answer 4 · 2016-04-29T09:36:42-07:00

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).

Miguel Nunes · Answer 5 · 2016-04-30T07:10:17-07:00

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])

Wojciech Ślawski · Answer 6 · 2016-04-30T08:12:40-07:00

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

Miguel Nunes · Answer 7 · 2016-04-30T10:08:28-07:00

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.

Wojciech Ślawski · Answer 8 · 2016-04-30T11:10:34-07:00

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