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

What's the best method of getting a single specific value from the database?

Hello,

I'm trying to create a very specific static function for my application. I have my Users model, and I have added the static method Users::getEmailById so that I don't have to create an object if I'm simply trying to obtain the email of a random user.

At the moment, I don't know how to simply fetch a database value using models, so I am using this code to accomplish that task:

    public static function getEmailById($user_id) {
        $user = Users::findFirstById($user_id);

        if(! empty($user)) {
            return $user->email;
        }

        return null;
    }

However, this is creating a Users object for basically no reason, because I won't be utilizing the object any further than grabbing the email value.

Is this some method of saying: Users model, give me the email of a user with an id value of $user_id?

I know the actual performance impact of my current method is irrelevant, but I would like to accomplish my goals in the most efficient way possible.

Thanks!

Edit: Would \Phalcon\Db be a better idea for this type of task?

edited Oct '14

Having a static method in your User model to obtain the User models email address itself doesn't really make sense for the MVC Design pattern

You could either specify 'columns' when finding a model record like so:

$user = Users::findFirst(['id = ?0', 'bind' => [$user_id], 'columns' => ['email']]);    //Please note this may affect model relationships by specifying columns
echo $user->email;

or literally obtain the email from the User model itself like so

$user = Users::findFirst(['id = ?0', 'bind' => [$user_id]]);
echo $user->email; //or $user->getEmail() if using getters/setters


6.6k

Why don't you use the PHQL or the QueryBuilder class of Phalcon? It's a nice tool to fetch single values or complex queries with e.g. joins from the database :-)

https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html



12.6k
edited Oct '14

Having a static method in your User model to obtain the User models email address itself doesn't really make sense for the MVC Design pattern

You could either specify 'columns' when finding a model record like so:

$user = Users::findFirst(['id = ?0', 'bind' => [$user_id], 'columns' => ['email']]);    //Please note this may affect model relationships by specifying columns
echo $user->email;

or literally obtain the email from the User model itself like so

$user = Users::findFirst(['id = ?0', 'bind' => [$user_id]]);
echo $user->email; //or $user->getEmail() if using getters/setters

Could you elaborate on why it does not make sense? In the situation that static method would be used, I would only need that single piece of information, as opposed to an entire object which I won't be using.

I am, however, converting an older project that utilized mainly functions to using Phalcon, so it's quite a different world... it just seems that fetching an entire object when you simply need the value of a single column in a row is overkill.

Why don't you use the PHQL or the QueryBuilder class of Phalcon? It's a nice tool to fetch single values or complex queries with e.g. joins from the database :-)

https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html

I tried it with with PHQL... pretty cool. This is what I used:

    public static function getEmailById($user_id) {

        $query = new \Phalcon\Mvc\Model\Query("SELECT email FROM Users WHERE id = :user_id: LIMIT 1", \Phalcon\DI::getDefault());

        $result = $query->execute([
            'user_id' => $user_id
        ]);

        return $result[0]->email;
    }

It seems to be somewhere around 30% faster than the method I posted in my original question surprisingly. Might be a nice solution. Thanks for the suggestion.

Could you elaborate on why it does not make sense? In the situation that static method would be used, I would only need that single piece of information, as opposed to an entire object which I won't be using.

I meant that in the sense that you're calling a static method that returns an instantiated instance of the the same class, rather than using an instantiated version of the class to find the result and return the same object.

Computing and memory wise it just makes more sense.



12.6k

Could you elaborate on why it does not make sense? In the situation that static method would be used, I would only need that single piece of information, as opposed to an entire object which I won't be using.

I meant that in the sense that you're calling a static method that returns an instantiated instance of the the same class, rather than using an instantiated version of the class to find the result and return the same object.

Computing and memory wise it just makes more sense.

Ah, I understand. My goal was simply to accomplish the same functionality that exists in my current pre-Phalcon code base that uses functions and MySQLi. It made sense to me to place it as a static method on the Users class since it's user-related.

This is (roughly) the function I am trying to replace:

function getEmailByUserId($user_id, mysqli $db) {
    $user_id = $db->real_escape_string($user_id);

    $query = $db->query("SELECT `email` FROM `users` WHERE `user_id` = {$user_id} LIMIT 1");

    $result = $query->fetch_assoc();

    return $result['email'];
}

So, hopefully you can see why I figured there was no need to instantiate any object at all to accomplish this - which is why I posted asking if there's a better way :)

edited Oct '14

So, hopefully you can see why I figured there was no need to instantiate any object at all to accomplish this - which is why I posted asking if there's a better way :)

Based on this sentence it sounds like you're trying to have a tiny memory footpoint, basically to avoid the Phalcon ORM?

Even when using PHQL it will return an instantiated class of the Model - if this is not okay with you, you might want to look into using the database service directly, without anything to do with the modelsManager.

(eg. for MySQL PDO Adapter - https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo.html )

otherwise I suggest reading into the documentation on PHQL and the models manager



12.6k
edited Oct '14

Exactly. I was trying to avoid the ORM because it seems like unneccesary resource usage in this scenario. However, I'm looking at how beautiful it is to use the ORM compared to ugly SQL and realizing I care less about that resource usage than I thought I did.

I appreciate the feedback guys! I'm reading up the PHQL and the other links and I'll be able to make a more informed decision.

Thanks again!

One other aspect that might be helpful is hydration modes. You've said it seems like a resource waste to create a full model object when all you want is a single value. Using the columns property as stated will help, but you can also set the hydration mode to be an array. Using this mode, Phalcon will just put the requested values in an array, rather than creating a full model object.

https://docs.phalcon.io/es/latest/reference/models.html#hydration-modes



32.2k
edited Oct '14

If you are concerned about performance why don't you simply stick to SQL? If you want a little bit more abstraction you could use PDO instead of mysqli. I agree that ORM maybe be less cumbersome to work with for simple queries but when you start having to create more complex ones with lots of joins it is usually much faster and performant to write the SQL yourself and get all the data in one shot.

Also you could simply do:

return $query->fetch_field();