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

Doubt about result of query & proper way of doing it

I have table and have to pull out some stuff from Model "Alerts" . The problem is that I am seeingn whole lot of things in my resultset apart of query answer. Since I am new to phalcon or any framework, I suppose its better to get the pointer hence, asking here.


$alerts = Alerts::find(
                    [
                        'conditions' => 'completed = :completed: AND  time < :currentTime: ',
                        'bind' => 
                            [                              
                            'completed' => 0,
                            'currentTime' => $currentTime
                        ]
                    ]);

It takes out stuff as I need but I can't iterate on result object as I see in $alerts is whole PDO object or what should I call .. here it is.

  Phalcon\Mvc\Model\Resultset\Simple Object
(
    [_result:protected] => Phalcon\Db\Result\Pdo Object
        (
            [_connection:protected] => Phalcon\Db\Adapter\Pdo\Mysql Object
                (
                    [_eventsManager:protected] => 
                    [_descriptor:protected] => Array
                        (
                            [host] => 127.0.0.1
                            [username] => root
                            [password] => somepass
                            [dbname] => vdbuser
                        )

                    [_dialect:protected] => Phalcon\Db\Dialect\Mysql Object
                        (
                            [_customFunctions:protected] => 
                            [_escapeChar:protected] => `
                        )

                    [_connectionId:protected] => 0
                    [_sqlStatement:protected] => 
                    [_sqlVariables:protected] => 

There is also my result of interest somewhere :

[_cache:protected] => 
    [_isFresh:protected] => 1
    [_pointer:protected] => 0
    [_count:protected] => 8
    [_activeRow:protected] => 
 [_rows:protected] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [reminderName] => Ankit Chauhan
                    [type] => event
                    [time] => 
                    [mode] => email
                    [email] => [email protected]
                    [phone] => 9098098099
                    [userId] => 3
                    [frequency] => weekly
                    [lastRun] => 
                    [completed] => 0
                )

            [1] => Array
                (
                    [id] => 3
                    [reminderName] => Ankit Ravindra
                    [type] => goal
                    [time] => 
                    [mode] => email
                    [email] => [email protected]
                    [phone] => 9098098099
                    [userId] => 3
                    [frequency] => weekly
                    [lastRun] => 
                    [completed] => 0
                )

            [2] => Array ......

So how do I get only desired result in $alert ?

edited May '18

Thats the whole DI being printed. Dont worry, just iterate over $alerts.

foreach ($alerts as $item) {
    echo $item->reminderName; 
}

Also you can use ->toArray() method for debugging purpose, it will only print the results, without DI and stuff. I have a simple function that I use while developing:

// Debug function
function d($what)
{
    if (is_object($what) AND method_exists($what, 'toArray')) {
        $what = $what->toArray();
    }
    echo '<pre>';
    print_r($what);
    die('</pre>');
}

// Try it
d($alerts);


8.8k

Thanks for the pointer. So essentially, I need to just see what I need, ignoring rest. As its my first time with any framwork and OOP too, taking little time to wrap it around my head .

Here is another doubt I have, now I have records, I have done manipulation, now I need to update record. Since we are using ORM here, I see no definitive way to only update what I have changed in multiple records I fetched. ie. If I had feched one record uing find, could have just changed its value $alert->save() would work.

So, how do update multiple rows, say I feched 15 rows, and in them have to change lastRun for 10 . What I see that I have to again use model::find($id) for each record and then update single one each time ?

Is that only possible best way to update multiple rows ?

edited May '18

Updating one record:

$item = YourModel::findFirst(54); // <- Primary key (record with "id" of 54)
$item->someProperty = 'new value';
$item->save();

Updating multiple records at once:

$items = YourModel::find();
foreach ($items as $item) {
    $item->someProperty = 'new value';
    $item->save();
}


8.8k
edited May '18

Thanks, that was precise.

It means for multiple records, via Model, the data is being updated / saved on each iteration rather than all once in eand as we do in raw SQL where we build statement and then send it to DB at once.

And here another general question related to MODEL usage, do you have any recomemndation where to USE MODEL for DB manipulation and where not and instead use PHQL or raw sql ?

edited May '18

Personally I always use the QueryBuilder for selecting and displaying data. ALso AVOIDING the use of SELECT *, only seleecting what is needed. Takes some more time, but its worth it.

For updating or simply manipulating data I use models, since I dont really care about performance in the ADmin panel or some cli script.

Here is a simple QueryBuilder query with a join:

public function getAll()
{
    $lang = $this->getDI()->getSession()->language ?? $this->getDI()->getConfig()->site->defaultLanguage;
    $cacheFile = 'products-all-'. $lang;
    $builder = $this->modelsManager->createBuilder();
    $builder->columns([
        'main.id', 

        'main18.title',
        'main18.content',
        'main18.volume',
    ]);
    $builder->from(['main' => 'Models\Products']);
    $builder->leftJoin('Models\ProductsI18n', 'main18.foreign_key = main.id', 'main18');
    $builder->where('main.is_active = 1');
    $builder->andWhere('main18.lang = :lang:');
    return $builder->getQuery()->cache(['key' => $cacheFile])->execute([
        'lang' => $lang,
    ]);
}

And lastly "raw sql" - using it when i need to run some very custom mysql queries, that use functions not supported by phql, here is a fresh exmaple: https://forum.phalcon.io/discussion/18157/how-can-i-run-this-query-in-phalcon Other situation where i would use raw sql is when I want to do some quick updates:

$this->db->execute('
    UPDATE uploads
    SET is_default = 0
    WHERE  
        is_default = 1 AND
        is_active = 1
');

It's true that there is no mass or batch update. Generally though, the performance loss from doing multiple queries is not that big. It's a trade-off for the convenience of an ORM.

I try to use the model to do database updates as much as I can - simply because that will generally reduce the number of coding errors. When I need to do a query, I rarely use the QueryBuilder over PHQL. In my opinion, it just adds overhead with no benefit.

@Dylan, would be nice if you add a PHQL query example so @iRickyJ can have all possible options in his question :)

edited May '18

PHQL is pretty much just like regular SQL, but with model names instead of table names. It would simply look like:

$phql = <<<PHQL
SELECT
    main.id, 
    main18.title,
    main18.content,
    main18.volume,
FROM
    Models\Products AS main,
    Models\ProductsI18n AS main18
WHERE
    main18.foreign_key = main.id AND
    main.is_active = 1 AND
    main18.lang = :lang:
PHQL;

// How "modelsManager" is retrieved would depend on the context in which this code is being run
$result = $DI['modelsManager']->executeQuery($phql,['lang'=>$lang]);


8.8k

Thanks that was informative. Also, I have been following docs closesly however, sometimes you need precise pointers and hence questions here. Still in my initial learning phase to learn my way around phalcon. I will definitely ask for help here as I need :)