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 for Drupal developers and administrators

SELECT PostTypes.Name AS [Post Type], Posts.Id AS [Post Link], Posts.LastActivityDate AS [Last Edit Date]
FROM Posts
INNER JOIN PostTypes
ON Posts.PostTypeId = PostTypes.Id
INNER JOIN Users
ON Posts.OwnerUserId = Users.Id
WHERE Posts.PostTypeId NOT IN (6, 7) AND
Posts.Score > -1 AND
Posts.ClosedDate IS NULL AND
Users.Reputation < 4000 AND
(
    Posts.Title LIKE '[abcdefghijklmnopqrstuvwxyz]%' OR
    (
        Posts.Title NOT LIKE '%?' AND
        (
            LOWER(Posts.Title) LIKE 'is %' OR
            LOWER(Posts.Title) LIKE 'are %' OR
            LOWER(Posts.Title) LIKE 'do%' OR
            LOWER(Posts.Title) LIKE '%wh[aeoy]%' OR
            LOWER(Posts.Title) LIKE '%how%'
        )
    ) OR
    Posts.Body LIKE '[abcdefghijklmnopqrstuvwxyz]%' OR
    Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz]%' OR
    Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz])[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%' OR
    Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz],[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%'
)
ORDER BY Posts.PostTypeId ASC, Posts.LastActivityDate DESC

view current version

edited nov 30 16

created nov 6 16 Gao