Here’s a quick walkthrough on how to use Query Magics.
Use Query Magics to
- Link to another table
- Make urls clickable
- Make email addresses clickable
- Display a url as an image
- Display an number as a star rating
- Display a number as a progress bar
- Display a number as localized money
How To Use Query Magics
You can format the results of a SQL query by simply adding some text to the end of your column names.
For example, to format the the
url field as a link you’d do this:
select url as url__format__link from some_table
Here’s a quick example that displays clickable emails, links to another table, and renders a number as a star rating.
Available Query Magics
In all below descriptions the text in square parens is optional.
Format as a clickable link
Make URLs clickable
Format as a clickable email address
Similar to links, makes emails clickable - this will open a compose window in your default email client.
Format as a check/tick
Display a check or cross - 1 for check, 0 for cross. There are no further options
Format as an image (img)
Takes a URL and displays it as an image. You can change the width and height if you like.
columname__format__image[__width[__height]] --examples columname__format__image -- default columname__format__image__50 -- sets width to 50 columnname__format__image__50__100 -- sets width to 50, height 100
Format as money
Display a number in a specific currency, defaults to USD.
columname__format__money[__currencyCode] --examples columname__format__money -- USD (the default) columname__format__money__gbp -- British Pound columname__format__money__cop -- Colombian Peso
Format as a progress bar
Displays a number as a progress bar, by default this assumes a range of 0 - 100.
columnname__format__progress[__max] -- examples columname__format__progress -- default 0-100 columname__format__progress_10 -- the range is 0-10
Format as a star rating
Displays a number as a star rating (like you would find on a review site). The default assumes a 0-5 star rating.
columname__format__stars[__max] --examples columname__format__stars__10 -- 0-10 stars
Link to another table (goto)
This magic allows you to link to other tables based on the value in your result set, just like how foreign key links work in the table data view.
Your table link can optionally include a column filter.
columname__goto[__schema]__table[__column] -- syntax --examples columname__goto__users -- link to the primary key of the users table columname__goto__public__users -- link to the primary key of the users table in the public schema columname__goto__products__user_id -- link to the products table, filter by user_id
Because you can provide a filter, you can use this for more than foreign key links. For example you could link to all products purchased by a specific user.