Getting what you want with HABTM using multiple models and pagination…

Oh my o my… This has cost me hours/days. Maybe it’s easy if you’re a long term CakePHPer. But I just started developing with this framework, also never used anything like an MVC framework before. Ok, I know how to do databases, php, javascript,etc… But this is something else. The basics of CakePHP are quite easy to understand and use. Simple stuff like you read in the tutorials: a user has posts, a post has comments. These are all hasMany and belongTo relations, those just work fine when doing pagination. But then you have something like Posts have Tags. Then you get into the hasAndBelongsToMany (HABTM) arena, where the ‘fun’ begins… With just to tables and doing a findAll or paginate is still not all that complicated. (by the way I’m using nightly builds of the 1.2 version).

So what has given me so much ’stress’ the past few days? Well, I’m developing a site that now has something like 30 tables (habtm join tables included), not all that big yet. But I have one table (addresses) that has multiple habtm relations (sections, areas, tags; among others). Still no problem if you just do a plain findAll(), but what if you want to search for addresses that are linked to a certain section AND area and maybe even a tag? To make it even more complex, an address hasMany telephone numbers, URLs and belongs to a user and a country and hasOne logo. There is more in the model but this gives you an idea of what I’m doing.

So I present a form to the user of the site. The form has some SELECTs where the user can select which section, area, tag and/or zip code to find addresses for. The result should be a paginated presentation of addresses. An address consists of fields like: company name, street name, zip code, telephone number(s), URL(s), logo, etc.. Here comes the somewhat simplified code (there are some comments in the controller code to explain what happens):

(Please note that this code will not work just like that, these are just some code fragments to show how I did my implementation. If you want some more info, please leave a comment. If you find an error, type or other, let me know and I will fix it. Thanks.)

Model for Addresses

class Address extends AppModel {
  var $name = 'Address';
  var $belongsTo = array(
    'Country' => array('className' => 'Country',
    'foreignKey' => 'country_id'),

    'User' => array('className' => 'User',
    'foreignKey' => 'user_id'),
  );

  var $hasOne = array(
    'Logo' => array('className' => 'Logo',
    'foreignKey' => 'user_id',
    'depent' => true),
  );

  var $hasMany = array(
    'Telephonenumber' => array('className' => 'Telephonenumber',
    'foreignKey' => 'address_id'),
    'Website' => array('className' => 'Website',
    'foreignKey' => 'address_id'),
  );

  var $hasAndBelongsToMany = array(
    'Area' => array('className' => 'Area',
    'joinTable' => 'addresses_areas',
    'foreignKey' => 'address_id',
    'associationForeignKey' => 'area_id'),

    'Section' => array('className' => 'Section',
    'joinTable' => 'addresses_sections',
    'foreignKey' => 'address_id',
    'associationForeignKey' => 'section_id'),

    'Keyword' => array('className' => 'Keyword',
    'joinTable' => 'addresses_keywords',
    'foreignKey' => 'address_id',
    'associationForeignKey' => 'keyword_id'),
  );
}

Model for join table
You should have one for each habtm join table

class AddressesSection extends AppModel {
  var $name='AddressesSection';
  var $belongsTo=array('Address', 'Section');
}

join table example

id, integer, primary, autoinc
address_id, integer
section_id, integer

Controller for the query form

class SearchController extends AppController {
  var $name = 'Search';
  var $uses = array('Section','Area','Keyword');
  var $helpers = array('Html', 'Form', 'Ajax');
  function index() {
    $areas = $this->Area->generateList();
    $sections = $this->Section->generateList();
    $keywords = $this->Keyword->generateList();
    $this->set(compact('areas', 'sections', 'keywords'));
  }
}

Controller to do the query

function search() {

/* bind the HABTM tables to the Address table using a hasOne
   relationship (see sources below for explanation on this) */

  $this->Address->bindModel(array('hasOne'=>array('AddressesArea'=>array())),false);
  $this->Address->bindModel(array('hasOne'=>array('AddressesKeyword'=>array())),false);
  $this->Address->bindModel(array('hasOne'=>array('AddressesSection'=>array())),false);

/* set the fields that should be returned by the paginate() call, this
   basically is what you would normally do in a sql SELECT statement
   to specify which columns the query should return. This is just for
   the query on the addresses table. For the end result cake will also
   give you all the HABTM stuff associated with each address, nice!
   The DISTINCT is important here, because of all the associations to
   many rows would be returned otherwise. */

  $this->paginate['fields'] = array('DISTINCT Address.id', 'name',
                    'street', 'zip', 'city','Country.*','Logo.*');

// ok, fire-up the query already! result is placed in $addresses for the View

  $this->set('addresses',$this->paginate('Address',Set::filter($this->postConditions($this->data))));
}

(I will explain the Set::filter($this->postConditions($this->data))) in another article)

View for query form (query.ctp)

<div class="search form">
<?php echo $form->create('Address',array('url' => '/addresses/search'));?>
<fieldset>
<legend><?php __('Search Address');?></legend>
<?php

/* Note: use the join table field name (AddressesSection.section_id)
   here as a name for the INPUTs, otherwise the cool PostConditions
   function in the Controller won't work. */

echo $form->input('AddressesSection.section_id', array('type'=>'select',
     'label'=>__('Section',true), 'empty'=>true, 'selected'=>0));
echo $form->input('AddressesArea.area_id', array('type'=>'select',
     'label'=>__('Area',true), 'empty'=>true, 'empty'=>true, 'selected'=>0));
echo '<label for="Keyword">';
__('Tag');
echo '</label>';
echo $ajax->autoComplete('AddressesTag.tag_id','/tags/autoComplete');
echo $form->input('zip');
?>
</fieldset>
<?php echo $form->end('Submit');?>
</div>

View for end result (search.ctp)

/* there should be code here to check if all data really exists before
   using it, but this is for demo purpose only */
foreach ($addresses as $address) {
  echo $address['Address']['name'].'<br />';
  echo $address['Address']['street'].'<br />';
  echo $address['Address']['zip'].' '.$address['Address']['city'].'<br />';
  foreach ($address['Tags'] as $tag) {
    echo $tag['tag].' ';
  }
  echo '<br />';
}

Now that it is finished (well this part is), not that much code at all. But it took me hours of trying this and that, reading loads of web pages, articles and the manual, tearing the little hair I have left off of my head…

Sources I used to come to this solution (and believe me, I read a lot more than just what’s on these links, my eyes, my Eyes, arghhh). Thanks to all the people that wrote this stuff, it helped me a lot:

nabble.com – Help me with paginate, please

Paginating the results after creating a dummy model to filter HABTM results?

Filtering HABTM associations using columns in both models

CakePHP 1.2 manual

But it works now, so I’m happy and can continue baking the rest. Hope that things will get a bit easier now, because that’s what CakePHP should do for us right: make development life a bit easier / less stressful. So maybe this article will help some of you creating that wonderful website, leave a comment if you like. Also tips on this subject are very welcome, maybe I did something completely wrong here. This article cost me more than an hour to write, I need sleep now…