modelsManager's problem in 2.0

in 1.3.4, the code below can work

$this->modelsManager->executeQuery("SELECT p.*,w.phone FROM Models\Page AS p LEFT JOIN Models\Member AS w ON p.wid=w.id WHERE p.id=?0", [$id])

but in 2.0, it can't get any record. the dump result is

Phalcon\Mvc\Model\Resultset\Complex::__set_state(array( '_type' => 0, '_result' => NULL, '_cache' => NULL, '_isFresh' => true, '_pointer' => -1, '_count' => NULL, '_activeRow' => NULL, '_rows' => NULL, '_errorMessages' => NULL, '_hydrateMode' => 0, '_columnTypes' => NULL, ))

what's the problem?

Could you please post the models and table structure to reproduce the problem?

I had this problem too in Phalcon 2.0

http://forum.phalconphp.com/discussion/6342/namespace-in-query-builder-is-not-working-with-phalcon-20-but-it

I thought something wrong with the namespace inside the query. Should we create issue in cphalcon github?

I tried to reproduce the issue and it seems to work:

<?php

use Phalcon\DI,
    Phalcon\Db\Column,
    Phalcon\Events\Manager as EventsManager,
    Phalcon\Db\Adapter\Pdo\Mysql as Connection,
    Phalcon\Mvc\Model\Manager as ModelsManager,
    Phalcon\Mvc\Model\Metadata\Memory as ModelsMetaData;

$eventsManager = new EventsManager();

$di = new DI();

$connection = new Connection(array(
    "host"     => "localhost",
    "username" => "root",
    "password" => "",
    "dbname"   => "phalcon_test"
));

$connection->setEventsManager($eventsManager);

$eventsManager->attach('db',
    function ($event, $connection) {
        switch ($event->getType()) {
            case 'beforeQuery':
                echo $connection->getSqlStatement(), "\n";
                break;
        }
    }
);

$modelsManager = new ModelsManager();
$modelsManager->setDi($di);
$di['db'] = $connection;
$di['modelsManager'] = $modelsManager;
$di['modelsMetadata'] = new ModelsMetadata();

class Robots extends Phalcon\Mvc\Model
{

}

class RobotsParts extends Phalcon\Mvc\Model
{

}

$phql = 'SELECT p.*, r.name FROM RobotsParts AS p LEFT JOIN Robots AS r ON p.robots_id = r.id WHERE r.id = ?0';

$rows = $modelsManager->executeQuery($phql, [1]);
foreach ($rows as $row) {
    echo $row->name, PHP_EOL;
    echo $row->p->parts_id, PHP_EOL;
}
php c.php 
SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'robots_parts' AND `TABLE_SCHEMA` = DATABASE()
DESCRIBE `robots_parts`
SELECT `p`.`id` AS `_p_id`, `p`.`robots_id` AS `_p_robots_id`, `p`.`parts_id` AS `_p_parts_id`, `r`.`name` AS `name` FROM `robots_parts` AS `p` LEFT JOIN `robots` AS `r` ON `p`.`robots_id` = `r`.`id`  WHERE `r`.`id` = :0
Robotina
1
Robotina
2
Robotina
3


3.9k

try to use namespace in models, like

$phql = 'SELECT p.*, r.name FROM Models\RobotsParts AS p LEFT JOIN Models\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0';

>I tried to reproduce the issue and it seems to work: > >```php ><?php > >use Phalcon\DI, > Phalcon\Db\Column, > Phalcon\Events\Manager as EventsManager, > Phalcon\Db\Adapter\Pdo\Mysql as Connection, > Phalcon\Mvc\Model\Manager as ModelsManager, > Phalcon\Mvc\Model\Metadata\Memory as ModelsMetaData; > >$eventsManager = new EventsManager(); > >$di = new DI(); > >$connection = new Connection(array( > "host" => "localhost", > "username" => "root", > "password" => "", > "dbname" => "phalcontest" >)); > >$connection->setEventsManager($eventsManager); > >$eventsManager->attach('db', > function ($event, $connection) { > switch ($event->getType()) { > case 'beforeQuery': > echo $connection->getSqlStatement(), "\n"; > break; > } > } >); > >$modelsManager = new ModelsManager(); >$modelsManager->setDi($di); >$di['db'] = $connection; >$di['modelsManager'] = $modelsManager; >$di['modelsMetadata'] = new ModelsMetadata(); > >class Robots extends Phalcon\Mvc\Model >{ >
>} > >class RobotsParts extends Phalcon\Mvc\Model >{ >
>} > >$phql = 'SELECT p.*, r.name FROM RobotsParts AS p LEFT JOIN Robots AS r ON p.robots
id = r.id WHERE r.id = ?0'; > >$rows = $modelsManager->executeQuery($phql, [1]); >foreach ($rows as $row) { > echo $row->name, PHPEOL; > echo $row->p->partsid, PHP_EOL; >} > >``` > >```bash >php c.php >SELECT IF(COUNT(*)>0, 1 , 0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'robots%5Fparts' AND TABLE_SCHEMA = DATABASE() >DESCRIBE robots_parts >SELECT p.id AS _p_id, p.robots_id AS _p_robots_id, p.parts_id AS _p_parts_id, r.name AS name FROM robots_parts AS p LEFT JOIN robots AS r ON p.robots_id = r.id WHERE r.id = :0 >Robotina >1 >Robotina >2 >Robotina >3 >```

Same result:

<?php

namespace Some; 

use Phalcon\DI,
    Phalcon\Db\Column,
    Phalcon\Events\Manager as EventsManager,
    Phalcon\Db\Adapter\Pdo\Mysql as Connection,
    Phalcon\Mvc\Model\Manager as ModelsManager,
    Phalcon\Mvc\Model\Metadata\Memory as ModelsMetaData;

$eventsManager = new EventsManager();

$di = new DI();

$connection = new Connection(array(
    "host"     => "localhost",
    "username" => "root",
    "password" => "",
    "dbname"   => "phalcon_test"
));

$connection->setEventsManager($eventsManager);

$eventsManager->attach('db',
    function ($event, $connection) {
        switch ($event->getType()) {
            case 'beforeQuery':
                echo $connection->getSqlStatement(), "\n";
                break;
        }
    }
);

$modelsManager = new ModelsManager();
$modelsManager->setDi($di);
$di['db'] = $connection;
$di['modelsManager'] = $modelsManager;
$di['modelsMetadata'] = new ModelsMetadata();

class Robots extends Phalcon\Mvc\Model
{

}

class RobotsParts extends Phalcon\Mvc\Model
{

}

$phql = 'SELECT p.*, r.name FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0';

$rows = $modelsManager->executeQuery($phql, [1]);
foreach ($rows as $row) {
    echo $row->name, PHP_EOL;
    echo $row->p->parts_id, PHP_EOL;
}
$ php c.php 
SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'robots_parts' AND `TABLE_SCHEMA` = DATABASE()
DESCRIBE `robots_parts`
SELECT `p`.`id` AS `_p_id`, `p`.`robots_id` AS `_p_robots_id`, `p`.`parts_id` AS `_p_parts_id`, `r`.`name` AS `name` FROM `robots_parts` AS `p` LEFT JOIN `robots` AS `r` ON `p`.`robots_id` = `r`.`id`  WHERE `r`.`id` = :0
Robotina
1
Robotina
2
Robotina
3

This is the database if you want to run it: https://github.com/phalcon/cphalcon/blob/2.0.0/unit-tests/schemas/mysql/phalcon%5Ftest.sql



3.9k

And my database is postgresql 9.4, does it matter´╝č

It seems to be a postgresql only problem, I'm checking it

I've tested again and I had a wrong parameter so I can't find the problem, if you want to are more details to this issue to make it fail please don't hesitate:

<?php

namespace Some;

use Phalcon\DI,
    Phalcon\Db\Column,
    Phalcon\Mvc\Model,
    Phalcon\Events\Manager as EventsManager,
    Phalcon\Db\Adapter\Pdo\PostgreSQL as Connection,
    Phalcon\Mvc\Model\Manager as ModelsManager,
    Phalcon\Mvc\Model\Metadata\Memory as ModelsMetaData;

$eventsManager = new EventsManager();

$di = new DI();

$connection = new Connection(array(
    "host"     => "127.0.0.1",
    "username" => "postgres",
    "password" => "password",
    "dbname"   => "phalcon_test",
    "schema"   => "public"
));

$connection->setEventsManager($eventsManager);

$eventsManager->attach('db',
    function ($event, $connection) {
        switch ($event->getType()) {
            case 'beforeQuery':
                echo $connection->getSqlStatement(), "\n";
                break;
        }
    }
);

$modelsManager = new ModelsManager();
$modelsManager->setDi($di);
$di['db'] = $connection;
$di['modelsManager'] = $modelsManager;
$di['modelsMetadata'] = new ModelsMetadata();

class Robots extends Model
{

}

class RobotsParts extends Model
{

}

$phql = 'SELECT p.*, r.name FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0';

$rows = $modelsManager->executeQuery($phql, [1]);
foreach ($rows as $row) {
    echo $row->name, PHP_EOL;
    echo $row->p->parts_id, PHP_EOL;
}


3.9k

I found that when I change the select column, the result also change.

the code below can work

SELECT p.* FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0
SELECT r.* FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0
SELECT p.somefield,r.somefield FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0

the code below can't work

SELECT p.* ,r.* FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0
SELECT p.*,r.somefield FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0
SELECT p.somefield,r.* FROM Some\RobotsParts AS p LEFT JOIN Some\Robots AS r ON p.robots_id = r.id WHERE r.id = ?0