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.

cast_on_hydrate ignored when columns are specified in Query Builder

Hello everyone, I've run into something quite specific and wondered if someone could take a look and advise if there is a fix?

In my bootstrap I have used ini_set() to set the value of cast_on_hydrate ini_set('phalcon.orm.cast_on_hydrate', 'on'); and I'm using Phalcon 3.4.0 on PHP 7.2.11.

If I use Model::find() style methods, cast_on_hydrate seems to be working fine. This changes when I use Query Builder though. Note that I'm using a multi-module style app with Model Namespacing enabled.

If I use Phalcon's Query Builder to create a simply query:

$di = \Phalcon\Di::getDefault();
$modelsManager = $di->getModelsManager();
$builder = $modelsManager
     ->createBuilder()
     ->from(['Jobs' => 'Jobs:Jobs']);
$results = $builder->getQuery()->execute();
var_dump($results[0]->id);

var_dump displays the result, correctly casted as an integer: int(87)

If I then add some columns into the mix:

// using same getModelsManager() as above
$builder = $modelsManager
     ->createBuilder()
     ->columns(['Jobs.id', 'Jobs.user_id'])
     ->from(['Jobs' => 'Jobs:Jobs']);
// getting results from $builder is same as above example

The output from var_dump is now string(2) "87"

My questions about this are:

  • Is this the expected behaviour, or is this a bug that I should report?
  • Are there any workarounds? e.g. I wondered if I used Phalcon\Mvc\Model\MetaData\Strategy\Annotations whether that would make any difference
  • Can I pass the details of the selected columns to query builder somehow and force it to cast the results of the query in a certain way? I've seen Phalcon\Mvc\Model\Query::setBindTypes() but this did not seem to work (see below)

For the final point here are some examples of what I've tried, which does not seem to work:

// using same $builder as above
$query = $builder->getQuery();
$query->setBindTypes([
     'Jobs.id' => \Phalcon\Db\Column::BIND_PARAM_INT,
     'Jobs:Jobs.id' => \Phalcon\Db\Column::BIND_PARAM_INT,
     'id' => \Phalcon\Db\Column::BIND_PARAM_INT,
]);
$results = $query->execute();
var_dump($results[0]->id); // string(2) "87"

I would really appreciate any suggestions or advice regarding the above. Thanks for your help in advance!

Hi @rich setBindTypes() is to set the type of parameters not "columns types" in results. however, if the result is a \Phalcon\Mvc\Model\Resultset\Simple, the columns should be classified, it is probably a small bug.

Check that or create a new issue on github

Good luck

edited 15d ago

thanks @emiliodeg, I checked and the returned class type is indeed \Phalcon\Mvc\Model\Resultset\Simple.

I'll go ahead and create a new issue on GitHub.

Update: issue raised



227
Accepted
answer
edited 15d ago

Fixed: seems like the db Di Service needed some additional PDO options:

$this->diContainer->setShared('db', new MySQLAdapter(
            [
                'host' => $this->config->database->host,
                'username' => $this->config->database->username,
                'password' => $this->config->database->password,
                'dbname' => $this->config->database->name,
                'options' => [
                    \PDO::ATTR_EMULATE_PREPARES => false,
                    \PDO::ATTR_STRINGIFY_FETCHES => false,
                ],
            ]
        ));

In my case adding just \PDO::ATTR_EMULATE_PREPARES => false did the job.

References: here and my issue on GitHub

Thanks for all the help both on GitHub and in the forum here.



60.0k

I'm really hoping that Phalcon 4.0 or 5.0 can revise the PDO options so that sane defaults are chosen to take advantage of easy features. So then assuming that only the "improved" mysql functions or pdo stuff is used on modern mysql above some version that ignores whatever older funky mysql setups. Fuck old myql, it was what people used but its a piece of shit just how old pre-5.3 PHP is considered today. Old mysql sucks really really bad and nothing about the setup has been revised since the old days. That means posts like this repeated forever as literally everyone has to reinvent the wheel for every fucking project. Copious uses the F-word is not out of line here at all as far as I'm concerned. I would just love to shift delete about 20% of the content in my sections of Phalcon Cookbook that I didn't enjoy writing and that people shouldn't need to read.

So as I've written in about three places now, what is needed is a single person or about two people whom together know pretty much all of the weird niggles and options of MySQL, PostgresSQL, SQLite and I guess that Oracle POS. I only know MySQL and SQLite and I've navigated the options and bugs of the PHP adapters to get stuff working but I'm far from being able to say that I know the complete ramifications of every option or that a new default option wouldn't impact another DB adapter. Its difficult to coordinate that much domain knowledge is a single push but that is what is needed.