4
\$\begingroup\$

I wrote a SEDE query to list all tag wikis with a body or an excerpt under a given number of characters. I've used this to find empty wikis or wikis with very little information. The tags can be sorted by post count to make choosing which to improve easier.

This is one of the first times I've written anything in SQL, I took ideas from other queries I found and threw them together. The conditional WHERE clause is my biggest concern. It looks really messy, but I'm not sure what the best way to achieve the same effect would be.

I'm aware that it doesn't actually get wikis with a length under the given number, but under or on. I wanted 0 to mean an empty wiki as that feel more intuitive than 1. I'm not sure about this design decision, what do you think?

-- MaxBodyLength: Max body length "Set to -1 to disable"
DECLARE @max_body_length INT = ##MaxBodyLength:INT?100##;
-- MaxExcerptLength: Max excerpt length "Set to -1 to disable"
DECLARE @max_excerpt_length INT = ##MaxExcerptLength:INT?-1##;

-- Max SE post length (see http://meta.stackexchange.com/a/176447/299387)
DECLARE @LEN_MAX INT = 30000;

BEGIN
  SELECT 
    t.TagName,
    t.Count AS [Post count],
    LEN(pExcerpt.body) AS [Excerpt length],
    LEN(pWiki.Body) AS [Wiki length]
  FROM Tags t
    LEFT JOIN Posts pExcerpt ON t.ExcerptPostId = pExcerpt.Id
    LEFT JOIN Posts pWiki ON t.WikiPostId = pWiki.Id
  WHERE
    LEN(pWiki.body) <= CASE
      WHEN @max_body_length >= 0 THEN
        @max_body_length
      ELSE
        @LEN_MAX
      END
    AND LEN(pExcerpt.body) <= CASE
      WHEN @max_excerpt_length >= 0 THEN
        @max_excerpt_length
      ELSE
        @LEN_MAX
      END
END
\$\endgroup\$
2
\$\begingroup\$

Overall, even for a non-first-timer with SQL, this is a good query. The consistency and styling makes it very clear you're not a programming newbie.

To answer your concern about "under" versus "under or on", and 0 meaning an empty wiki - I totally agree. For me, 0 meaning empty is the only logical interpretation - empty strings have length 0 in every language I've come across. The way you've named your parameters (MaxSomething) implies clearly that the user is inputting the highest number that will be included in the results - and that's what they get. This is all perfectly clear and sensible to me, no need to worry.

Minor niggles

You don't need the BEGIN and END wrappers here. They don't hurt, but they're only required for control-of-flow blocks (usually, IF and WHILE constructs).

You don't need @LEN_MAX in the current version of the query.

For a "report" type query like this, I'd always prefer to have default ordering set by the query (an ORDER BY clause) so the results are guaranteed consistent.

The WHERE clause

Looking at this half a year on from your original post, I can see you've refined the WHERE clause yourself. At the time of my writing the WHERE clause on the linked SEDE query is:

WHERE
(@max_body_length >= 0 AND LEN(pWiki.body) <= @max_body_length)
OR (@max_excerpt_length >= 0 AND LEN(pExcerpt.body) <= @max_excerpt_length)

This is definitely a much clearer way to express your desired logic than the CASE statement in the original post. For me, the reason it's clearer is that it maps quite easily to reading in plain English:

Show me rows where the max body length is 0 or more and the length of the Wiki Body is less than or equal to that, or where...

I reckon we could go further though :)

Different operators can improve readability

This is a matter of opinion only, but if your aim is to make query logic easily readable in plain English, sometimes inverting your operators can help that.

@max_body_length >= 0

"max body length is greater than or equal to 0, I mean 0 or more"

This is what I think when I read this code; I re-word it post-hoc to "0 or more" after understanding the meaning.

@max_body_length !< 0

"max_body_length is not less than 0"

For me this reads more clearly first-pass, and doesn't need a post-hoc re-scan. It'll depend a lot on the reader and how familiar they are with ! as a negation operator (some people prefer <>).

An even shorter alternative

Shorter isn't always better: for complex logic, spelling it out step by step (more verbosely) as you have can be much clearer, because of the "mapping to plain English" property.

However, we could go shorter here using nullif() to equate your -1 default values with SQL NULL i.e. "unknown"/"not applicable":

WHERE
LEN(pWiki.body) <= nullif(@max_body_length,-1)
or LEN(pExcerpt.body) <= nullif(@max_excerpt_length,-1)

This takes advantage of the all-consuming nature of NULL: if I ask "is the length of this (known) thing less than an unknown number", the only possible answer can be "I don't know" - another NULL.

Then, because SQL NULL doesn't evaluate to true, the overall WHERE clause as a whole works out just as you intend.

However, the catch here (you probably spotted it) is that this only works for -1 as a default, not any negative input. Since negative inputs apart from -1 are meaningless in this query, you could validate this at the start of the query:

IF (@max_body_length<-1) or (@max_excerpt_length<-1) 
    RAISERROR('Input parameters should be -1, 0 or positive integers',16,1)

Frankly this is probably overkill for a SEDE query but demonstrates the right approach for more generic application.

Ideally, one would actually make the default input for each parameter be NULL (rather than -1) and only permit 0 or positive input values: then you wouldn't need the nullif functions at all. However I'm not aware of a way to do that in SEDE.

| improve this answer | |
\$\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.