Parameters and Links

The queries we've looked at so far have been based on "high-scoring" questions—but we hard-coded a value for that (25). That value might be too limiting on some sites and too inclusive on others. Currently the only way to change that value is to edit the query. That's kind of a pain—but, fortunately, you don't have to do that. SEDE allows you to write queries with parameters so that the people who run the queries can easily adjust these values on the fly.

Note: the particular syntax used for parameters is specific to SEDE. Everything we've said so far about SQL is true for all flavors of SQL; this is different.

Let's modify the previous query to use a parameter for the score threshold. We'll explain the syntax below:

SELECT p.Title, p.Id, p.Score, a.Score AS "Accepted Score", 
u.DisplayName AS "Asker", au.DisplayName AS "Answerer"
  
FROM Posts p
JOIN Posts a ON p.AcceptedAnswerId = a.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN Users au ON a.OwnerUserId = au.Id
WHERE p.PostTypeId = 1
AND p.Score >= ##MinScore:int##
and p.AcceptedAnswerId IS NOT NULL
-- MinScore: Minimum question score to include.
ORDER BY p.Score DESC

Before you can run the query you need to fill in the score:

As shown in this query, you refer to a parameter by surrounding its name with doubled pound signs, such as ##MinScore##. Optionally you can specify the data type (int (a whole number), float (a number with a decimal value, like 2.5), or string): ##MinScore:int##. If you specify a type then SEDE will validate values against that type, so that if you're expecting a number for score and somebody types "unicorn" the query won't run. If you don't specify a type, your query may receive unexpected inputs.

A query might refer to a parameter more than once. You only need to specify the type once.

By default, the prompt on the query form (where users enter these values) is the name of the parameter, such as "MinScore". But this example doesn't do that; it has a fuller explanation. That's because the query included documentation for the parameter:

-- MinScore: Minimum question score to include.

This documentation can occur anywhere within the query.

Score is pretty obvious, but if your parameters have more semantics, or required formatting, you should say so in your documentation. Dates, for example, must be YYYY-MM-DD; if you're using dates as parameters, it's a good idea to tell your users how to format them.

This query returns post titles and IDs, but wouldn't it be better if you could just click on links to the actual posts? SEDE has some magic values that give you links. In this query, we've replaced the post title and ID and both users with magic links. The only changes are in the SELECT line:

SELECT p.Id AS [Post Link], p.Score, u.Id AS [User Link], 
a.Score AS "Accepted Score", au.Id AS [User Link]
  
FROM Posts p
JOIN Posts a ON p.AcceptedAnswerId = a.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN Users au ON a.OwnerUserId = au.Id
WHERE p.PostTypeId = 1
AND p.Score >= ##MinScore:int##
and p.AcceptedAnswerId IS NOT NULL
-- MinScore: Minimum question score to include.
ORDER BY p.Score DESC

p.Id AS [Post Link] and u.Id AS [User Link] take IDs and turn them into links. The AS [something Link] conversion only works when used with IDs. The link for a post automatically uses the post title and the link for a user automtically uses the display name. The SEDE query language supports the following magic columns:

• [Post Link]
• [User Link]
• [Comment Link]
• [Suggested Edit Link]

We had to make some compromises. We were previously using AS to label the two user columns as "Asker" and "Answerer". To get the links we had to change that. We re-ordered the columns, putting the asker right after the question score and the answerer right after the answer score: