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

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 : https://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!

1


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


2.0k
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)



2.0k

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?



37

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


518
in reply to kumy

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