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
                    INNER JOIN serendipity_entrytags AS entrytags ON (e.id = entrytags.entryid)  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 isdraft = 'false' AND e.timestamp <= 1308754800 AND entrytags.tag = 'serendipity' COLLATE utf8_unicode_ci  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
                                      
                                    )
                               )
                     GROUP BY e.id
                     
                     ORDER BY orderkey DESC, timestamp DESC
                      LIMIT 5

Code: Select all

ALTER TABLE `serendipity_entrytags` CONVERT TO CHARACTER SET utf8
COLLATE utf8_unicode_ci

Code: Select all

CREATE TABLE IF NOT EXISTS `serendipity_entrytags` (
  `entryid` int(10) NOT NULL,
  `tag` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`entryid`,`tag`),
  KEY `tagsentryindex` (`entryid`),
  KEY `tagsTagIndex` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Code: Select all

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `serendipity_entrytags`
--

CREATE TABLE IF NOT EXISTS `serendipity_entrytags` (
`entryid` int( 10 ) NOT NULL ,
`tag` varchar( 50 ) COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY ( `entryid` , `tag` ) ,
KEY `tagsentryindex` ( `entryid` ) ,
KEY `tagsTagIndex` ( `tag` ) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;


--
-- Daten für Tabelle `serendipity_entrytags`
--

INSERT INTO `serendipity_entrytags` (`entryid`, `tag`) VALUES
(1, 'Blog'),
(2, 'RealLife'),

BLABLABLA

Code: Select all

ALTER TABLE `serendipity_entrytags` ADD PRIMARY KEY ( `entryid` , `tag` );
CREATE INDEX tagsentryindex ON serendipity_entrytags (entryid);
CREATE INDEX tagsTagIndex ON serendipity_entrytags (tag);