Re: CakePHP model db joins

Look s like your models are not being used due to invalid file naming...

Model name should be singular as: Artist.php , Song.php

Also the is a typo, class Post should be Class Artist :

Model/Artists.php
<?php
class Post extends AppModel

Probably you don't need the options['joins] as this is very bias query… :)

Andras Kende
http://ww.kende.com

On Nov 28, 2011, at 7:18 AM, 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

--
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