开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜