AR relation on composite key

Yii 1.1 General Discussion
1

Hello,

I have a question regarding Active Record relations.

Assume following two tables.

Team

team_id, youth_team_id,…

Match

match_id, home_team_id, away_team_id, source_system

I have corresponding Active Records, and I’m struggling to create relations. I want to create relation ‘youthmatches’ on Team AR that would follow this pattern:


SELECT * team t LEFT OUTER JOIN match m on (t.youth_team_id=m.home_team_id or t.youth_team_id=m.away_team_id) and source_system='youth'

I cannot get this to work both lazy loading and relational way.

I would appreciate any help.

Thanks

2

Try this:

‘youthmatches’ => [self::HAS_MANY, ‘Match’, [‘home_team_id OR away_team_id’ => youth_team_id’’], ‘condition’=>‘youthmathches.source_system = “youth”’

never used it like this, but it’s worth trying.

Or U can make 2 relations

3

Thank for your answer Dragan.

I tried it, but it does not work. Yii recognizes ‘home_team_id OR away_team_id’ as a column name and SQL query won’t run.

I cannot use two relations since I need to have all matches ordered by match date. Any other ideas? :)

4

I don’t quite understand how your database is setup. Does your team table have a composite primary key with those 2 id fields? Shouldn’t the match team id columns be paired up with the team.team_id column?

Anyway from the team model, I think what you should be doing is creating 2 has_many relations. One to grab matches where that team is the "home" team, and another where they are the "away" team. If you want to grab all the matches for a specific team, then I think it would be better to start from match model