Re: CakePHP model db joins

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 <neil.cr...@thedotproduct.org> 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
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php