Find by related table attribute

Archives Yii 1.0.x Discussions
1

I’m dealing with a many to many relationships.

These are my tables:

Issue (id, title, description)

Topic (id, title)

Issue_Topic (id, issue_id, topic_id)

So now I’m trying to find all the Issues for a given Topic.

My relation is as follows:

Issue.php > relations()




'topics'=>array(self::MANY_MANY, 'Topic', 'Issue_Topic(issue_id, topic_id)'),

This works fine for the Issue.actionShow() method where I say:




$models = Issue::model()->with('topics')->findByPk($id);

Now, I’m going for a list of Issues that belong to a particular topic. I was imagining it working something akin to:




$models = Issue::model()->with('topics')->findByAttributes( /* topic.id = $id OR Issue_Topic.topic_id = $id */ );

I cannot get the above to work. I keep getting the error message about topic.id is not a field of the issue table.

I have found a way that I can accomplish what I want, but it seems like Yii’s relationship handling should be able to deal with this.

This is what I have that works, which I want to not do.




$criteria = new CDbCriteria;

$criteria->join = 'inner join issue_topic on issue.id=issue_topic.issue_id inner join topic on issue_topic.topic_id=topic.id';

$criteria->condition = 'topic.id='.$tid;

$models = Issue::model()->findAll($criteria);

It seems crazy to me to have to define this relationship when it’s already there in the issue model. What am I missing here? What is the way to do findAllByAttributes where the attribute is on the related table?

2

I did not try it for many to many but for other relations you can set ‘together’=>true in your relation definition array. This will result in a single SQL statement will all the fields included.

Hope this helps.

/Kaf

3

What would the findAllByAttributes line look like? Because I still have the same problem of it expecting attributes to belong to the primary table and not the joined table.

4

I remember I tried something and it required a ‘together’ option in the relations options… something like that:




'topics'=>array(self::MANY_MANY, 'Topic', 'Issue_Topic(issue_id, topic_id)', 'together'=>true)

Hope this works…

/Kaf

5

So then in the findAllByAttributes(array(‘whatid’=>$id));

Answer what this ‘whatid’ is. I have a topic id, so: $tid. Is it topic.id=$tid, topic_id=$tid, Issue_Topic.topic_id=$tid ?? Do you see where my problem is?

6

Did you try topics the name of the relation… I think it should be the name you geve to the relations which in your case topics.

I have something similar to this running fine at my end.




$models = Issue::model()->with(array('topics'=>array('condition'=>'id=' . $id)))->findAll();

you can also try when together is true:




$models = Issue::model()->with('topics')->findAll('id=' . $id);

I tried with using table_name.attribute and I keep on getting errors as well… and I find it strange too.

/Kaf

7

I just remembered that you have the same name ‘id’ for both tables… so I have doubts option #2 would be what you are looking for…

/Kaf

8

Adding the condition in a with call like that should (I think) automatically add the defined alias for the relationship when it generates SQL.

You can explicitly define the table alias used in a relationship by specifying ‘alias’ => ‘someAlias’ in the relation array. It defaults to the relationship name.

Then in your condition, use the defined alias if you need to.

9

This is the way I am set up at the moment and I can’t get your suggestion #1 to work. It’s giving me all results.

Issue->relations()




'topics'=>array(self::MANY_MANY, 'Topic', 'issue_topic(issue_id,topic_id)', 'joinType'=>'inner join'),

controllers.issue.ListLatest(CAction)->run()




$models = Issue::model()->with(

    array(

        'subjects'=>array(

             'alias'=>'mysubjects',

             'condition'=>'mysubjects.id='.$sid,

        ),

    ))->findAll();

Still isn’t functioning properly.

10 



$models=Issue::model()->with(

  array(

    'subjects'=>array(

         'alias'=>'subject', 

         'condition'=>'subject.id='.$sid,

         'together'=>true),

    'voteCount'

  )

)->findAll();

This works except for one small thing. An Issue has many subjects, but this only returns one subject. Obviously, I’ve set a condition to match a specific subject, but I still want the other subjects for the Issue. I can get the subjects the original way I got this to work that looks like this:




$criteria=new CDbCriteria;

$criteria->join='inner join issue_subject on issue.id=issue_subject.issue_id inner join subject on issue_subject.subject_id=subject.id';

$criteria->condition='subject.id='.$sid;

$models=Issue::model()->with('subjects','voteCount')->findAll($criteria);

I just don’t see the point of describing the join like that. In looking at the db profile output, it seems like it’s doing the join properly most of the time. But somehow setting the condition and join this way gives me what I want. I’d like to get something like the first way to work, but I’ll live with this second way if there is no alternative.

11

Hi,

I have the same issues. Did you, or somebody else, found another solution with less SQL in it?

Cheers

Ramon

12 



$model = subject::model()->with(array('issue', 'issue.subjects')->findByPk($id)


$issue = $model->issue;

$subjects = $issue->subjects;