CodeIgniter Forums
Can't figure this out... - Printable Version

+- CodeIgniter Forums (
+-- Forum: Archived Discussions (
+--- Forum: Archived Development & Programming (
+--- Thread: Can't figure this out... (/thread-13596.html)

Can't figure this out... - El Forum - 11-28-2008

Hey guy,

Got a problem with a query.

Got 2 MySQL tables setup like this:

article (ID, feedID, title, url, email, rating, view)


article_comment (ID, articleID, userID, date, comment)

I can order my articles by the number of times it has been emailed, its rating and its view count but I also want to be able to order them by the number of comments associated with said article.

Below is my attempt which isn't quite working.

Giving me the database error "Subquery returns more than 1 row."

There is one comment saved in the article_comment table.

$this->db->select('(SELECT count(*) FROM article_comment WHERE article_comment.articleID = (SELECT ID from article)) AS comments, ');
return $this->db->order_by('comments DESC')->limit(5)->get('article')->result_array();

I just need it to return the number of comments so I can "order_by(comments DESC)" and also have all the associated article information.

Any help would be much appreciated, thanks!

Can't figure this out... - El Forum - 11-28-2008

[eluser]GSV Sleeper Service[/eluser]
you'll have to join the two tables. something like this should work (no promises, this is off the top of my head, plus it's friday night, I'm stoned :/)
select a.*,count( as num_comments
from article a
left join article_comments c on = c.articleid
group by
order by num_comments
that group by is probably wrong, try it out and see how you get on.