asked on

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

ASKER CERTIFIED SOLUTION

Mark Gilbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chavia

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.

Chavia

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

CWS (haripriya)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.