Joining the same table twice

[mariancraciun1983]

I am having problems producing the suitable results when joining the same table twice.

The code is the following:

$query = \Zero\Context\Zero::instance()->modelsManager()->createBuilder()
      ->columns("[RecruitPeople].*,[Recruiter].*, [Converter].*  ,RecruitClass.*, 'Dummy' as Dummy")
      ->from(array('RecruitPeople'=>'\Zero\Models\RecruitPeople'))
      ->innerJoin('\Zero\Models\RecruitClass','RecruitPeople.class_id = RecruitClass.class_id','RecruitClass')
      ->innerJoin('\Zero\Models\User','RecruitPeople.recruiter_id = Recruiter.user_id','Recruiter')
      ->innerJoin('\Zero\Models\User','RecruitPeople.converter_id = Converter.user_id','Converter');

The generated Phql looks correct and is:

SELECT 
   RecruitPeople.*,Recruiter.*, Converter.*  ,RecruitClass.*, 'Dummy' as Dummy 
FROM 
   [\Zero\Models\RecruitPeople] AS [RecruitPeople] 
   INNER JOIN [\Zero\Models\RecruitClass] AS [RecruitClass] ON RecruitPeople.class_id = RecruitClass.class_id 
   INNER JOIN [\Zero\Models\User] AS [Recruiter] ON RecruitPeople.recruiter_id = Recruiter.user_id 
   INNER  JOIN [\Zero\Models\User] AS [Converter] ON RecruitPeople.converter_id = Converter.user_id

The results is always missing the first alias of the User table.

//This will result in missing "Recruiter"
->columns("RecruitPeople.*,Recruiter.*, Converter.*  ,RecruitClass.*, 'Dummy' as Dummy")

//This will result in missing "Converter"
->columns("RecruitPeople.*, Converter.* ,Recruiter.* ,RecruitClass.*, 'Dummy' as Dummy")

//THis will cause to have Converter overide the results from Recruiter. 
->innerJoin('\Zero\Models\User','RecruitPeople.converter_id = Converter.user_id','Converter')

The idea is that RecruitPeople can be converter or recruited by a User, so i need to join twice with User.

I know that in the docs, it's being said that there are some issues with the columns renaming,aliases, but not with tables.

I though about extending the model User to make this properly work, but it's not ideal.

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

[maxgalbu]

I'm getting this bug too. Joining the same table/model twice makes phalcon lose an object :((

Extending the model works, but this is definitely a bug

[mariancraciun1983]

The solution that works is by extending the User class (in my case) so that class names are unique.
The code from post 1 was changed into:

class Recruiter extends \Zero\Models\User {
}

class Converter extends \Zero\Models\User {
}

$query = \Zero\Context\Zero::instance()->modelsManager()->createBuilder()
      ->columns("RecruitPeople.*,Converter.*  ,Recruiter.*  ,RecruitClass.*, 'Dummy' as Dummy")
      ->from(array('RecruitPeople'=>'\Zero\Models\RecruitPeople'))
      ->innerJoin('\Zero\Models\RecruitClass','RecruitPeople.class_id = RecruitClass.class_id','RecruitClass')
      ->innerJoin('\Zero\Models\Converter','RecruitPeople.converter_id = Converter.user_id','Converter')
      ->innerJoin('\Zero\Models\Recruiter','RecruitPeople.recruiter_id = Recruiter.user_id','Recruiter');

So if you need to join the same table more than once, just extend it so that model passed to "join" is unique.

[mrjameshamilton]

We are having this issue on the 1.3.3 and the 1.3.2 branch but not on the master. Unit test "testIssue1803" fails.

[andresgutierrez]

Could you please try again using Phalcon 2?

[datacas]

I tried with Phalcon 2.0.0b and didn't work

phpInfo:
Web framework delivered as a C-extension for PHP
phalcon => enabled
Author => Phalcon Team and contributors
Version => 2.0.0
Powered by Zephir => Version 0.6.2a

I viewed the final sql, and the problem is that phalcon doesn't put de columns for this join. The Join statement appears, but not the columns.

[marianacapelo]

Using Phalcon 2.0.2 I get the same problem.
Joining Model1 to Model2 twice using aliases as j_m2_1 and j_m2_2, only the last joined model (with alias j_m2_2) shows in the select block, the first is completely lost.

[andresgutierrez]

@marianacapelo Could you provide a script that we can copy-paste in our machines allowing us to see the problem and reproduce it?

[marianacapelo]

@andresgutierrez Does this work?

class DbProfiler
{

    protected $_profiler;
    protected $_logger;

    /**
     * Creates the profiler and starts the logging
     */
    public function __construct()
    {
        $this->_profiler = new \Phalcon\Db\Profiler();
        $this->_logger   = new \Phalcon\Logger\Adapter\File("db.log");
    }

    /**
     * This is executed if the event triggered is 'beforeQuery'
     */
    public function beforeQuery($event, $connection)
    {
        $this->_profiler->startProfile($connection->getSQLStatement());
    }

    /**
     * This is executed if the event triggered is 'afterQuery'
     */
    public function afterQuery($event, $connection)
    {
        $this->_logger->log($connection->getSQLStatement(), \Phalcon\Logger::INFO);
        $this->_profiler->stopProfile();
    }
}


class Test1 extends \Phalcon\Mvc\Model
{

    public $id;
    public $fk_1;
    public $fk_2;
    public $name;

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


class Test2 extends \Phalcon\Mvc\Model
{

    public $id;
    public $name;

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

}



$di = new \Phalcon\DI();
$di->set('db', function ()  {

    $config = new Phalcon\Config\Adapter\Ini("config.ini");

    $connection = new Phalcon\Db\Adapter\Pdo\Mysql(
        array(
            "host" => $config->database->host,
            "username" => $config->database->username,
            "password" => $config->database->password,
            "dbname" => $config->database->dbname
        )
    );



    $eventsManager = new \Phalcon\Events\Manager();
    $db_profiler = new \DbProfiler();
    $eventsManager->attach('db',$db_profiler);
    $connection->setEventsManager($eventsManager);


    return $connection;
}, true);

$di->set(
    'modelsManager',
    function()
    {
        $modelsManager = new  \Phalcon\Mvc\Model\Manager();
        $eventsManager = new Phalcon\Events\Manager();

        $modelsManager->setEventsManager($eventsManager);

        return $modelsManager;
    }
);

$di->set(
    'modelsMetadata',
    function()
    {
        $metaData = new Phalcon\Mvc\Model\MetaData\Memory();
        return $metaData;
    }
);


/**
 * Query builder fetching the models
 */

$models_manager = $di->get('modelsManager');
$q = $models_manager->createBuilder()
    ->columns('t1.*, t2_1.*, t2_2.*')
    ->addFrom('Test1','t1')
    ->join(
        'Test2',
        't2_1.id = t1.fk_1',
        't2_1'
    )
    ->join(
        'Test2',
        't2_2.id = t1.fk_2',
        't2_2'
    )
    ->getQuery();

$row = $q->getSingleResult();

print_r($row->t2_1);

Database simple script

CREATE TABLE IF NOT EXISTS `test_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `test_2` (`name`) VALUES
  ('first id');

INSERT INTO `test_2` (`name`) VALUES
  ('second id');

CREATE TABLE IF NOT EXISTS `test_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fk_1` int(11) NOT NULL,
  `fk_2` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

INSERT INTO `test_1` (`fk_1`, `fk_2`, `name`) VALUES
  (1,2,'hi!');

[dreamsxin]

The parse has question, I will fix it.

[marianacapelo]

@dreamsxin great, thanks!
@andresgutierrez Can you tell when we can expect this to be fixed? (next release?)

[andresgutierrez]

Fixed in Phalcon 2.0.x