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.

Error mapping column to model after changing table column type

Hi,

I have a model named 'Users', and a corresponding database table named 'users'. I've built some basic creation, getting, and setting functionality that has worked well thusfar.

I've run into a problem after changing the data type of my 'id' column in my database. I want to use GUIDs instead of numbers for identifying my users, but after changing the column data type from int to varchar, I get the following error:

PHP Fatal error:  Uncaught exception 'Phalcon\\Mvc\\Model\\Exception' with message 'Column '1dffc8f8' doesn't belong to any of the selected models (1), when preparing: SELECT [Users].* FROM [Users] WHERE ([customerid] = :APR0:) AND id = 1dffc8f8-ec15-91c9-0d3e-9b1aafa1ffad' in /home/.../public_html/controllers/UsersController.php:255\nStack trace:\n#0 [internal function]: Phalcon\\Mvc\\Model\\Query->_getQualified(Array)\n#1 [internal function]: Phalcon\\Mvc\\Model\\Query->_getExpression(Array, true)\n#2 [internal function]: Phalcon\\Mvc\\Model\\Query->_getExpression(Array, true)\n#3 [internal function]: Phalcon\\Mvc\\Model\\Query->_getExpression(Array, true)\n#4 [internal function]:
Phalcon\\Mvc\\Model\\Query->_getExpression(Array, true)\n#5 [internal function]: Phalcon\\Mvc\\Model\\Query->_get
Expression(Array, true)\n#6 [internal function]: Phalcon\\Mvc\\Model\\Query->_getExpression(Array)\n#7 [internal function]: Phalcon\\Mvc\\Model\\Query->_prepareSelect()\n#8 [internal
function]: Phalcon\\Mvc\\Model\\Query->parse()\n#9 [internal function] in /home/.../public_html/controllers/UsersController.php on line 255

The line throwing the error is:

$user = $customer->getUsers("id = $userid")->getFirst();

But any other code that retreives users by id fails as well.

The part of the error where it says: "Column '1dffc8f8' doesn't belong to any of the selected models" Makes me think that Phalcon has somehow cached my table structure, and is now trying to find a column that no longer exists. However, I'm not aware of any caching that I've enabled, or where this might be getting cached. This error persists after a restart of Apache, and a restart of the MySQL server.

As soon as I change the data type back van varchar to int, the error disappears.

It would be great if anyone has any insight in what is going wrong, and how to fix this. Thanks very much in advance.

I'm running this on Phalcon 2.0.8 and MySQL.

Below is my model.

<?php
use Phalcon\Mvc\Model,
    Phalcon\Mvc\Model\Validator\InclusionIn,
    Phalcon\Mvc\Model\Validator\Uniqueness;

/**
 * @property string username
 * @property string email
 * @property string password
 * @property int status
 * @property string firstName
 * @property string lastName
 * @property int telephoneNumber
 * @property DateTime dateOfBirth
 * @property int gender
 * @property bool isAdmin
 */
class Users extends Model
{
    public $customerid;

    public function initialize()
    {
        $this->belongsTo("customerid", "Customers", "id");
        $this->hasMany("id", "Statistics", "userid");
        $this->hasOne("id", "Sessions", "userid");
        $this->hasMany("id", "Images", "userid");
    }

    public function validation()
    {

        //active must be: 0 or 1
        $this->validate(new InclusionIn(
            array(
                "field"  => "status",
                "domain" => array("0", "1")
            )
        ));

        //Email address must be unique
        $this->validate(new Uniqueness(
            array(
                "field"   => "email",
                "message" => "The user's email must be unique"
            )
        ));

        //Check if any messages have been produced
        if ($this->validationHasFailed() == true) {
            return false;
        }
    }
}

You sure you dont have maybe modelsMetadata cache enabled in di in APC or somewhere ?

edited Dec '15

Pretty sure. The application is pretty basic at this stage, and I haven't manually configured any caching functionality.

This is the entire setup portion for the database adapter:

function getDbAdapter(){

    $configFile = parse_ini_file("config.ini");

    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => $configFile["host"],
        "username" => $configFile["username"],
        "password" => $configFile["password"],
        "dbname" => $configFile["dbname"]
    ));
}
$di = new \Phalcon\DI\FactoryDefault();
$di->set('db', getDbAdapter());
$app = new Phalcon\Mvc\Micro();
$app->setDI($di);

This is the Phalcon information from phpinfo:

phalcon Web framework delivered as a C-extension for PHP

phalcon enabled
Author  Phalcon Team and contributors
Version     2.0.8
Build Date  Nov 6 2015 11:16:24
Powered by Zephir   Version 0.8.0a

Directive   Local Value Master Value
phalcon.db.escape_identifiers   On  On
phalcon.db.force_casting    Off Off
phalcon.orm.cast_on_hydrate Off Off
phalcon.orm.column_renaming On  On
phalcon.orm.enable_implicit_joins   On  On
phalcon.orm.enable_literals On  On
phalcon.orm.events  On  On
phalcon.orm.exception_on_failed_save    Off Off
phalcon.orm.ignore_unknown_columns  Off Off
phalcon.orm.late_state_binding  Off Off
phalcon.orm.not_null_validations    On  On
phalcon.orm.virtual_foreign_keys    On  On

Is there anything else I could check?

'Column '1dffc8f8' doesn't belong to any of the selected models (1), when preparing: SELECT [Users].* FROM [Users] WHERE ([customerid] = :APR0:) AND id = 1dffc8f8-ec15-91c9-0d3e-9b1aafa1ffad'

the generated statement seems to think 'id' is still an integer; the error refers to a non-existing column named '1dffc8f8', as this is the first part of the GUID. This is seen as a column identifier, because it isn't wrapped in single quotes in the generated statement.

Well its pretty possible. Use single quotes or backticks.

I would, but that statement isn't generated by me, but by Phalcon. If I do:

$user = $customer->getUsers("id = $userid")->getFirst();

or

$user = Users::findFirst($userid);

Then I get that error because the statement it prepares seems to be meant for an integer column.

I tried to manually tell Phalcon that the id property is a string by adding this to the model:

* @property string id

But that didn't do anything.

edited Dec '15

Try:

$user = $customer->getUsers("id = `$userid`")->getFirst(); // or
$user = $customer->getUsers("id = '$userid'")->getFirst();

Also im not sure is it safe, you should use binding.

These both seem to work:

$user = Users::find("id = '$userid'")->getFirst();
//and
$user = Users::find(array('id' => "'$userid'"))->getFirst();

Anyone have any idea why this is required? Is there anything that can be done in the model to make this work normally? Otherwise I'd have to re-do a lot of logic.



99.3k
Accepted
answer
edited Dec '15

Its not about model name or phalcon, its about builded query, string without any backticks/quotes is just a column name, try binding:

$user=Users::findFirst([
    'conditions'=>'id = :id:'
    'bind'=>array['id'=>$useid]
]);

Excellent. This works:

$user=Users::findFirst([
    'conditions'=>'id = :id:',
    'bind'=>array('id'=>$userid)
]);

Thanks very much for the help.

I liked the previty of $user = Users::findFirst($userid);. Do I understand correctly that I can't use this if my ID is not an integer?

edited Dec '15

Yes, exactly :P Well you can still use it like:

$user = Users::findFirst($userid);

You can just overload findFirst method yourself :). Well you can even create BaseModel/AbstractModel - well you extend it here. And then your models will use it, and you can use your super duper long id just by findFirst($userid) :).

You just have do something like this:

(abstract) class BaseModel/AbstractModel extends Model
{
    public function findFirst($parameters)
    {
        if(is_string($parameters)){// well in mostly times convention tell us to use int, but you want string
                return parent::findFirst(array(
                    'conditions'=>'id = :id:',
                    'bind'=>array('id'=>$parameters)
                ));
        }
        return parent::findFirst($parameters);
    }

    // not sure about find method, i dont think you need to overload it in your case
}

and then your users:

class Users extends AbstractModel
{
}

And you can go back to :

Users::findFirst($userid);

Also i dont understand your code in first place:

$user = $customer->getUsers("id = $userid")->getFirst();

Cant you just use

Users::findFirst($userId)

With code i provided above ? Also - can you check if for example if you put string without dashes is it still not working ? If not i will do PR with fix for that, cuz its seems like a bug.

Also you sure that

Users::findFirst($userId)

Wasnt working ? Cuz as i remember phalcon be default using bind when you provide only parameter as not array.

edited Dec '15

I can confirm that this:

$user = Users::findFirst($userId)

Doesn't work. It produces the error mentioned in the opening post. This also doesn't work:

$user = Users::find("id = $userid")->getFirst();

This produces the same error.

This seems like a bug to me. It's fine if the framework requires the ID to be an integer, but it should at least throw a better exception, instead of building an invalid query and then interpreting the ID as a column name.

As to your question about this code:

$user = $customer->getUsers("id = $userid")->getFirst();

At that stage in the logic I have a customer, and I want to make sure that the requested user belongs to that customer. Of course, that could also be achieved with an extra parameter for the customer when searching users.

Great idea about implementing my own findFirst method that handles varchar IDs. I'll give that a try!

edited Dec '15

For future reference, here is the implementation of the abstract BaseModel class with the override for findFirst, that supports non-integer IDs. I can confirm that this works.

<?php
use Phalcon\Mvc\Model;

abstract class BaseModel extends Model
{
    /**
     * @param array|null|string $parameters
     * @return Model
     */
    public static function findFirst($parameters = null)
    {
        if(is_string($parameters) && (strpos($parameters,'=') === false)){
            return parent::findFirst(array(
                'conditions'=>'id = :id:',
                'bind'=>array('id'=>$parameters)
            ));
        }
        return parent::findFirst($parameters);
    }
}

Edit: added the check if the parameter contains the character '='. Otherwise this usage breaks:

$user = Users::findFirst("username = '$username'");

I'm not entirely happy with this approach. Might reconsider in the future.

edited Dec '15

Well you should not use

$user = Users::findFirst("username = '$username'");

This, cuz i dont event now if this working, but this is not using binding. If you want to pass variable in the condition YOU HAVE TO use binding. Cuz not using it can cause SQL Injection

That usage does work by default. But that's a good point, I'll change it to use binding.

Any idea why binding isn't used by default for the 'find' and 'findFirst' methods?