Code: Select all
SELECT DISTINCT ep_sticky.value AS orderkey,
ep_cache_extended.value AS ep_cache_extended,
ep_cache_body.value AS ep_cache_body,
e.id,
e.title,
e.timestamp,
e.comments,
e.exflag,
e.authorid,
e.trackbacks,
e.isdraft,
e.allow_comments,
e.last_modified,
a.realname AS author,
a.username AS loginname,
a.email
FROM
serendipity_entries AS e
LEFT JOIN serendipity_authors a
ON e.authorid = a.authorid
LEFT JOIN serendipity_entrycat ec
ON e.id = ec.entryid
LEFT JOIN serendipity_category c
ON ec.categoryid = c.categoryid
INNER JOIN serendipity_entrytags AS entrytags ON e.id = entrytags.entryid LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended
ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended')
LEFT OUTER JOIN serendipity_entryproperties ep_cache_body
ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body')
LEFT OUTER JOIN serendipity_entryproperties ep_access
ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access')
LEFT JOIN serendipity_entryproperties ep_sticky
ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') LEFT JOIN serendipity_authorgroups AS acl_a
ON acl_a.authorid = 0
LEFT JOIN serendipity_access AS acl_acc
ON ( acl_acc.artifact_mode = 'read'
AND acl_acc.artifact_type = 'category'
AND acl_acc.artifact_id = c.categoryid
)
WHERE e.timestamp >= 1238194800 AND e.timestamp <= 1238277599 AND isdraft = 'false' AND e.timestamp <= 1269812100 AND (entrytags.tag IN ('blog','categories','46-Kino')) AND (ep_access.property IS NULL OR ep_access.value = 'public') AND (
c.categoryid IS NULL
OR ( acl_acc.groupid = 0)
OR ( acl_acc.artifact_id IS NULL
)
)
HAVING count(entrytags.tag) = 3
ORDER BY orderkey ASC, timestamp DESC
LIMIT 3