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
