[pLog-general] about getRecentArticles

[Su Baochen]

hi oscar,

Function getRecentArticles of summarystatics.class.php may have
problems?

For example, we can get 15 recent articles, but these 15 articles only
belongs to one blog user, so the result of getRecentArticles is only one
article.

My friend(baojg) have write a sql to achive:
1 one article per blog
2 order by date desc
3 return 15($maxPosts) articles

The SQL is:

  SELECT id,
         user_id,
         blog_id,
         status,
         properties,
         num_reads,
         slug,
         date,
         topic,
         text
   FROM
         (SELECT a.id,
                 a.user_id,
                 a.blog_id,
                 a.status,
                 a.properties,
                 a.num_reads,
                 a.slug,
                 a.date,
                 t.topic,
                 t.text
           FROM plog_articles a,
                plog_articles_categories c,
                plog_article_categories_link l,
plog_articles_text t
           WHERE t.article_id = a.id
                 AND TO_DAYS(NOW()) - TO_DAYS(a.date) < 7
                 AND l.article_id = a.id
                 AND l.category_id = c.id
                 AND c.in_main_page = 1
                 AND a.status = 1 AND t.topic NOT LIKE 'Congratulations'
ORDER BY a.date DESC) AS sorted_articles
    GROUP BY blog_id ORDER BY date DESC  LIMIT 15

I'd like to explain it:
The nested SELECT clause get all useful information order by date, then
group by blog_id can get only one article per blog.

This sql seems not effective, but works on my machine, and get 15
articles within 0.07s from 501 articles. You can test it on your machine
with bigger database.

I have modified my own summarystatics.class.php, but not commited,
please double check above sql.... hope helpful.

Best regards,

Su Baochen