6
\$\begingroup\$

I've refactored my previous question finder, and added multi-tag support, and support for searching for keywords in the question titles. Essentially, what it does is find questions that could be answered based on the following parameters.

-- @MinimumQuestionVotes - The minimum amount of votes on a question.
-- @MaximumQuestionAnswers - The maximum amount of answers to a question.
-- @QuestionTags - The question's tags. Blank string is a wildcard.
-- @TitleContains - Words in the title. Blank string is a wildcard.

If you feel that any parameters aren't needed, or can be improved, just mention it. I'm open to any suggestions. Anyways, here's the code, and here's the SEDE query link.

-- User parameters for finding questions. Here is
-- a brief description of what each parameter does.

  -- @MinimumQuestionVotes - The minimum amount of votes on a question.
  -- @MaximumQuestionAnswers - The maximum amount of answers to a question.
  -- @QuestionTags - The question's tags. Blank string is a wildcard.
  -- @TitleContains - Words in the title. Blank string is a wildcard.
DECLARE @MinimumQuestionVotes INT = ##MinimumQuestionVotes:int?0##;
DECLARE @MaximumQuestionAnswers INT = ##MaximumQuestionAnswers:int?0##;
DECLARE @QuestionTags NVARCHAR(150) = ##QuestionTags##;
DECLARE @TitleContains NVARCHAR(250) = ##TitleContains##;


-- Final results are SELECTed based on the following
-- conditions.

  -- ClosedDate  IS EQUAL TO              null
  -- PostTypeId  IS EQUAL TO              question
  -- Score       GREATER THAN OR EQUAL TO @MinimimQuestionVotes
  -- AnswerCount LESS THAN OR EQUAL TO    @MaximumQuestionAnswers
  -- Tags        CONTAIN                  @QuestionTags
SELECT
    [Posts].Id AS [Post Link]
  , [Posts].OwnerUserId AS [User Link]
  , [Posts].Score
  , [Posts].Tags
  , [Posts].ViewCount
  , [Posts].AnswerCount
  FROM Posts WHERE
        [Posts].ClosedDate IS NULL
    AND [Posts].PostTypeId = 1
    AND [Posts].Score >= @MinimumQuestionVotes
    AND [Posts].AnswerCount <= @MaximumQuestionAnswers
    AND [Posts].Tags LIKE '%' + REPLACE(@QuestionTags, ' ', '%%') + '%'
    AND [Posts].Title LIKE '%' + REPLACE(@TitleContains, ' ', '%%') + '%'
  ORDER BY Score DESC;

Finally, here's some example input for the parameters. Do note, in the @QuestionTags and @TitleContains fields, you need to put single quotes, '', and space-separate each tag/word.

@MinimumQuestionVotes:   1
@MaximumQuestionAnswers: 0
@QuestionTags:           'python beginner'
@TitleContains:          'web'
\$\endgroup\$
  • \$\begingroup\$ your query doesn't work with empty @QuestionTags or @TitleContains unless you put single quotes in. \$\endgroup\$ – Malachi Jun 19 '15 at 16:54
  • \$\begingroup\$ @Lyle'sMug That's one of the quirks of the SEDE. You have to put in raw variable values. \$\endgroup\$ – Ethan Bierlein Jun 19 '15 at 16:57
4
\$\begingroup\$

There are some usability issues with @QuestionTags:

  • The ordering of tags matters. I think in the typical use case that's not the desired behavior
  • The input format is hackish (the implementation too) and unintuitive

I have an alternative suggestion that has different downsides:

  • Use multiple tag input fields, for example @Tag1, @Tag2, and so on
  • The advantage is that you could enter tags in any order, and without funky input rules
  • The disadvantage is that the number of tags you can enter will be limited to the number of fields you provide
  • Note that this approach will also open the possibility to choose the condition for for matching tags: they can be AND-ed or OR-ed. Your choice, or give another input to users to let them decide

The numbered fields are not exactly pretty, but I think the query will be easier to use this way. (Can be subjective.)

You could do similarly for matching keywords in the title.

Note that I dropped "Question" from the parameter names, to simplify to @TagN. On the network only questions can have tags, so this is obvious.

Other notes:

  • The semicolons at the end of variable declarations are unnecessary
  • It would be nice to make title optional
  • The repeated [Posts]. prefix of fields are not necessary, and a bit of a noise

Example implementation with some of the improvements explained above:

DECLARE @MinimumQuestionVotes INT = ##MinimumQuestionVotes:int?0##
DECLARE @MaximumQuestionAnswers INT = ##MaximumQuestionAnswers:int?0##
DECLARE @Tag1 NVARCHAR(50) = RTRIM(LTRIM(##Tag1:string? ##))
DECLARE @Tag2 NVARCHAR(50) = RTRIM(LTRIM(##Tag2:string? ##))
DECLARE @Tag3 NVARCHAR(50) = RTRIM(LTRIM(##Tag3:string? ##))
DECLARE @TitleContains NVARCHAR(250) = RTRIM(LTRIM(##TitleContains:string? ##))

SELECT
    Id AS [Post Link]
  , OwnerUserId AS [User Link]
  , Score
  , Tags
  , ViewCount
  , AnswerCount
  FROM Posts WHERE
        ClosedDate IS NULL
    AND PostTypeId = 1
    AND Score >= @MinimumQuestionVotes
    AND AnswerCount <= @MaximumQuestionAnswers
    AND (@Tag1 = '' OR Tags LIKE '%' + @Tag1 + '%')
    AND (@Tag2 = '' OR Tags LIKE '%' + @Tag2 + '%')
    AND (@Tag3 = '' OR Tags LIKE '%' + @Tag3 + '%')
    AND (@TitleContains = '' OR Title LIKE '%' + REPLACE(@TitleContains, ' ', '%%') + '%')
  ORDER BY Score DESC;
\$\endgroup\$
3
\$\begingroup\$

Your code's looking really good!

I can only really see a few things that could be improved.


SELECT  
  [Posts].Id AS [Post Link]
, [Posts].OwnerUserId AS [User Link]

into:

SELECT
  [Posts].Id [Post Link]
, [Posts].OwnerUserId [User Link]

AS is optional, see this answer for some more elaboration on why it's unnecessary and cluttery.


ORDER BY Score DESC;

I would add another ORDER to that, and make it ORDER BY Score DESC, ViewCount DESC;


When working from/with a single table (Posts), you can either prefix [Posts]. to the beginning of your column requests, or just omit it entirely.

However, in the ORDER, you change the continous use of this and opt for vanilla Score, rather than [Posts].Score.

It's best if you choose one method (I'd suggest not attaching [Posts].), and stick with it.


Other than that, your code looks really neat and nice. Good work!

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.