We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

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

https://forum.phalcon.io/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


6.7k

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

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

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_test.sql



6.7k

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;
}


6.7k

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