I think you have your belongsTo/hasMany backwards. An artist hasMany songs, and a song belongsTo an artist.
-d On Nov 28, 8:18 am, Neil Craig <[email protected]> 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
