Re: CakePHP model db joins

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