A find condition about hasMany relationship in cakephp
I'm working an action for message search in my project,and here are the two models:Msgcontent and Msgblock.Relationship is Msgblock hasMany Msgcontent.What I want to do is get all the Msgblock records that contain Msgcontent with some searching keyword.My code as follows:
if($keyword)
{
$conditions['and'] = array(
'Msgcontent.content LIKE'=>'%'.$keyword.'%',
'Msgcontent.content <>'=>''
);
$results = $this->Msgblock->Msgcontent->find('all',array('group'=>array('Msgblock.开发者_JAVA技巧chatsessionid'),'conditions'=>$conditions));
}
It seems not a good work.Is there any better solution?Thanks.
Short of writing your own SQL query with appropriate JOINs, this is about the easiest way to do it in Cake with two queries:
$ids = $this->Msgblock->Msgcontent->find('all', array(
'fields' => array('Msgcontent.msgblock_id'),
'recursive' => -1,
'conditions' => ...
));
$this->Msgblock->find('all', array(
'conditions' => array('Msgblock.id' => Set::extract('/Msgcontent/msgblock_id', $ids))
));
You can use bindModel with conditions, Ex:
$this->Msgblock->bindModel(
array(
'hasMany' => array(
'Msgcontent' => array(
'className' => 'Msgcontent',
'conditions' => array(
'Msgcontent.content LIKE' => "%$keyword%"
)
)
)
);
and then you can use your find without conditions because it's already done when binding.
This is a perfect situation to use Cake's Containable behavior, as discussed in multidèria's excellent answer to a very similar question. Either add Containable
to your Msgcontent model's $actsAs
, or attach the behavior on the fly.
Setup a sub query in your conditions that searches the hasMany table and returns a count if found.
(select count(id) from YOUR_HAS_MANY_TABLE where YOUR_HAS_MANY_TABLE.foreign_key=MAIN_TABLE.id where YOUR_HAS_MANY_TABLE.field_to_search='%SEARCH%')>0
It is not incredibly efficient but with proper indexing it is a solution that can get the job done.
精彩评论