Joomla 学习笔记

文章查询SQL, 多表左联查询。

注意SQL语句： CASE WHEN .. THNE .. ELSE .. END 的应用。

 

SELECT
 a.*,
 u.name AS author,
 u.usertype,
 cc.title AS category,
 s.title AS section,
 CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,
 CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,
 g.name AS groups,
 s.published AS sec_pub,
 cc.published AS cat_pub,
 s.access AS sec_access,
 cc.access AS cat_access
FROM #__content AS a
LEFT JOIN #__categories AS cc
 ON cc.id = a.catid
LEFT JOIN #__sections AS s
 ON s.id = cc.section AND s.scope = "content"
LEFT JOIN #__users AS u
 ON u.id = a.created_by
LEFT JOIN #__groups AS g
 ON a.access = g.id
 WHERE a.id = 5 AND ( ( a.created_by = 0 )
 OR ( a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2009-06-04 01:27:10' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2009-06-04 01:27:10' ) ) OR ( a.state = -1 ) )