cakephp cakedc search plugin - multiple table / multiple query search

Question

I am having some issues searching from multiple tables. I have a keyword search setup on a cards model which works fine, I also want the keywords to look into a contact model which has a forign key of card_id. I cannot seem to work out how to go about this.

The points to look at / with issues are the findByContacts function and:

array('Contact.street_suburb' => 'contacts', 
      'type' => 'subquery', 
      'method' => 'findByContacts', 
      'field' => 'Card.id'),

I have initially tried to just get the suburb to search but ideally I would like any of the fields within the contacts model to come up on the card search.

Thanks!

My code in Card Model is as follows:

public $filterArgs = array(
    array('name' => 'keyword', 'type' => 'query', 'method' => 'filterQuery'),
);

public $hasAndBelongsToMany = array('Contact' => array('with' => 'Contact'));

public function filterQuery($data = array()) {
    if(empty($data['keyword'])) { // keyword is the name of my search field
        return array();
    }

    $query = '%'.$data['keyword'].'%';
    return array(
        'OR' => array(
            array('Card.name LIKE' => $query),
            array('Property.name LIKE' => $query),
            array('Building.name LIKE' => $query),
            array('Stage.name LIKE' => $query),
            array('Contact.street_suburb' => 'contacts', 'type' => 'subquery', 'method' => 'findByContacts', 'field' => 'Card.id'),
        )
    );
} // END SEARCH 

// FIND BY CONACTS - PART OF SEARCH
// ------------------------------------------------------------------------------------>
public function findByContacts($data = array()) {
        $this->Contact->Behaviors->attach('Containable', array('autoFields' => false));
        $this->Contact->Behaviors->attach('Search.Searchable');
        $query = $this->Contact->getQuery('all', array(
            'conditions' => array('Contact.street_suburb'  => $data['contacts']),
            'fields' => array('foreign_key'),
            'contain' => array('Contact')
        ));
        return $query;
    }

Answer 1

I had similar trouble. It turns out I had to manually define 'with' on the HABTM model even though I had (thought) my fields were all following standard CakePHP convention. Also, I don't see you calling findByContacts in your filterArgs but maybe I am missing it.

So my code worked as such:

(Treasure model, pertinent code)

public $filterArgs = array(array('name' => 'makers', 'type' => 'subquery', 'method' => 'findByMaker', 'field' => 'Treasure.id'),);

public function findByMaker($data = array()) {
$this->MakersTreasure->Behaviors->attach('Containable', array('autoFields' => false));
$this->MakersTreasure->Behaviors->attach('Search.Searchable');
    $query = $this->MakersTreasure->getQuery('all', array(
        'conditions' => array("Maker.name LIKE '%" . $data['makers'] ."%'"),
        'fields' => array('treasure_id'),
        'contain' => array('Maker'),
    //the limit does not work, but it does make the timeout query halt so you can see what's going on
    //'limit'=>1
    ));
    return $query;
}

And then, also on the model I had to set with for Makers as such:

public $hasAndBelongsToMany = array(
    'Maker' => array(
        'className' => 'Maker',
        'joinTable' => 'makers_treasures',
        'foreignKey' => 'treasure_id',
        'associationForeignKey' => 'maker_id',
        'unique' => 'keepExisting',
        'with' => 'MakersTreasure'
    ),
            ...

So in your function, it doesn't look like you're referencing the junction Model when running the query or in your 'with'. Do you have a junction table on your DB for the HABTM? From the sounds of it, if Contact has a FK card_id then its not a HABTM relationship but OnetoMany or something else.