Can I use subqueries in a 'containable' condition?
In my CakePHP I have ModelA which hasMany ModelB. ModelB has an int value Q.
Can I query ModelA and use containable to ensure that only those ModelB records with the maximum value for Q?
I've tried this:
$this->ModelA->contain(array(
'ModelB.Q =(SELECT MAX(ModelB.Q) FROM modelb ModelB WHERE ModelA_id = ' . $id . ')'
));
But it throws a MySQL error because CakePHP interprets the right hand side of that equality operator as a field (at least I think that's why) and so dots it.
... WHERE `Draw`.`round` =.(SELECT MAX.(`Draw`.`round`) ...
Is there a way to do this? I'd prefer not to have to drop down into $query()
mode, if at all possible.
EDIT OK, after trying to follow the advice on the page that api55 suggest开发者_如何学编程ed, I have this code:
$dbo = $this->Tournament->getDataSource();
$conditionsSubQuery['"Draw"."tournament_id"'] = $id;
$maxRounds = $dbo->buildStatement(array(
'fields' => array('MAX(Draw.round) AS prevRound'),
'table' => $dbo->fullTableName($this->Tournament->Draw),
'alias' => 'Draw',
'limit' => null,
'offset' => null,
'joins' => array(),
'conditions' => $conditionsSubQuery,
'order' => null,
'group' => null
),
$this->Tournament
);
$maxSubQuery = ' "Draw"."round" = (' . $maxRounds . ') ';
$maxSubQueryExpression = $dbo->expression($maxSubQuery);
$this->Tournament->contain(array(
'Entrant.selected = 1',
$maxSubQueryExpression
));
$tournament = $this->Tournament->read(null, $id);
But when it runs, it gives me 7 notice/warnings. The first 6 are to do with an object being passed instead of a string:
preg_match() expects parameter 2 to be string, object given
And 6 variations on this:
Object of class stdClass to string conversion
The last is less clear:
Model "Tournament" is not associated with model ""
I suspect I'm being colossally stupid, but there we go.
The contain uses conditions as a normal find, a subquery can be generated and put in conditions. So you should be able to do this as well. Try the subquery part in here and tell me how did it go ;)
This way of generating subqueries for conditions shouldn't fail :D since is the cakephp way. If you got an error or something comment the answer to see if i can help.
精彩评论