How can i implement this in cakephp

Question

Pls help me implement this in cakephp

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 1

I'm not sure what specifically you are struggling with, but you can make your query clearer by separating the join conditions from the selection criteria:

select s.name
from safari s 
inner join regions_safaris sr on(s.id=sr.safari_id)
inner join region r on(sr.region_id=r.id)
where r.name='yourRegion' 
and s.duration=5;

Your query also had the mistake of using safaris_regions instead of regions_safaris, and aliasing the regions table twice.

Answer 2

you should change the region table to regions, and safari table to safaries to make it work with cakephp. after setting up the relations in the model, you would use a find.

//safaries_controller.php 
$this->Safari->find('all', array('fields'=>'Safari.name', 'conditions'=>array('Region.name'=>'yourRegion'));