[eluser]Unknown[/eluser]
Thank you. This looks to be exactly what I need. However there seems to be a slight problem.
Code:
Unknown column 'COUNT(e.entry_id)' in 'field list'
SELECT `c`.*, `COUNT(e`.`entry_id)` AS total_comments FROM (`entries` e) JOIN `comments` c ON `c`.`entry_id` = `e`.`id` GROUP BY `c`.`entry_id`
As you could probably guess I'm slightly bad at SQL but I can't see anything wrong with this. The only thing that looks weird to me is how it's trying to select entry_id from the e(entries) table (being that this only exists in comments). Though I guess SELECT happens last so it wouldn't matter...
Anyone have any thoughts on this?
Thanks again.
ADDED: I've solved this problem. What I ended up doing was just writing a regular SQL statement to do the same thing that darhouse posted. I guess his version would've worked save for some minor detail that I could not identify due to my lack of experience with ActiveRecord.
Here is my final code:
Code:
function getEntries()
{
$query = $this->db->query('SELECT entries.*, COUNT(comments.entry_id) AS total_comments FROM entries LEFT JOIN comments ON comments.entry_id = entries.id GROUP BY entries.id ORDER by entries.id');
return $query;
}
Thanks again for the help though darkhouse. You put me on the right track and I guess the fact that I wasn't able to copy and paste the fix made it a much more effective learning experience.
