Auto join relations

Question

Auto join relations

ThinTroll Jul '13

Created Jul '13	Last Reply Aug '13	Replies 2	Views 2628	Votes 3

ThinTroll 2.5k

Jul '13

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();

3

Andrey · Answer 1 · 2013-07-28T11:41:29-07:00

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;

ThinTroll · Answer 2 · 2013-07-29T03:07:36-07:00

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.

incrize · Answer 3 · 2013-08-09T03:34:55-07:00

incrize
891

Aug '13

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