CakePHP model db joins

Hi all

My apologies if this has been posted already, i just can't seem to
find a solution so i am hoping someone will be kind enough to have a
look and make some suggestions for me...

I'm an experienced PHP dev and sysadmin and am building a small
website, and subsequently hoping to persuade my company to adopt (and
contribute to) cake. I'm having trouble making cake join my database
tables and have tried everything i can find on the internet but
nothing seems to work.

I'm trying to join 2 tables via the belongsTo/hasOne/hasMany etc
relationships in the models for my site. My site aims to list songs
with artist and other information so i have separate db tables for
songs, artists, genres etc. My files are as follows:

Model/Artists.php
<?php
class Post extends AppModel
{
public $name = 'Artist';

public $belongsTo=array("Song" => array("className"=>"Song"));
}

Model/Songs.php
<?php
class Song extends AppModel
{
public $name = 'Song';

var $hasMany=array('Artist'=>array('className'=>'Artist'));
}

Controller/ArtistsController.php
<?php
class ArtistsController extends AppController
{
public $helpers = array ('Html','Form','Cache');
public $name = 'Artists';

function index()
{
$this->set('artists', $this->Artist->find('all'));
}
}

Controller/SongsController.php
<?php
class SongsController extends AppController
{
public $helpers = array ('Html','Form','Cache');
public $name = 'Songs';

function index()
{
$options["joins"]=array(
"table"=>"artists",
"foreignKey"=>false,
"alias"=>"`Artists`",
"type"=>"inner",
"conditions"=>"Song.artist_id=Artist.id"
);

$this->set('songs', $this->Song->find('all'));
}
}

The DB tables are:

songs:
songs | CREATE TABLE `songs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`artist_id` int(10) unsigned NOT NULL,
`decade_id` int(10) unsigned NOT NULL,
`genre_id` int(10) unsigned NOT NULL,
`title` text COLLATE utf8_bin NOT NULL,
`youtube_url` text COLLATE utf8_bin NOT NULL,
`itunes_trackId` text COLLATE utf8_bin NOT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

artists:
CREATE TABLE `artists` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` text COLLATE utf8_bin NOT NULL,
`itunes_artistId` text COLLATE utf8_bin NOT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

When i run a listing of songs in my view, i only get data from the
songs table, i want to be able to pull fields from the artists table
(and also from genres). If i do a manual join using the raw SQL query
method, i get data from both tables, it works fine.

My join is on songs.artist_id=artists.id

System config:
Debian Linux
PHP 5.3.5
Percona fork of MySQL 5.5
CakePHP 2

If anyone has any suggestions, i would be extremely grateful.

Many thanks
Neil

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.

To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php