[pLog-general] about getRecentArticles

[Oscar Renalias]

I already knew about that but there is not much we can do...

In which version did mysql introduce nested queries?

Oscar

On 5 Mar 2005, at 12:58, Su Baochen wrote:

> 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
>
>
> _______________________________________________
> pLog-general mailing list
> pLog-general at devel.plogworld.net
> http://devel.plogworld.net/mailman/listinfo/plog-general
>