[BUG] Phalcon\Mvc\Model::find() fails when using "columns" and "limit" params with Oracle

[rlaffers]

I am using Phalcon 1.2.1 (unable to upgrade at the moment but seeing the changelog up to 1.2.4, this issue has presumably not been addressed yet)
The issue confirmed in Phalcon 1.2.4 with Oracle 10g (release 10.2.0.3.0), PDO::ATTR_CASE is set to PDO::CASE_LOWER.
Related issue: #760

$robots = Models\Robots::find(array(
    'columns' => 'id' 
));

Fails with the following error:

Phalcon\Mvc\Model\Exception: Column 'id' doesn't belong to any of the selected models (1), when preparing: SELECT id FROM [MyApp\Models\Robots]

My db table is called "robots":

ID	NAME	TYPE	YEAR	CURRENT_STATUS
1	Robotina	mechanical	1972	on
2	Astro Boy	mechanical	1952	off
3	Terminator	cyborg	2029	on

My model:

namespace MyApp\Models;
class Robots extends \Phalcon\Mvc\Model {
    public function getSource() {
        return 'robots';
    }
}

According to the docs, this ought to work but does not.

A workaround is to specify $columnMap in the model (which is not optimal and should not be necessary):

class Robots extends \Phalcon\Mvc\Model {
    public $id;
    public $name;
    public $type;
    public $year;
    public $current_status;
    public $db_rownum;

    public function getSource() {
        return 'robots';
    }

    public function columnMap() {
        return array(
            'id' => 'id',
            'name' => 'name',
            'type' => 'type',
            'year' => 'year',
            'current_status' => 'current_status',
            'db_rownum' => 'db_rownum',
        );
    }
}

Now it works fine, however, it will fail again if I try:

$robots = Models\Robots::find(array(
    'columns' => 'id' 
    'limit' => 1 
));

Phalcon\Mvc\Model\Exception: Column "db_rownum" doesn't make part of the column map

Please note that I included db_rownum in the columnMap as suggested in #760 . It helps only when:

$robots = Models\Robots::find(array(
    'limit' => 1 
));

If both "limit" and "columns" are used the find() always fails with the above error.

I suspect Oracle is the culprit here, Phalcon does not play nice with it.
Phalcon\Mvc\Model::findFirst() is affected by the same issue.

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[marciopaiva]

@rlaffers try this model.

class Robots extends \Phalcon\Mvc\Model {
    public $id;
    public $name;
    public $type;
    public $year;
    public $current_status;

    public function getSource() {
        return 'robots';
    }

    public function columnMap() {
        return array(
            'id' => 'id',
            'name' => 'name',
            'type' => 'type',
            'year' => 'year',
            'current_status' => 'current_status',
            'DB_ROWNUM' => 'db_rownum',
        );
    }
}

[nokios]

This will not work (adding DB_ROWNUM) to the column map. Because then it will build a select clause with "[table name].DB_ROWNUM, which is not valid. The Dialect_Oracle needs to be fixed to select only the desired columns... not Z2.* as it does now... i'm trying to fix it locally and test. If I get it to work, I will post a pull request...

[rlaffers]

Well, have you tried? It works for me. The DB_ROWNUM is added to the columns in a subquery, so it is valid.
Actually, I have ended up with a slightly updated column map and model property names, so now it fits most use cases:

class Robots extends \Phalcon\Mvc\Model {
    public $ID;
    public $NAME;
    public $TYPE;
    public $YEAR;
    public $CURRENT_STATUS;

    public function getSource() {
        return 'robots';
    }

    public function columnMap() {
        return array(
            'ID' => 'ID',
            'NAME' => 'NAME',
            'TYPE' => 'TYPE',
            'YEAR' => 'YEAR',
            'CURRENT_STATUS' => 'CURRENT_STATUS',
            'DB_ROWNUM' => 'db_rownum',
        );
    }
}

Anyhow, I agree that the Oracle adapter needs some fixes. Model::count() still does not work.

[nokios]

I tried adding that and got a different error. It adds "DB_ROWNUM" to the inner select query.... here is a small sample:

SELECT Z2.* FROM (SELECT Z1.*, ROWNUM DB_ROWNUM FROM ( SELECT SLTV.DB_RONUM ... FROM SLTV ) Z1 ) Z2 WHERE Z2.DB_ROWNUM BETWEEN 1 AND 5;

The error I get is that SLTV.DB_ROWNUM is an invalid identifier. i'm honestly surprised any modelling with Oracle is working at all unless you've modified the source code. What version of Phalcon are you running?

This might work in 1.2.6 (but appears to be broken in 1.3.0 and beyond.)

[nokios]

I see that "cloneResultMap" is called once the data is fetched.. but my attempts to override that method are not working... it isn't behaving as expected.

[nokios]

Alright.. in playing with the source of 1.3.3, I was able to get Oracle to work ... I modified cloneResultMap to skip over the DB_ROWNUM column... however, I renamed it to ZDB_ROWNUMZ in the source to make it more unique (in case someone actually wants DB_ROWNUM).

this allows you to keep DB_ROWNUM out of your column map.

The next issue would be to fix how it performs getting a count but I think that's a lot more involved than I can get into at this point... looks like I am going to have to resort to a Model/PHQL: approach for now. you can see my changes here: https://github.com/nokios/cphalcon/tree/1.3.3-oracle-fix

it's far from perfect... I'm just playing around trying to get it started...

[rlaffers]

Thumbs up for digging into this.
I tried using the PHQL but ended up with the same problems (if memory serves). Please let us know if you fared better.

[nokios]

@rlaffers I started messing with other parts, but I don't really know C. The problem is the Model uses PHQL, and that's what I was working on to fix. (or rather, Dialect). I was still having trouble with joins/relationships, I believe.

If I have some time, I will play around some more... I know Phalcon is meant to be optimized, and I'm really only looking to fix it.... I actually started implementing some of the "PHQL" directly in my base model to support this, but I realized i'm overextending myself...I only need to query the database, not update it.

[andresgutierrez]

This is fixed in the 2.0.x branch (Phalcon 2.0.4), you have to change a global ORM setting to allow unexpected columns:

\Phalcon\Mvc\Model::setup(['ignoreUnknownColumns' => true]);