Smart search for posts by tags (combinations), with optional date and sort parameters

1

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- fromDate: Posts created on, or after, Date (EG 2015-09-19)
-- Tag1: Tag name (lowercase) (required) "Enter a tag name (lowercase)"
-- Tag2: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag3: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag4: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- sortOrder: Sort Order (DESC or ASC) "Sort scending (oldest first), or descending (newest first)?"
DECLARE @fromDate   datetime      = ##fromDate:string?2005-01-01##;
DECLARE @Tag1       varchar(255)  = ##Tag1:string?javascript##;
DECLARE @Tag2       varchar(255)  = ##Tag2:string?NA##;
DECLARE @Tag3       varchar(255)  = ##Tag3:string?NA##;
DECLARE @Tag4       varchar(255)  = ##Tag4:string?NA##;
DECLARE @sortOrder  varchar(255)  = ##sortOrder:string?DESC##;

SELECT
            p.Id                AS [Post Link]
            , p.Score
            , p.Tags
            , p.CreationDate    AS [Created]
            , p.AnswerCount     AS [Num Answers]
            , p.ClosedDate      AS [Closed?]
FROM        Tags t
INNER JOIN  PostTags pt         ON t.ID = pt.TagId
INNER JOIN  Posts p             ON p.ID = pt.PostId

WHERE       p.CreationDate  >= @fromDate
AND         t.TagName       = @Tag1
AND         (
    @Tag2   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = p.Id
        AND         t2.TagName      = @Tag2
    )
)
AND         (
    @Tag3   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = p.Id
        AND         t2.TagName      = @Tag3
    )
)
AND         (
    @Tag4   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = p.Id
        AND         t2.TagName      = @Tag4
    )
)
ORDER BY    CASE   WHEN @sortOrder = 'ASC'   THEN p.CreationDate END ASC
            , CASE WHEN @sortOrder = 'DESC'  THEN p.CreationDate END DESC
            , p.Score DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results