[2.0.4] PHQL DISTINCT does not work for my case due to inappropriate implicit join

[dschissler]

Between bugs #10526 and #10507 I'm tempted to remove all of the assigned relationships to severals core models that make up all of the interesting parts of my user data and to only use the assigned relationships for the mundane admin related models.

There is no way to get the phql below to work with the joining as it is now. I need to be able to specify that Placement is joining against SuperRecord and not Record.

        $phql = <<<PHQL
SELECT DISTINCT Record.*
FROM $Record Record
LEFT JOIN $Type Type ON Type.recordsId = Record.id
LEFT JOIN $Attribute Attribute ON Attribute.recordsId = Record.id
... conditions
AND
EXISTS (
  SELECT SuperRecord.*
  FROM $Record SuperRecord
  LEFT JOIN $Placement Placement ON Placement.setId = SuperRecord.id
... conditions
)
PHQL;

In my case I actually have two assigned relationships between Placement and Record (so another edge case) and I get this error message: There is more than one relation between models 'Webird\Data\Record' and 'placement', the join must be done using an alias, when preparing: SELECT DISTINCT Record.*. Ok, but aliases are very broken when things start getting complicated (mostly I guess due to #10526) and the forced implicit joins.

I think a more comprehensive rework of the joining and aliases is necessary but for now I've created a more simple bug #10704 to let Phalcon get out of the way as people push the ORM into strange database specific use cases.

[andresgutierrez]

@dschissler Can you please close this issue?