I'm trying to get the results of a slightly complicated query into an active record, but I'm not sure what the most Yii-friendly approach is. Would anyone care to offer any suggestions? Here's the situation:
There are three tables:
Team (table of teams)
Player (table of players)
TeamPlayer (many-to-many between Team and Player)
I need to get a list of all players that are not attached to a particular group of teams, described here as "someCondition". Here's the query that I'm running:
SELECT * FROM Player
WHERE Player.playerId NOT IN
SELECT Player.playerId FROM Player
INNER JOIN TeamPlayer ON TeamPlayer.playerId = Player.playerId
INNER JOIN Team on Team.teamId = TeamPlayer.teamId
WHERE Team.someCondition = :someCondition
The results I'm interested in are in the Player table, so I'm hoping to just get the results of this query into my nifty little Player active record generated from the Player table.
I'm comfortable using either Player::relations() or generating the appropriate CDbCriterias for simple queries and single joins… What is tripping me up is the multiple joins and the subselect.
Thanks in advance!