Help with AR relationships

Hi, I’m new to Yii and active record and was wondering if its possible to have multiple joins when defining a relationship.

Example:

User - id(pk), name, email

Article - id(pk), title, userId(fk)

Paragraph - id(pk), text, articleId(fk)

ChangeRequest - id(pk), status, text, paragraphId(fk)

I can define the following:

User.articles - HAS_MANY, Article, userID

Article.paragraphs - HAS_MANAY, Paragraph, articleId

Article.user - BELONGS_TO, User, userId

Paragraph.changeRequets - HAS_MANY, ChangeRequest, paragraphId

Paragraph.article - BELONGS_TO, Article, articleId

I would also like to define:

Article.changeRequests - HAS_MANY, Paragraph, articleId (JOIN) HAS_MANY, ChangeRequest, paragraphId

Is this possible?

You may want to use something similar to this.




$model = Article::model()->with(paragraph, paragraph.changerequests)->findAll();



More details here: Relational AR

/Tommy

Doesn’t this return all Paragraphs with their related ChangeRequests? I want just the ChangeRequests that are related to an Article (via Paragraphs).

I haven’t tried exactly this, but you should be able to declare a condition like this




... ->with(some-relationship=>array('condition'=>'some-condition'), ...)-> ...



BTW this is eager loading. Alternatively, for lazy loading you should be able to specify a condition and ‘with’ in a relationship declaration.

Edit:

See also with() in class reference.

/Tommy

I want to something more like this but with active record:

select * from ChangeRequest where paragraphId in

(select id from Paragraph where articeId = <some-article-id>)

I just tried something similar to this




$criteria = new CDbCriteria;

$criteria->condition = 'paragraph.articleId="' . $articleId . '"';

ChangeRequest::model()-with('paragraph'=>array('select'=>'id'))->findall($criteria);



It will bring back all columns from “paragraph”, though. The select didn’t work as I expected. Without doubt somebody else in this forum can give a better answer.

For sure you have to define an alias in the model ChangeRequest




public function relations()

{

  return array(

   'paragraph'=>array(self::BELONGS_TO, 'Paragraph', 'paragraphId', 'alias'=>'paragraph'),

  }

}



/Tommy

Thanks, I don’t think this is possible. Sigh… and I was really enjoying Yii.

On to the next framework!

Don’t give up yet. I still think it’s possible, one way or another. For now, I have to continue with something else, but you may want to search for some older posts on relational AR or wait for a better answer from somebody else.

/Tommy

Your right, I’m just getting a bit pissed off with all ORM’s making this kind of thing hard.

It would be good if Yii could define a new relationship type:

array(‘changeRequests’, ‘self::HAS_INNER_MANY’, ‘ChangeRequest’, ‘alias’=>array(‘ChangeRequest’=>‘c’, ‘Paragraph’=>‘p’, ‘Article’=>‘a’), ‘inner’=>array(‘Paragraph’=>‘c.paragraphId = p.id’, ‘Article’=>‘p.articleId = a.id’)

Would produce:

SELECT c.id, c.status, c.text, c.paragraphId

FROM ChangeRequest c

INNER JOIN Paragraph p ON c.paragraphId = p.id

INNER JOIN Article a ON p.articleId = a.id

WHERE a.id = <some-article-id>

No eager loading supported, but still be able to supply CDbCriterea etc.

I had another look on this query

In my test I used the wrong relationship when testing for disappearing attributes.

That is, the select does work.

These (on-the fly) conditions will take precedence over conditions declared in relations()

Controller




$criteria = new CDbCriteria('condition' => 'paragraph.articleId="' . $articleId . '"');

ChangeRequest::model()-with('paragraph'=>array('select'=>'id'))->findall($criteria);



Model (ChangeRequest)




public function relations() {

  return array(

   'paragraph'=>array(self::BELONGS_TO, 'Paragraph', 'paragraphId', 'alias'=>'paragraph', 'joinType'=>'INNER JOIN'),

  }

}



I may have misinterpreted what you exactly want to do, might take another look tomorrow.

/Tommy

Just on a quick note. I was browsing through this thread and noticed that every one of Tri’s posts that did not solve the problem had been given a negative rating.

I just wanted to point out that these +/- buttons on the posts are to rate the usefulness of a response in regards to your topic but are used to record the reputation of the poster. By giving a negative mark, it decreases the posters reputation (thus making them look less knowledgeable and helpful to others).

As for my thoughts on this topic, have you considered using straight SQL and just passing it the parameters your want? Sometimes (most times) I find that with relational queries it can just be easier to write the SQL by hand and just execute it. This removes the extra overhead of what the framework has to do to generate the query and it guarantees you have the query you want.

Just a thought…

A quick addition to Tri’s last piece of Controller code, I suggest using addColumnCondition to preserve parameter binding, like so:

Controller




$criteria = new CDbCriteria;

$criteria->addColumnCondition(array('paragraph.articleId'=>$articleId));

ChangeRequest::model()->with('paragraph'=>array('select'=>'id'))->findAll($criteria);



Have done some more think on this…

I have the methods below in my base ActiveRecord which my User model extends, and can access all Paragraph and ChangeRequest instances that indirectly point to a User.

Define indirect relations in User model:




    public function getIndirectRelations()

    {

        return array(

            'paragraphs' => array(

                array('Article', 'authorId', '_Article'),

                array('Paragraph', 'articleId', 'Paragraph')

            ),

            'changeRequests' => array(

                array('Article', 'authorId', '_Article'),

                array('Paragraph', 'articleId', '_Paragraph'),

                array('ChangeRequest', 'paragraphId', 'ChangeRequest')

            )

        );

    }



Access an indirect relationship:

(Cant eager load, but can eager load normal relations)




    public function indirectRelation($ralationName, $args = array())

    {

        $config = $this->_indirectRelations;

        if(!isset($config[$ralationName])) throw new CException();


        if(isset($args['condition']))

        {

            $criteria = $args['condition'];

            if(!$criteria instanceof string)

            {

                $save = $criteria;

                $criteria = new CDbCriteria();

                $criteria->condition = $save;

                if(isset($args['params']))

                {

                    $criteria->params = $args['params'];

                }

            }

        }

        else $criteria = null;


        $with = isset($args['with']) ? $args['with'] : null;


        $outerCriteria = new CDbCriteria();


        if($with == null) $outerCriteria->select = $config[$ralationName]['select'];

        $outerCriteria->join = $config[$ralationName]['join'];

        $outerCriteria->condition = $config[$ralationName]['condition'];

        $outerCriteria->params = $config[$ralationName]['params'];


        if($criteria != null) $outerCriteria->mergeWith($criteria);


        if($with == null) return $config[$ralationName]['model']->findAll($outerCriteria);

        else return $config[$ralationName]['model']->with($with)->findAll($outerCriteria);

    }



Parse the config array:




    public function configureIndirectRelations()

    {

        $config = array();

        $relations = $this->getIndirectRelations();

        foreach($relations as $relationName=>$relation)

        {

            $join = '';

            $condition = '';

            $select = '';


            $count = count($relation);

            $prevConfig = $relation[$count - 1];

            for($i = $count - 2; $i >= 0; $i--)

            {                

                $modelClass = $relation[$i][0];

                $modelAlias = $relation[$i][2];


                $prevAlias  = $prevConfig[2];

                $prevFk     = $prevConfig[1];


                $model = new $modelClass();


                $modelTable = $model->tableName();

                $modelPk    = $model->getMetaData()->tableSchema->primaryKey;


                $join .= strtr("INNER JOIN `modelTable` `modelAlias` ON (`modelAlias`.`modelPk` = `prevAlias`.`prevFk`) ",

                                   array(

                                       'modelTable' => $modelTable,

                                       'modelAlias' => $modelAlias,

                                       'modelPk'    => $modelPk,

                                       'prevAlias'  => $prevAlias,

                                       'prevFk'     => $prevFk

                                   ));

                $prevConfig = $relation[$i];

            }


            $firstFk    = $relation[0][1];

            $firstAlias = $relation[0][2];

            $selfPk     = $this->getMetaData()->tableSchema->primaryKey;


            $condition  = strtr("`firstAlias`.`firstFk` = :selfPk",

                                    array(

                                        'firstAlias' => $firstAlias,

                                        'firstFk'    => $firstFk

                                    ));


            $lastClass = $relation[$count - 1][0];

            $lastAlias = $relation[$count - 1][2];

            $lastModel = new $lastClass();

            

            $lastColumns = $lastModel->getMetaData()->tableSchema->columns;

            $count = count($lastColumns);

            foreach($lastColumns as $colName=>$column)

            {

                $comma = $count == 1 ? '' : ', ';

                $select  .= strtr("`lastAlias`.`colName`, ",

                                    array(

                                        'lastAlias' => $lastAlias,

                                        'colName'   => $colName,

                                        ', '        => $comma

                                    ));

                $count--;


            }


            $config[$relationName] = array(

                                        'select'    => $select,

                                        'join'      => $join,

                                        'condition' => $condition,

                                        'model'     => $lastModel,

                                        'params'    => array(':selfPk' => $this->$selfPk)

                                    );

        } 

        return $config;

    }



Check the attachment…

declare behavior in CActiveRecord:




public function behaviors()

{

    return array(

        'IndirectRelationBehaviour'=>'application.extensions.IndirectRelationBehaviour'

    );

}



declare indirect relations in CActiveRecord:




public function indirectRelations()

{

    return array(

        array('realtionName', 'type: count / join', array(

                    array('tableClass', 'foreignKey', 'alias'),

                    array('tableClass', 'foreignKey')

            )

        )

    );

}



The first ‘tableClass’ declared must have a ‘foreignKey’ that relates to the current model.

The last ‘tableClass’ declared is the model we want a result from.

The in-between ‘tableClass(s)’ declare how to ‘INNER JOIN’ the indirect relation.

We can use the behavior as follows:




$user = User::model()->findByPk('some-id');

$paragraphs = $user->indirect->paragraphs;



or




$user = User::model()->findByPk('some-id');

$paragraphs = $user->indirect()->paragraphs;



or




$user = User::model()->findByPk('some-id');

$paragraphs = $user->indirect->paragraphs('published=:published', array(':published'=>'$published'));



or




$paragraphs = $user->indirect('user')->paragraphs



etc…

gazbond, that’s awesome! If this works like you say it does, I’ll develop an immediate man-crush on you ;) I’m going to check this out soon! (Although I’ll probably get to it next week at the earliest, unfortunately)

One thing: A first look through the source makes me think this is a MySQL-only implementation (I see lots of backticks being used). Is this true? And would it somehow be possible to make this DB-independent (I use a lot of SQLite and even MSSQL and would love to use this in my projects!)

Anyway, good work!

Thanks. Yes its MySQL only - as far as I know, I have limited experience with other db’s.

I looked into adding another CActiveRelation type but that proved too complex.

Do you think this kind of operation is very common and should be part of Yii?

i.e. on Article model:




    public function relations()

    {

        return array(

            'author' => array(self::BELONGS_TO, 'User', 'userId', 'alias'=>'User'),

            'paragraphs' => array(self::HAS_MANY, 'Paragraph', 'articleId', 'alias'=>'Paragraph'),

            'changeRequests' => array(self::HAS_MANY, 'ChangeRequest', array(

                array('Paragraph', 'articleId', 'alias'=>'Paragraph'),

                array('ChangeRequest', 'paragraphId', 'alias'=>'ChangeRequest')

                )

            )

        );

    }



and Paragraph model:




    public function relations()

    {

        return array(

            'article' => array(self::BELONGS_TO, 'Article', 'articleId', 'alias'=>'Article'),

            'changeRequests' => array(self::HAS_MANY, 'ChangeRequest', 'paragraphId', 'alias'=>'ChangeRequest')

        );

    }



and if you did this:




$articleModel = Article::model()->with('paragraphs.changeRequests')->findAll();



You would have exactly the same SQL query generated as you do now but all the ChangeRequest instances would be referenced from:




$articleModel->changeRequests;



As well as:




foreach($articleModel->paragraphs as $paragraph) 

{

     $paragraph->changeRequests

}



Do you think this would be useful for MANY_MANY indirect relations as well?

Article model:




    public function relations()

    {

        return array(

            'author' => array(self::BELONGS_TO, 'User', 'userId', 'alias'=>'User'),

            'paragraphs' => array(self::HAS_MANY, 'Paragraph', 'articleId', 'alias'=>'Paragraph'),

            'images' => array(self::MANY_MANY, 'Image', array(

                array('Paragraph', 'articleId', 'alias'=>'Paragraph'),

                array('Image', 'ParagraphImage(paragraphId, imageId)', 'alias'=>'Image')

                )

            )

        );

    }



and Paragraph model:




    public function relations()

    {

        return array(

            'article' => array(self::BELONGS_TO, 'Article', 'articleId', 'alias'=>'Article'),

            'images' => array(self::MANY_MANY, 'Image', 'ParagraphImage(paragraphId, imageId)', 'alias'=>'Image')

        );

    }



Removed for being silly, silly me!

Hello again, sorry for the delay (I didn’t promise to have another look the next day).

I read the thread more thoroughly from the top and I think I understand the problem domain. Your relationship chain is primary->has_many->has_many->has_many. I also think I understand your need for an indirect relationship to accomplish the subquery (not tested).

Like I suggested in a previous post, you could start from ChangeRequest instead (my doubt was about which attributes was returned, not records). The relationship chain will be primary->belongs_to->belongs_to (with ChangeRequest as primary and not including the user table). This should work, at least it does in my configuration.

(Neither am I a DB specialist, so won’t comment on your latest post.)

/Tommy

Fantastic! This is what I really needed to do:




$criteria = new CDbCriteria();

$criteria->condition = 'Paragraph.articleId = 1';

$criteria->select = 'ChangeRequest.id, ChangeRequest.status, ChangeRequest.paragraphId';

$paragraphs = ChangeRequest::model()->with('paragraph')->findAll($criteria);



The only thing is that all the Paragraphs are returned even though I changed CDbCriteria->select (I have just upgraded to Yii 1.1 though).

This is the generated SQL:




SELECT `ChangeRequest`.`id` AS `t0_c0`, `ChangeRequest`.`status` AS `t0_c2`, 

`ChangeRequest`.`paragraphId` AS `t0_c3`, `Paragraph`.`id` AS `t1_c0`, 

`Paragraph`.`text` AS `t1_c1`, `Paragraph`.`articleId` AS `t1_c2` 

FROM `ChangeRequest`  

LEFT OUTER JOIN `Paragraph` `Paragraph` 

ON (`ChangeRequest`.`paragraphId`=`Paragraph`.`id`)

WHERE (Paragraph.articleId = 1)



Many thanks :slight_smile: