PHQL uses join ON 1 condition when joining directly against the hasMany linking table

[dschissler]

I have 3 table:

• table1: id
• link_table: id, table1Id, table2Id, type
• table2: id

The SQL reads INNER JOIN table1 ON 1 instead of inferring the relationship from the hasManyToMany relationship. I must include an additional hasMany relationship from table1 the linking table but this information is already available from the hasManyToMany relationship.

This stems from needing to include a flag field in the link table. Phalcon seems to assume that the link hasManyToMany link table will only have a primary id and two foreign keys and just falls apart and offers no facilities for dealing with it any other way.

If there are no edge cases that would become ambiguous then I propose that in effect that a hasManyToMany relationship will automatically add an extra hasMany relationship to the linking table or some other other equivalent method that allows the ON condition to be filled out.

I did enjoy the new $query->getSql() method though and it made it real easy to catch once I knew what I was looking for.

[andresgutierrez]

Are you compiling from ext/ in the 2.0.x branch?

[andresgutierrez]

I've made the following test, Robots joining directly to RobotsParts:

<?php

namespace Some;

use Phalcon\DI,
    Phalcon\Db\Column,
    Phalcon\Db\RawValue,
    Phalcon\Mvc\Model,
    Phalcon\Events\Manager as EventsManager,
    Phalcon\Db\Dialect\MySQL as SqlDialect,
    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 Model
{
    public function initialize()
    {
        $this->hasMany(
            "id",
            "Some\RobotsParts",
            "robots_id"
        );
    }
}

class RobotsParts extends Model
{

}

class Parts extends Model
{

}

$phql = "SELECT r.* FROM Some\\Robots r JOIN Some\\RobotsParts rp";

$robots = $modelsManager->executeQuery($phql);
echo(count($robots));

Outputs:

SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'robots' AND `TABLE_SCHEMA` = DATABASE()
DESCRIBE `robots`
SELECT `r`.`id`, `r`.`name`, `r`.`type`, `r`.`year`, `r`.`datetime`, `r`.`text` FROM `robots` AS `r`  INNER JOIN `robots_parts` AS `rp` ON `r`.`id` = `rp`.`robots_id`
3

The join condition is automatically added there

[andresgutierrez]

I'm checking sad1:

• There is no direct relation between Persons and PersonsGroups so that's why it joins the model but using a dummy condition 1. PersonsGroups is just an intermediate model to reach Groups. I think you can define this explicit relation and you will get the right condition there.

[ruudboon]

Any update's regarding this issue?

[ruudboon]

Thnx @dschissler I'l dive into it. All relation are there. Guess I must be missing something.

[ruudboon]

That's my case indeed. Table 1 -> (many to 1) Table 2 -> (many to 1) Table 3
And doing a where based on Table 1.

[stale]

Thank you for contributing to this issue. As it has been 90 days since the last activity, we are automatically closing the issue. This is often because the request was already solved in some way and it just wasn't updated or it's no longer applicable. If that's not the case, please feel free to either reopen this issue or open a new one. We will be more than happy to look at it again! You can read more here: https://blog.phalconphp.com/post/github-closing-old-issues