5

Please login or register to vote for this query.

(click on this box to dismiss)

Updated with feedback from CodeReview.SE: https://codereview.stackexchange.com/q/176680

Q&A about the site for administrators, end users, developers and designers for ExpressionEngine® CMS

SELECT DISTINCT
     PostTypes.Name AS [Post Type]
    ,Posts.Score AS [Post Score]
    ,Posts.Id AS [Post Link]
    ,Posts.LastActivityDate AS [Last Activity]
FROM Posts
    INNER JOIN PostTypes
        ON Posts.PostTypeId = PostTypes.Id
    INNER JOIN Users
        ON Posts.OwnerUserId = Users.Id
    INNER JOIN PostHistory
        ON Posts.Id = PostHistory.PostId
WHERE
    Posts.Score > 0 -- Low quality posts are probably not worth salvaging
    AND Posts.ClosedDate IS NULL
    AND Users.Reputation < 4000 -- Posts by trusted users are exempt from editing (that is the theory anyway)
    AND (
            (
                Posts.PostTypeId IN (1, 3)
                AND Posts.Id <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = PostHistory.PostId
                        AND PostHistoryTypeId IN (14, 15)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            )
            OR -- Locked posts can't be edited
            (
                Posts.PostTypeId IN (2, 3)
                AND Posts.ParentId <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = Posts.ParentId
                        AND PostHistoryTypeId IN (14, 15)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            )
            OR Posts.PostTypeId IN (4, 5)
        )
    AND (
            (
                Posts.Title NOT LIKE '%?'
                AND (
                       LOWER(Posts.Title) LIKE 'is %'
                    OR LOWER(Posts.Title) LIKE 'are %'
                    OR LOWER(Posts.Title) LIKE 'do[en ]%'
                    OR LOWER(Posts.Title) LIKE '%wh[aeoy]%'
                    OR LOWER(Posts.Title) LIKE '%how%'
                )
            )
            OR
            (
                   Posts.Title LIKE '[a-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '%[A-Za-z]([A-Za-rt-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '%[A-Za-rt-z])[A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '%[A-Za-z],[A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '% i[ '']%'
            )
            OR
            (
                   Posts.Body LIKE '[a-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '%[A-Za-z]([A-Za-rt-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '%[A-Za-rt-z])[A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '%[A-Za-z],[A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '% i[ '']%'
            )
        )
ORDER BY Posts.LastActivityDate DESC

view current version

edited oct 22 17

created nov 6 16 Gao