[pLog-general] about getRecentArticles

[Oscar Renalias]

unfortunately mysql 4.1 is too much... I think we shouldn't target 
anything higher than mysql 3.x anyway, at least for the time being.

Is there any way to detect whether we're using mysql 4.x or 3.x via 
adodb or some php code? Perhaps we could add some extra condition: if 
mysql 4.1 is available, then use your suggestion otherwise use the 
current way. Or something like that :)

Oscar

On 5 Mar 2005, at 15:06, Su Baochen wrote:

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