CakePHP containable behaviour troubles 1.3

Hi, I have a model called Media that belongsTo Artist and hasMany Vote.  I am trying to fetch Vote and Artist data from within the Media model.  I am using containable to try and fetch the Artist and Vote data, without fetching another related model.  With or without the containable behaviour, the find() call does not fetch Vote data.  It happily fetches Artist data, and the other related Model I haven't mentioned.  When using containable it doesn't fetch the other Model (so containable is working on some level).

Here is the call to find() from within media.php (the model file).
 
$votes = $this->find('all',
	    array(
                'contain'=>array('Artist','Vote'),
		'fields'=>array('Media.artist_id','COUNT(*) AS votes'),
		'conditions'=>
		    array(
			"Vote.created BETWEEN '" . $start . "' AND '" . $end . "'",
			'Artist.department_id' => $department
		    ),
		    'group'=>'Media.artist_id',
		    'order'=>array('votes DESC'),
		)
								);

Open in new window


Media has actAs = array('Containable');, and var $belongsTo = 'Artist';, and var $hasMany = 'Vote';.  Vote has var $belongsTo = 'Media';.

The SQL that is generated:
SELECT `Media`.`artist_id`, COUNT(*) AS votes, `Media`.`id` FROM `media` AS `Media` LEFT JOIN `artists` AS `Artist` ON (`Media`.`artist_id` = `Artist`.`id`) WHERE `Vote`.`created` BETWEEN '2010-11-08 00:00:00' AND '2010-11-14 23:59:59' AND `Artist`.`department_id` = 1 GROUP BY `Media`.`artist_id` ORDER BY `votes` DESC

Open in new window


Obviously I get the error "Unknown column 'Vote.created' in 'where clause'".

Can anyone help me out?

Thanks
 
willevansAsked:
Who is Participating?
 
Mark GilbertConnect With a Mentor Senior Performance EngineerCommented:
Should it not rather be something like:

$votes = $this->find('all',
	    array(
                'contain'=>array('Artist','Vote'),
		'fields'=>array('Media.artist_id','COUNT(*) AS votes'),
		'conditions'=>
		    array(
			"votes.created BETWEEN '" . $start . "' AND '" . $end . "'",
			'Artist.department_id' => $department
		    ),
		    'group'=>'Media.artist_id',
		    'order'=>array('votes DESC'),
		)

Open in new window


Reason being is you select the table votes but then call Vote.created so Vote doesn't exist.
0
 
ChaviaCommented:
As far as I know hasMany-Models aren't joined in find calls. They are fetched afterwards.

Try to fetch your records from the Vote Model, containing  Artist and Media models, group by Media.artist_id.
0
 
ChaviaCommented:
Like this:

$votes = $this->Vote->find('all',
	    array(
                'contain'=>array('Media'=> array('Artist') ),
		'fields'=>array('Media.artist_id','COUNT(*) AS votes'),
		'conditions'=>
		    array(
			"Vote.created BETWEEN '" . $start . "' AND '" . $end . "'",
			'Artist.department_id' => $department
		    ),
		    'group'=>'Media.artist_id',
		    'order'=>array('votes DESC'),
		)
								);

Open in new window

0
 
CWS (haripriya)Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.