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;
    }

i think you are missing a couple of things, but can you define not working? what exactly is NOT working? does it search for the cards correctly? does it search for the contacts correctly? does it do a query at least? or simply doesn't do anything at all? — api55, Oct 24 '11 at 13:04
Thanks, the error I get when above in in place is: Warning (512): SQL Error: 1054: Unknown column 'Contact.street_suburb' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684] I guess this means the contact and card models are not linking correctly... — sluggerdog, Oct 25 '11 at 20:50

score 0 · Answer 1 · answered Feb 01 '14 at 16:51

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.

cakephp cakedc search plugin - multiple table / multiple query search

1 Answers1