We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Auto join relations

Hi. Is there any way to use relations in query condition, like in yii, without manualy join them using querybuilder?

I want something like this to find all robots with arms:

Robot::find( array( "RobotPart.name = :part:", 'bind' => array( 'part' => 'arm' ) ) );

instead of this monstrous construction:

$this->modelsManager->createBuilder()
    ->from('Robots')
    ->join('RobotsParts')
    ->where('RobotPart.name = :part:', array( 'part' => 'arm' ))
    ->getQuery()
    ->execute();


404

You need to set relations between models first. https://docs.phalcon.io/en/latest/reference/models.html#relationships-between-models And then you can get all robots with arms like this:

$robots = RobotsParts::findFirst(
    ["part = :part:"],
    "bind" => ["part" => "arm"]
)->Robots;


2.5k

Your solution wouldn't work in more complex situation like this:

Robot::find( array( "RobotPart.name = :part: AND RobotMotherboard.name = :motherboard:", 'bind' => array( 'part' => 'arm', 'motherboard' => 'intel' ) ) );

Also, I've already made dirty hack:

abstract class Model extends \Phalcon\Mvc\Model {

    static protected $modelsManager;
    static public function setModelsManager( $modelsManager ) {
        self::$modelsManager = $modelsManager;
    }

    static protected $relationsMetadata = array();
    static protected function getRelations( $modelName ) {
        if( ! isset( self::$relationsMetadata[$modelName] ) ) {
            $mm = self::$modelsManager;

            // initialize model relations metadata
            if( ! $mm->isInitialized( $modelName ) ) {
                $mm->load( $modelName );
            }

            // cache relations
            self::$relationsMetadata[$modelName] = array();
            foreach( $mm->getRelations( $modelName ) as $relation ) {
                $options = $relation->getOptions();
                $alias = isset( $options['alias'] ) ? $options['alias'] : $relation->getReferencedModel();

                if( ! $mm->isInitialized( $relation->getReferencedModel() ) ) {
                    $mm->load( $relation->getReferencedModel() );
                }

                self::$relationsMetadata[$modelName][$alias] = $relation;
            }
        }

        return self::$relationsMetadata[$modelName];
    }

    static protected function replaceRelations( &$query, &$joins, $modelName, $condition, $parentAlias = '' )
    {
        $nextLevel = array();

        if( $relations = self::getRelations( $modelName ) ) {
            // find child calls
            preg_match_all("/".( $parentAlias ? $parentAlias.'(.|<-|->)' : '' )."(".implode('|', array_keys($relations)).")[^\w:%\'\"]/i", $condition, $matches, PREG_OFFSET_CAPTURE);

            // replace parent by joined table alias
            foreach( array_reverse($matches[$parentAlias ? 2 : 1]) as $i => $match ) {
                if( ! isset( $joins[$match[0]] ) ) {
                    $alias = 't'.count($joins);
                    $joins[$match[0]] = array(
                         'alias' => $alias,
                         'model' => $relations[$match[0]]->getReferencedModel(),
                         'cond' => $alias . '.' . $relations[$match[0]]->getReferencedFields() . '=' . ($parentAlias ? $parentAlias : $modelName) . '.' . $relations[$match[0]]->getFields(),
                         'type' => ( $matches[1][$i][0] == '<-' ? 'LEFT' : ( $matches[1][$i][0] == '->' ? 'RIGHT' : 'INNER' ) )
                    );
                    $nextLevel[$alias] = $relations[$match[0]]->getReferencedModel();
                }

                $pal = $parentAlias ? strlen($parentAlias) + strlen($matches[1][$i][0]) : 0;
                $condition = substr_replace($condition, $joins[$match[0]]['alias'], $match[1] - $pal, strlen($match[0]) + $pal );                   
            }
        }

        foreach( $nextLevel as $alias => $nextModelName ) {
            $condition = self::replaceRelations( $query, $joins, $nextModelName, $condition, $alias );
        }

        return $condition;
    }

    static protected function buildCriteria( $condition, $params ) {
        $info = self::query();

        $query = self::$modelsManager->createBuilder()->from( $info->getModelName() );

        if( $condition ) {
            $joins = array();
            $condition = self::replaceRelations($query, $joins, $info->getModelName(), $condition);

            foreach( $joins as $join ) {
                $query->join( $join['model'], $join['cond'], $join['alias'], $join['type'] );
            }

            $query->where( $condition, $params );
        }

        return $query;
    }

    static public function get( $condition = '', $params = array() ) {
        return self::buildCriteria( $condition, $params )->getQuery()->execute();
    }

    static public function getFirst( $condition = '', $params = array() ) {
        return self::buildCriteria( $condition, $params )->limit(1)->getQuery()->execute()->getFirst();
    }

}

And now I can write queries like this:

$robots = Robot::get( "RobotParts.Vendor.name = :vendor: AND RobotMotherboard.Memory.type = :memory:", array( 'vendor' => 'Umbrella Inc.', 'memory' => 'DDR2' ) );

but I still wanna find a better solution.



891

Hi. I have a similar problem. Is there a solution?