5
\$\begingroup\$

I've put together another simple query that allows you to search post/comments with specific keywords. At the moment, only post bodies are checked.

DECLARE @search_keywords NVARCHAR(MAX) = ##SearchKeywords:string##;
DECLARE @search_type_id NVARCHAR(MAX) = ##SearchTypeId:string##;

IF (LOWER(@search_type_id) = 'post')
BEGIN
  SELECT
      Id AS [Post Link]
    , OwnerUserId AS [User Link]
    , Score
    , Tags
  FROM Posts WHERE
    LOWER(Body) LIKE LOWER(
      CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
    )
  ORDER BY Score DESC;
END

ELSE IF (LOWER(@search_type_id) = 'comment')
BEGIN
  SELECT
      Id AS [Comment Link]
    , UserId AS [User Link]
    , Score
    , Text
  FROM Comments WHERE
    LOWER(Text) LIKE LOWER(
      CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
    )
  ORDER BY Score DESC;
END

ELSE
BEGIN
  PRINT 'SearchTypeId must either be "comment" or "post".';
END

Here's some sample input. Single quotes, '', are not needed for the input.

SearchKeywords: :)
SearchTypeId:   comment
\$\endgroup\$
4
\$\begingroup\$

Let's start with your input parameters, since that is SEDE-specific. Did you know you can add parameter hints to make the experience easier for users? See the SEDE Help Page. So we could do something like this:

-- SearchKeywords: Search for word(s) "No need for quote marks"
DECLARE @search_keywords NVARCHAR(MAX) = ##SearchKeywords:string##;
-- SearchTypeId: post / comment "No need for quote marks"
DECLARE @search_type_id NVARCHAR(MAX) = ##SearchTypeId:string##;##;

That way it looks like this instead, including the little tool tip:

sede


That said, there are some things we could improve. As it stands, when you search for a post there are a few deficiencies in the result set:

  • No indication whether it is a Question, Answer, or Other type of post.

This is easy to improve. Let's declare two new variables. We will reuse them for your comments query, so let's declare them at the top.

DECLARE @question INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @answer INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');

Then we can simply use a CASE statement to add in the type of post, like this:

, CASE 
    WHEN PostTypeId = @Question THEN 'Question'
    WHEN PostTypeId = @Answer THEN 'Answer'
    ELSE 'Other' 
    END AS [Post Type]

This will show you another deficiency:

  • Non-Question posts are missing tags!

The reason, as I'm sure you know, is that only Question posts have Tags. What we can do then is use a self-referencing join to get the Tags from the parent questions, again using a CASE statement, like this:

, CASE
    WHEN PostTypeId = @Question THEN Tags
    WHEN PostTypeId = @Answer THEN (SELECT parent.Tags FROM Posts AS parent INNER JOIN Posts AS child ON child.ParentId = Parent.Id WHERE child.Id = Posts.Id)
    ELSE NULL END AS [Tags]

You could break down that one-liner subquery with line breaks, if it makes it easier to read for you.

So we're in pretty good shape for the post search now! Let's look at the comment query.


I think it would be good information to know what kind of post the comment was made on, so why don't we add a join to the Posts table, and get that information from there, using again a CASE statement:

SELECT TOP 10
    Comments.Id AS [Comment Link]
  , Comments.UserId AS [User Link]
  , CASE 
      WHEN Posts.PostTypeId = @Question THEN 'Question'
      WHEN Posts.PostTypeId = @Answer THEN 'Answer'
      ELSE 'Other' 
      END AS [Post Type]
  , Comments.Score
  , Comments.Text

FROM Comments
INNER JOIN Posts ON Posts.Id = Comments.PostId

Note that I had to reference the tables for each column otherwise it would be an ambiguous join and give you an error.

If you wanted to you could also use the same method as above to get the post tags, although that may not be desired with comments. Also, in both cases, perhaps consider adding the CreationDate field which is useful for sorting.


With everything put together, I came up with this query:

-- SearchKeywords: Search for word(s) "No need for quote marks"
DECLARE @search_keywords NVARCHAR(MAX) = ##SearchKeywords:string##;
-- SearchTypeId: post / comment "No need for quote marks"
DECLARE @search_type_id NVARCHAR(MAX) = ##SearchTypeId:string##;

DECLARE @question INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @answer INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');

IF (LOWER(@search_type_id) = 'post')
BEGIN

  SELECT
      Id AS [Post Link]
    , OwnerUserId AS [User Link]
    , Score
    , CASE 
        WHEN PostTypeId = @Question THEN 'Question'
        WHEN PostTypeId = @Answer THEN 'Answer'
        ELSE 'Other' 
        END AS [Post Type]
    , CASE
        WHEN PostTypeId = @Question THEN Tags
        WHEN PostTypeId = @Answer THEN (SELECT parent.Tags FROM Posts AS parent INNER JOIN Posts AS child ON child.ParentId = Parent.Id WHERE child.Id = Posts.Id)
        ELSE NULL END AS [Tags]
    , CreationDate
  FROM Posts 
  WHERE
    LOWER(Body) LIKE LOWER(
      CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
    )
  ORDER BY Score DESC;
END

ELSE IF (LOWER(@search_type_id) = 'comment')
BEGIN
  SELECT
      Comments.Id AS [Comment Link]
    , Comments.UserId AS [User Link]
    , CASE 
        WHEN Posts.PostTypeId = @Question THEN 'Question'
        WHEN Posts.PostTypeId = @Answer THEN 'Answer'
        ELSE 'Other' 
        END AS [Post Type]
    , Comments.Score
    , Comments.Text
    , Comments.CreationDate AS [Comment Creation Date]
    , Posts.CreationDate AS [Post Creation Date]
  FROM Comments
    INNER JOIN Posts ON Posts.Id = Comments.PostId
  WHERE
    LOWER(Text) LIKE LOWER(
      CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
    )
  ORDER BY Score DESC;
END

ELSE
BEGIN
  PRINT 'SearchTypeId must either be "comment" or "post".';
END
| improve this answer | |
\$\endgroup\$
  • 3
    \$\begingroup\$ Well look at you, all tips and tricks about SEDE... ++ mate \$\endgroup\$ – RubberDuck Jul 14 '15 at 23:00

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.