We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

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.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html. As you can see, there is no case explained about fetching computed columns.

Any idea ?

Thanks in advance for your help!



3.9k
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(last_activity), CURRENT_TIMESTAMP()) 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)



1.9k

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

Thanks ever so much, Kumi.

I had the same trouble and that dialect scheme has worked pretty well for me.

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