• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Can't figure this out...

#1
[eluser]iainco[/eluser]
Hey guy,

Got a problem with a query.

Got 2 MySQL tables setup like this:

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

and

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.


Code:
$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!

#2
[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 :/)
Code:
select a.*,count(c.id) as num_comments
from article a
left join article_comments c on a.id = c.articleid
group by a.id
order by num_comments
that group by is probably wrong, try it out and see how you get on.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.