A couple of errors: - Your Artist model has this as the first line: class Post extends AppModel. It should be class Artist extends AppModel. - Your $options['joins'] statement in your index function of the songs_controller is unnecessary (and in fact you are not bringing it into your find statement anyway).
The way Cake works is that when doing a find it references the model associations ($belongsTo etc), so once you have corrected the first error above you will be ready to go. By default it will only bring in rows from the current model and not from any associations. The 'blunt' way to extend that is by setting the recursive value (see here http://book.cakephp.org/view/1063/recursive). If you set that to 1 or 2 you should get the related model data. Think of associated data as onion rings; as you extend the recursive level you get more and more data but not in a very controlled fashion. By far the better way is to use the Containable behaviour in your AppModel - see here: http://book.cakephp.org/view/1323/Containable and set recursive to -1 throughout. You can then finely control the finds. Your find statement then becomes: $songs = $this->Song->find( 'all', array( 'contain' => array( 'Artist', 'Genre' ) ) ); $this->set('songs', $songs); Each song will now have a key for its artists and genres (which you didn't describe in your email, but assume they are set up correctly). Finding is pretty elementary stuff; I suggest you read this section before going much further: http://book.cakephp.org/view/1017/Retrieving-Your-Data Jeremy Burns Class Outfit http://www.classoutfit.com On 28 Nov 2011, at 13:18, Neil Craig wrote: > 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 > [email protected] For more options, visit this group at > http://groups.google.com/group/cake-php -- 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 [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
