Oracle Model ::find() not working

[nokios]

I am using customer metaData since I am using views as my source, not tables. The SELECT query generated is incorrect. I am using 1.3.1 (haven't tested on 1.3.2 yet, or 1.2.6)

I also have a MySQL instance of this for testing, with the same views as tables, and this model works.

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

My class is below:

class SiteClli extends \Phalcon\Mvc\Model
    public function initialize()
    {
        $this->setSource(...HIDDEN...);
        $this->setConnectionService(...HIDDEN...);

        /*$this->belongsTo('clli', 'Connection', 'DEV1SITECLLI', array(
            'alias' => 'connection1'
        ));*/
    }

    public function metaData()
    {
        $columns = $this->columnMap();

        $fields = array_keys($columns);

        $types = array();

        foreach ($fields as $fieldName) {
            $types[$fieldName] = Column::TYPE_VARCHAR;
        }

        return array(

            //Every column in the mapped table
            MetaData::MODELS_ATTRIBUTES => $fields,

            //Every column part of the primary key
            MetaData::MODELS_PRIMARY_KEY => array(
                'LOCATIONID'
            ),

            //Every column that isn't part of the primary key
            MetaData::MODELS_NON_PRIMARY_KEY => $fields,

            //Every column that doesn't allows null values
            MetaData::MODELS_NOT_NULL => array(),

            //Every column and their data types
            MetaData::MODELS_DATA_TYPES => $types,

            //The identity column, use boolean false if the model doesn't have
            //an identity column
            MetaData::MODELS_IDENTITY_COLUMN => array('LOCATIONID'),

            //How every column must be bound/casted
            MetaData::MODELS_DATA_TYPES_BIND => array(),

            //Fields that must be ignored from INSERT SQL statements
            MetaData::MODELS_AUTOMATIC_DEFAULT_INSERT => array(),

            //Fields that must be ignored from UPDATE SQL statements
            MetaData::MODELS_AUTOMATIC_DEFAULT_UPDATE => array()

        );
    }

    public function columnMap()
    {
        $columns = array(
            'LOCATIONID' => 'locationId',
            'NAME' => 'name',
            'DESCRIPTION' => 'description',
            'ADDRESS' => 'address',
            'TOWNCITY' => 'city',
            'PROVINCE' => 'state',
            'ZIP' => 'zip',
            'NOTES' => 'notes',
            'ADDRESS1' => 'address1',
            'ADDRESS2' => 'address2',
            'CLLI' => 'clli'
        );

        return $columns;
    }
}

[nokios]

I dug into the code to see if I could identify the problem. It appears, in PHP_METHOD(Phalcon_Db_Dialect_Oracle, select) {} (line 43414 in phalcon.c), you are not concatenating the Column or from strings into SQL. Compare this with PHP_METHOD(Phalcon_Db_Dialect, select) {}, (line 38887 in phalcon.c), where you are doing the following (line 39052):

PHALCON_SCONCAT_VSV(sql, columns_sql, " FROM ", tables_sql);

The above line is completely missing from the Oracle equivalent.

[rlaffers]

@nokios Here's the patch with your proposed changes (for patching the latest stable version 1.3.2):

--- ext/db/dialect/oracle.c 2014-08-20 14:12:10.000000000 +0200
+++ fixed/oracle.c  2014-08-20 14:02:02.929968979 +0200
@@ -988,6 +988,8 @@
        ZVAL_STRING(sql, "SELECT ", 1);
    }

+   PHALCON_SCONCAT_VSV(sql, columns_sql, " FROM ", tables_sql);
+
    /**
     * Check for joins
     */
--- build/32bits/phalcon.c  2014-08-20 14:00:25.000000000 +0200
+++ fixed/phalcon_32bits.c  2014-08-20 14:16:55.202013417 +0200
@@ -43577,6 +43577,8 @@
        ZVAL_STRING(sql, "SELECT ", 1);
    }

+   PHALCON_SCONCAT_VSV(sql, columns_sql, " FROM ", tables_sql);
+
    if (phalcon_array_isset_quick_string(definition, SS("joins"), 120974824UL)) {

        PHALCON_OBS_VAR(joins);
--- build/64bits/phalcon.c  2014-08-20 14:00:25.000000000 +0200
+++ fixed/phalcon_64bits.c  2014-08-20 14:18:56.810019473 +0200
@@ -43577,6 +43577,8 @@
        ZVAL_STRING(sql, "SELECT ", 1);
    }

+   PHALCON_SCONCAT_VSV(sql, columns_sql, " FROM ", tables_sql);
+
    if (phalcon_array_isset_quick_string(definition, SS("joins"), 6953673027048UL)) {

        PHALCON_OBS_VAR(joins);
--- build/safe/phalcon.c    2014-08-20 14:00:25.000000000 +0200
+++ fixed/phalcon_safe.c    2014-08-20 14:19:27.414020997 +0200
@@ -43577,6 +43577,8 @@
        ZVAL_STRING(sql, "SELECT ", 1);
    }

+    PHALCON_SCONCAT_VSV(sql, columns_sql, " FROM ", tables_sql);
+
    if (phalcon_array_isset_string(definition, SS("joins"))) {

        PHALCON_OBS_VAR(joins);

That seems to have fixed the issue.

[nokios]

Cool! Thanks! I was doing something like this and testing locally. there are other things wrong with the oracle client (such as the count() method... but this is the big one).

[rlaffers]

Yep, there are other problems with the Oracle dialect in 1.3.x. I'm working on a fix to Model::findFirst-related SQL query. I expect to make pull request tomorrow.

[jeiel]

Thanks at this time I review