Search term about hasMany relationship in cakephp

Question

I am working on finding messages in my project, and here are two models: Msgcontent and Msgblock. Relationship is Msgblock hasMany Msgcontent. What I want to do is get all the Msgblock entries containing Msgcontent with some keyword searches. My code is as follows:

if($keyword) { $conditions['and'] = array( 'Msgcontent.content LIKE'=>'%'.$keyword.'%', 'Msgcontent.content <>'=>'' ); $results = $this->Msgblock->Msgcontent->find('all',array('group'=>array('Msgblock.chatsessionid'),'conditions'=>$conditions)); } 

This doesn't seem to be a very good job. Is there a better solution? Thanks.

Answer 1

Other than writing your own SQL query with the appropriate JOINs, this is the easiest way to do this 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)) )); 

Answer 2

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 unconditionally, because it was already done with the binding.

Answer 3

This is an ideal situation to use Cake Containable behavior, as discussed in an excellent answer from several countries to a very similar question . Either add Containable to your Msgcontent $actsAs , or bind behavior on the fly.

Answer 4

Configure an additional query in your conditions that searches the hasMany table and returns a counter 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 effective, but with the right indexing, it is a solution that can do the job.