[pLog-general] about getRecentArticles

[Su Baochen]

>From mysql manual:

SELECT is used to retrieve rows selected from one or more tables.
Support for UNION statements and subqueries is available as of MySQL 4.0
and 4.1, respectively.

It seemed that from mysql 4.1, subquery is supported.

ÔÚ 2005-03-05ÁùµÄ 13:07 +0200£¬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
> >
> 
> _______________________________________________
> pLog-general mailing list
> pLog-general at devel.plogworld.net
> http://devel.plogworld.net/mailman/listinfo/plog-general
>