Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to fetch computed columns using the query builder ?

Hi there,

I'm having troubles to fetch computed columns using the query builder. My concrete situtation is the following : I've an user list and I want to display in this list if the member is logged in or not. So my computation is currently based on the last activity user's. Something like :

SELECT username, (TIMESTAMPDIFF(MINUTE, TIMESTAMP(last_activity), CURRENT_TIMESTAMP()) < 20) AS logged_in FROM user LIMIT 30;

But how can I use the query builder ? This is an important point because I've built my own model layer over the phalcon models. It allows me to fetch every columns I want in every models with automatic joins. All columns, except computed columns :(

Here is the link to the referred doc : http://docs.phalconphp.com/en/latest/api/Phalcon%5Mvc%5Model%5Query%5Builder.html. As you can see, there is no case explained about fetching computed columns.

Any idea ?

Thanks in advance for your help!



2.3k
Accepted
answer
edited Mar '14

Just add computed columns in the same way as regular columns:

    $builder->columns(array(
            'username',
            'TIMESTAMPDIFF(MINUTE, TIMESTAMP(last_activity), CURRENT_TIMESTAMP()) as tsdiff'))
        ->from('user')
        ->where('tsdiff < 20')
        ->limit(10);

I see that you're simply doing a timestamp calculation - consider just sending in a relevant where clause with prepared parameters. The exact format will depend on your field content:

    $builder->columns(array(
            'username',
            'last_activity'
        ->from('user')
        ->where('last_activity < :t:', array('t' => strtotime('-20 minutes')))
        ->limit(10);
edited Mar '14

hi! Thank you for your answer!

I've test what you suggest but it cannot compute a valid query. It generates me an error :

Phalcon\Mvc\Model\Exception: Column 'MINUTE' doesn't belong to any of the selected models (1), when preparing: SELECT id, username, TIMESTAMPDIFF(MINUTE, TIMESTAMP(lastactivity), CURRENTTIMESTAMP()) as tsdiff FROM [User] AS [u] ORDER BY id asc LIMIT 15

I cannot figure out why it interpretes "minute" as a column. Maybe the keywords list's is not complete in the orm code ? Plus I can't use it as where statement because this is not a filter criteria (I need all results and some of them flagged)

In fact I misinterpret the resolve button with the "vote button". So my issue isn't solved.

What about this "minute" sql keyword ? Should I create another thread in the forum ?



49
edited Apr '15

I have this problem, I use:

$builder->columns('TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) as age, COUNT(u.id) as total')

Result: Column 'YEAR' doesn't belong to any of the selected models (1)

How do resolve it?

Same bug

->columns(array('id', 'email', 'firstname', 'lastname', 'TIMESTAMPDIFF( YEAR ,birthday,CURDATE()) AS age')); Result: Column 'YEAR' doesn't belong to any of the selected models (1)