I have a list of articles. One of the columns shows an aggregate
field, which is the average rating of the article.
Problem: The list only shows articles that have at least one rating.
The SQL query generated by CakePHP is not considering articles that
have no rating at all.
The function in the controller i pretty simple:
function index() {
$this->Article->bindModel(array(
'hasOne' => array(
'average_rating' => array(
'className' => 'Rating',
'fields' => 'AVG(average_rating.value)
AS average_rating'
)
)
), false);
$this->set('articles', $this->paginate());
}
The SQL query that is executed is (pagination is used, with 5 items
per page):
SELECT `Article`.`id`, `Article`.`user_id`, `Article`.`title`,
`Article`.`text`, `Article`.`finished`, `Article`.`created`,
`Article`.`modified`, `User`.`id`, `User`.`first_name`,
`User`.`last_name`, `User`.`email`, `User`.`password`,
`User`.`activated`, `User`.`created`, `User`.`modified`, AVG
(`AvgRating`.`value`) AS AvgRating, AVG(`average_rating`.`value`) AS
average_rating FROM `nedito_articles` AS `Article` LEFT JOIN
`nedito_users` AS `User` ON (`Article`.`user_id` = `User`.`id`) LEFT
JOIN `nedito_ratings` AS `AvgRating` ON (`AvgRating`.`article_id` =
`Article`.`id`) LEFT JOIN `nedito_ratings` AS `average_rating` ON
(`average_rating`.`article_id` = `Article`.`id`) WHERE 1 = 1 ORDER BY
`Article`.`created` desc LIMIT 5
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
