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

Querying view

Hi All. I am a first time user of Phalcon and I have a question about the Model part of Phalcon.

Consider this query "SELECT user_id, first_name, last_name, totalSalesFigure(user_id) as sales_figure FROM users "; totalSalesFigure is a stored function on the database.

Is there anyway to load the query in the model USERS itself and reference it like this? $user = Users::findFirst('user_id = 3'); // loads the user with user_id = 3 echo $user->sales_figure; // outputs the salesFigure of this user.

Thanks

Hello

As far as I can see you can always do

$user = Users::findFirst('user_id = 3');

which will get the user. However the stored procedure will not be executed if you do

$user->sales_figure

since the model doesn't know about this. The reason behind it is that the stored procedure is specific to MySQL and Phalcon ORM doesn't know about it.

This is something we need to discuss about addressing (if we can) in the ORM. The reason behind it is that different RDBMSs have different implementations with regards to stored procedures and some do not support it. The ORM is designed to be as generic as possible so as to offer the same functionality for as many backends as possible.

However what you can do is kind of trick the system by using raw SQL.

Create a model that is as follows:

<?php

use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class MyUsers extends Phalcon\Mvc\Model
{
    public static function findFirst()
    {
        // The raw SQL statement
        $sql = "SELECT user_id, first_name, last_name, totalSalesFigure(user_id) as sales_figure FROM users";

        // Base model
        $user = new Users();

        // Execute the query
        return new Resultset(null, $user, $user->getReadConnection()->query($sql));
    }
}

The MyUsers object now will contain the sales_figure.

Thanks! the raw sql is exactly what I am looking for. As long as I can inject the value into the user model instead of just querying it back as an obj or array.