CakePHP searchable HABTM relations

Today I had a moment where I had to create a simple search function, but extending over multiple models. Let me start by explaining the idea;

I have 2 models to use in the search; Note and Category.
A Note can have multiple Categories, and a Category can be linked to multiple Notes. Meaning it has to be a many-to-many relation.
In CakePHP that is called a HasAndBelongsToMany relation, or HABTM.

The 2 models are then linked via a link-table; notes_categories, which only contains a note_id and a category_id field.

There is a list of Notes which needs to be searchable and filterable. There are 2 filters and 1 search-box.

  • a Type filter (a note can have either type A or type B)
  • a Category filter (a note can have one or more categories)
  • a message search field (a note has a message in it, who’s text must be searchable)

The Type filter and search field are quite simple, as those searchables are both saved in the Note model.

// Handle search queries
$query = array();
if (isset($_GET['q'])) {
    $query[] = array('Note.message LIKE "%'.$_GET['q'].'%"');
    $this->set('q', $_GET['q']);
}

if (isset($_GET['type']) && $_GET['type'] != 'all') {
    $query[] = array('Note.type' => $_GET['type']);
    $this->set('type', $_GET['type']);
}

$this->set('notes', $this->Paginator->paginate($query));

The Category however is a bit more complex, and required some thought.
After trying some alternative methods (such as containing and such) the solution eventually was presented in this 2009 article on the CakePHP Bakery.

First, we join the link-table, find all instances of a Note there.
Then we connect to the Categories through the link-table, where the category_id is the same as the category the filtering happens on.

if (isset($_GET['cat']) && $_GET['cat'] != 'all') {
    $this->paginate = array(
        'joins' => array( 
            array( 
                'table' => 'notes_categories', 
                'alias' => 'NotesCategories', 
                'type' => 'inner', 
                'foreignKey' => false, 
                'conditions'=> array('NotesCategories.note_id = Note.id') 
            ), 
            array( 
                'table' => 'categories', 
                'alias' => 'Category', 
                'type' => 'inner', 
                'foreignKey' => false, 
                'conditions'=> array( 
                    'Category.id = NotesCategories.category_id', 
                    'Category.id' => $_GET['cat'] 
                ) 
            ) 
        )
    );
    
    $this->set('cat', $_GET['cat']);
}

Well that pretty much helped me write a simple search function over multiple Models!

Leave a Reply

Your email address will not be published. Required fields are marked *