Serendipity Forums

Code: Select all

SELECT
                    ep_sticky.value AS orderkey,

                    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
                     , e.body, e.extended

                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
                     LEFT OUTER JOIN serendipity_entryproperties  
ep_no_frontpage
                                                  ON (e.id =  
ep_no_frontpage.entryid AND ep_no_frontpage.property =  
'ep_no_frontpage')
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')
                    WHERE isdraft = 'false' AND e.timestamp <=  
1225306200 AND  (ep_access.property IS NULL OR ep_access.value =  
'public') AND  (ep_no_frontpage.property IS NULL OR  
ep_no_frontpage.value
!= 'true')
                     GROUP BY e.id
            ORDER BY orderkey DESC, timestamp DESC
                      LIMIT 10;

Code: Select all

SELECT count(distinct e.id)
                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
                     LEFT OUTER JOIN serendipity_entryproperties  
ep_no_frontpage
                                                  ON (e.id =  
ep_no_frontpage.entryid AND ep_no_frontpage.property =  
'ep_no_frontpage')
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')
                    WHERE isdraft = 'false' AND e.timestamp <=  
1225305900 AND  (ep_access.property IS NULL OR ep_access.value =  
'public') AND  (ep_no_frontpage.property IS NULL OR  
ep_no_frontpage.value
!= 'true');