Re: CakePHP model db joins

Hi everyone

Thank you so much for your advice, i tried it all and experimented a
little to work out what was wrong and it turned out to be a bit from
each persons advice :-).

To summarise, in case it helps someone else in the future, here is
what i had wrong:

* Model files were pluralised e.g. i had Model/Artists.php - this
should be Model/Artist.php
* Model/Song.php - the relationship needed to be belongsTo rather than
hasMany
* Model/Artist.php had an incorrect class name (i had copied this from
the blog example and forgot to change it)

So my files are now:

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

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

public $belongsTo=array("Artist" => array("className"=>"Artist"),
"Genre" => array("className"=>"Genre"));
}

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

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

DB schemas unchanged.

The above now successfully joins my database tables - i will add
options to the find() to limit what is retrieved.

Thank you all again for your help, i had made many mistakes but got
there with your help.

Neil

On Nov 29, 8:51 pm, dw <mil...@gmail.com> wrote:
> 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