How do I find the following data

Question

I,m using two tables (regions and safaris) having many to many relationship. the tables are joined by another table called regions_safaris (with the fields - region_id $ safari-id). I want to fetch all safaris meeting a given criteria like durations of 5 days and in a given region. This should be implemented in cakephp

Answer 1

This seems more like a database issue than a php issue. Here's the syntax (for most DBs like e.g. MySQL) to get a list of all 5-day safaries that are mapped to region 'yourRegion'

table safari:
id
name
duration

table region:
id
name

table regions_safaris:
safari_id
region_id

select s.name
from region r, safaris_regions sr, region r, safari s
where r.name='yourRegion'
and sr.region_id=r.id and s.id=sr.safari_id and s.duration=5;

Answer 2

Assuming you have your models associated correctly. These are the lines of code I would use in a similar situation. When filtering on a habtm condition, I've found it helpful to start with the model that has the most restrictive filter (or the one that pears down the database the fastest). In this case, the region.

From the Safaris Controller:

$conditions = array('conditions' => array('Region.id' => 'yourRegion', 'Safari.duration' => 5);
$this->Safari->Region->recursive = 1;
$safaris = $this->Safari->Region->find('all', $conditions);

You should then be able to print_r($safaris) and access each safari via a nested region array. i.e.:

$safaris['Region'][0]['Safari'][0]['name']

This should give you the first returned regions first associated safari that matches the criteria. print_r($safaris) to get a better look at the data structure and you will see what I mean.