best practice for complex query results using CActiveRecord

Archives Yii 1.0.x Discussions
1

Greetings, all!

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.

Thoughts?

Thanks in advance!

2

Hey jazzy,

I’m trying to figure out the ActiveRecord myself. Perhaps looking at your problem will help me with mine. :)

Could you set up something like this relation in your Player model:

'team' => array(self::MANY_MANY, 'Team', 'TeamPlayer(playerId,teamId)'),

Then for your actual query:



$criteria = new CDbCriteria();


$criteria->condition = 'Team.someCondition=:someCondition';


$criteria->params = array(':someCondition' => someCondition);


$players = Player::model()->with('team')->together()->findAll($criteria);

I'm not sure about best practices for setting the criteria that is for a query determined by the 'with'. Calling 'together()' makes sure it is called as one query which has helped me get all my conditions working correctly. Again, not sure if that's the best way to do it but it has worked for me.

3

Hey Boston, thanks for the suggestion.  I gave it a shot, but unfortunately, Yii responded with the following:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Team.someCondition' in 'where clause'

So, at first glance, it looks like I can't get away with stuffing it into a criterion as mentioned.  Any other ideas?  I also thought about writing the whole query out like this:

$sql = '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

            )';

$command = $this->getDbConnection()->createCommand($sql);

$command->bindParam(':someCondition', $someCondition, PDO::PARAM_INT);

$dataReader = $command->query();

That would probably get the results I'm looking for, but I'm not sure how to conveniently turn this into a list of my Player models.  Again, not sure if thats a very Yii-ish way to do things, but I'll probably give it a try as well.

Any other ideas?

4

Have you turned on logging so you can see the sql being generated (extremely awesome logging in Yii)? It'd be helpful if I/we could see the sql being generated. If you are joining on Team, any column in Team should be available, although I'm having some issues myself with Yii to where I've resorted to writing my own sql.

If you want to stick with your sql, I think you could use:



$players = $command->queryAll();//will return array of ActiveRecords


$playerData = array();


foreach($players as $player) {


    //doing a var_dump on $player should give you a Player active record


    $playerData[$player->id] = $player->first_name . ' ' . $player->last_name;//or whatever


}


//...do whatever you want with the player data.

Obviously, you want to get it working, but if you have some time I wouldn't mind seeing you get it working with the Yii/ActiveRecords instead of your own sql. Like I said, I'm trying to understand Yii better so any info I can get helps.

5

Ok, so I figured out part of the issue.  After looking at the log, the query was being generated with an alias for the table I was including as part of the relation.

This was my relation:

'team'=>array(self::MANY_MANY, 'Team', 'TeamPlayer(teamId, playerId)'

However, when the query was generated, it had an automatically generated alias for the table in my relation, something like 't1'.  After reading the documentation here:

http://www.yiiframew…elations-detail

I found that you can specify the alias yourself if you like.  So, I changed my relation to this:

'team'=>array(self::MANY_MANY, 'Team', 'TeamPlayer(teamId, playerId)', 'alias'=>'teamAlias'),

Then, changed my code to this:



    $criteria = new CDbCriteria();


    $criteria->condition = 'teamAlias.someCondition=:someCondition;


    $criteria->params = array(':someCondition' => $someCondition);


    $players = Player::model()->with('team')->together()->findAll($criteria);

So, that works nicely now.  Well done.

However, once I ran the code, it reminded me that it only answers 1/2 of my original question.  Remember that in my original query, I was attempting to stuff all of this inside a subselect.  Any idea how to do that in a pretty, Yii-oriented way?  Obviously, I can grab the results, stuff them into a list, and then write a second query using the results of the inner query, but I'm rather interested if there's a way to issue this entire query in Yii without too much trouble.

Any thoughts on where to go from here?

6

Glad you got it working (1/2) :)).

I'm somewhat confused by your query though. Perhaps you are just giving an example, but it looks like you are unnecessarily making two queries from one. Couldn't you just do != on your condition?



$sql = 'SELECT * FROM Player


              INNER JOIN TeamPlayer ON TeamPlayer.playerId = Player.playerId


              INNER JOIN Team on Team.teamId = TeamPlayer.teamId


              WHERE Team.someCondition != :someCondition


            ';

Or Yii way:



$criteria = new CDbCriteria();


$criteria->condition = 'teamAlias.someCondition!=:someCondition;


$criteria->params = array(':someCondition' => $someCondition);


$players = Player::model()->with('team')->together()->findAll($criteria);

Maybe I'm confused with your query, but you don't seem to require enough columns/tables to need multiple queries.

7

Yeah, the query is actually more complicated than the one I had written, but I was using that query (along with someCondition) as an example.  Basically, I need to do the subselect because in the system I'm building, because a player can belong to zero or more teams and the teams vary by event.

So, in effect, I need to do some sort of NOT IN or ANTI JOIN in order to grab the players who are not associated with any teams for a specific event.  I guess what it breaks down to is as simple as this:

SELECT * FROM Foo

WHERE Foo.id NOT IN

(

  SELECT id FROM Bar

)

If I can write this in some sort of clever Yii style, cool.  If not, I'll probably just end up doing the var dump as you suggested previously.

8

I figured it was a little more complicated. One can hope though…

I'd guess you need a nested with. Without knowing more about your query, it's hard to say. I'd recommend exploring something like this:

Keep the work done above, just add to it. In your Team model, define the relation:



'bar'=>array(self::HAS_MANY, 'Bar', 'id'),

Then change your query to:



$players = Player::model()->with('team.bar')->together()->findAll($criteria);

I think that is the approach you should take, but I'm having a little trouble without seeing something more concrete. In the end, I'd guess that Yii will end up writing two queries anyway.

I'm going to try to work on setting up a quick test system to see if I can figure something out with this. Would be good for me to get the experience anyway. I'll let you know if I come up with anything.

9

An interesting problem. I think the key to success is to first cut this down to a single SQL statement. Just an idea, but have you tried something like this:

SELECT * FROM 


Player p


LEFT JOIN TeamPlayer tp ON tp.playerID=p.playerId


LEFT JOIN Team t ON t.teamId=tp.teamId AND someCondition=:someCondition


GROUP BY p.playerId HAVING COUNT t.id=0
10

Mike,

That seems like it would work, plus it’s much simpler than doing a nested query. Too bad you weren’t here a few hours ago ;)

Will you let me know if that works for you jazzy?

11

If the SQL works you need a way to add the HAVING. CDbCriteria doesn't provide this property but you could try to add it to the group property. Not sure if it works though. The complete requirement then would be:

team'=>array(self::MANY_MANY, 'Team', 'TeamPlayer(teamId, playerId)', 'alias'=>'team'),
<?php


$criteria = new CDbCriteria();


$criteria->condition = 'team.someCondition!=:someCondition';


$criteria->params = array(':someCondition' => $someCondition);


$criteria->group='playerId HAVING COUNT(team.teamId)=0';


$players = Player::model()->with('team')->together()->findAll($criteria);
12

Correction: CDbCriterias has “having”. ;)

13

Oh indeed :). So that should make it even easier…

14

Indeed, sleep seemed to be the critical element for me here as well.  For some reason, I was determined to believe that I needed a nested query, when in fact, adding an AND to one of my JOIN clauses solved the problem for me.

When all was said and done, this is what I wound up using…



$criteria = new CDbCriteria();


$criteria->join = 


  'LEFT JOIN TeamPlayer tp ON tp.playerId=Player.playerId


   LEFT JOIN Team t ON t.teamId = tp.teamId AND t.someCondition = :someCondition';


$criteria->condition = 'tp.playerId IS NULL';


$criteria->params = array(':someCondition' => $someCondition);





$players = Player::model()->findAll($criteria);

Again, the use of someCondition represents a number of boolean evaluations, but this was the gist of the code.  The only minor stumbling block for me, aside from the misconception about the nested query, was realizing that I could specify two joins in the join clause.  For some reason, I had it in my head that I could only perform one join.  Clearly, the lack of sleep working on my after hours project had turned my brain into mush.

Thanks to all for your input!