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



462

For may case, I've registered a custom dialect function in my services.php

/**
 * Database connection is created based in the parameters defined in the configuration file
 */
$di->setShared('db', function() {
    $config = $this->getConfig();

    $dialect = new Phalcon\Db\Dialect\Mysql();
    // Register a new function called TIMESTAMPDIFF_HOUR
    $dialect->registerCustomFunction(
        'TIMESTAMPDIFF_HOUR',
        function($dialect, $expression) {
            $arguments = $expression['arguments'];
            return sprintf(
                " TIMESTAMPDIFF(HOUR, %s, %s)",
                $dialect->getSqlExpression($arguments[0]),
                $dialect->getSqlExpression($arguments[1])
             );
        }
    );

    $class = 'Phalcon\Db\Adapter\Pdo\\'.$config->database->adapter;
    $params = [
        'host' => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname' => $config->database->dbname,
        'charset' => $config->database->charset,
        "dialectClass"  => $dialect
    ];

    $connection = new $class($params);

    return $connection;
});

Then I can use it in my Controller like

        $this->view->geokrety = $this
            ->modelsManager
            ->createBuilder()
            ->from('GkRuchy')
            ->join('GkUsers')
            ->join('GkGeokrety')
            ->join('GkWaypointy')
            ->where('owner_id = :userid:', ['userid' => $this->view->user->user_id])
            ->andWhere('TIMESTAMPDIFF_HOUR(GkRuchy.date_added, NOW()) < 240')
            ->orderBy('date_added ASC, GkRuchy.date ASC')
            ->getQuery()
            ->execute();