SQL is one of the most powerful tools in a developer’s toolkit — but there are plenty of clients, managers, and even some more technically-inclined engineers who have little to no idea how to write a SQL query that will give them the data they want in an efficient way. What if I told you there was a way to give those non-SQL users access to the same data-viewing capabilities without requiring any technical knowledge?
Meet Composable’s QueryView:
QueryView is a way for any proficient SQL user to set up a simple, easy-to-use interface for anyone (technically proficient or not!) to access, view, and slice underlying SQL data. Let’s dive into how setting up a QueryView works and what the various available options are.
Setting Up: The Basics
When you first click the QueryView button on the Composable top toolbar, this is what you’ll see.
For our demo, we’ll be taking a look at some data on the top 5000 movies & TV shows from IMDB (data found here) which we’ve imported into our SQL Server database. For now, let’s just set up a simple SELECT query to view the top 50 rows in a grid.
Note that the Output text area appears to just be a duplicate of our Template. Don’t worry, that will get more interesting later.
After attaching the relevant SQL connection on the Database tab, we are ready to take a look at what a user of this QueryView would see. Let’s click the Execute button!
Once we run the query, we can see a grid showing the columns that we selected, just as we’d expect from a SQL query. Now, this isn’t particularly useful; this is basically just a static grid of these 50 records of data, with no way to sort, filter, or gain any more detail.
Let’s head back to the Edit page and start adding some functionality!
Adding Some Levers
The first thing we’d like to do is add the ability for the user to order by any arbitrary column.
In some database scenarios this might not be a great idea; for example, if the underlying query is returning many, many records of data, including some unindexed columns, we might want to restrict the user’s ability to run a long, inefficient ORDER BY. In our case, though, we know we only have 5000 rows, so we’d rather give the user the extra freedom.
Adding the ability to order the query results is as simple as adding a template variable to our query; the QueryView will know behind the scenes to substitute in the relevant value to make this a syntactically valid (and useful!) query. We also add a default order, in this case simply the movie title.
Now if we flip back to our Execute page and run the query again, we see that our column headers are now clickable! Click one and sort by whichever header you desire; you can even shift-click to order by multiple columns.
Alright, that’s all well and good, but what if we know exactly what we’re looking for? We should have some way to filter the results. Let’s add a minimum IMDB score and a way to search based on movie title.
Let’s add the minimum IMDB score as a Literal Input to our QueryView; that is, we will set up the structure of the query ourselves, but we want to take a single literal value from the user. This requires us to add a default value for when the user has not entered anything; in our case, let’s just filter out anything below a score of 7 unless the user explicitly sets the threshold lower.
Now let’s add the title search as a Filter Input; a Filter Input intelligently applies itself to your query when the user has entered a value, and disappears when the user has not entered anything. In most cases, you’ll want to use Filter Inputs for most of your record filtering. Because the syntax is being generated for us behind the scenes, we need to let the QueryView know which column we’re setting this input up on.
Now let’s just add these inputs to our query template. As you can see, with the Literal Input we need to enter the relevant query syntax around our template variable, while with the Filter Input we just place the template variable into the query and the QueryView handles the rest. Check the Output text area to see an example replacement happening on the fly, and go ahead and click the validation checkbox in the top-right corner to ensure your query syntax is correct! Once all is well, let’s head back to the Execute page and test out our filters.
We can now see that both of our inputs are showing up on the left-hand side of our screen. The title filter is blank by default, as expected, while the minimum score input is showing a gray “7” to represent the default we entered. Let’s try running the query now, just to see what we get.
As expected, we’ve cleared out all the trash from these results; only (moderately, at least) well-reviewed movies & series are showing up now! Let’s go ahead and test out our inputs — say, anything with “Star” in the title that’s rated above an 8.
As expected, this gives us a pretty small set; but if you’re in the mood for a space movie (or television series), this is just the ticket!
Further Functionality
It’s clear how QueryView inputs provide the ability to create an interface that allows users to search even huge datasets for the records that they’re interested in; this alone makes it an extremely valuable tool. But what else can QueryView do?
As a start, you might not be so keen on the idea of always limiting your users to a fixed amount of records. With QueryView, you can easily implement either a user-adjustable limit or a fully-paged result, again simply by adding template variables.
Additionally, say you wanted to provide users with the ability to do a deep dive into a given record they find interesting. It’s simple to link to a child QueryView (created in just the same way as this one) that takes a single record’s columns as its input values; here we have a child QueryView that provides additional information about a given movie!
And QueryViews are not simply read-only; it’s easy to hook up Composable applications to perform actions based on any number of selected records. For example, say your user wanted to keep track of which of these highly-rated IMDB movies they’ve watched; it’s simple to add a link to a Composable application that will store the marked movies back in your database. Now just make sure you’re selecting that value with your query, and voila!
QueryView also supports exporting the query output records directly to a CSV or XLSX file, for the cases where you need to send data along to someone who might not have access to Composable.
Conclusion
Once you’ve set up your QueryView, make sure you save it and set the permissions on it so that all the relevant users & groups can access it; from there, you can send out the link to anyone who needs it (or let them discover it on their own)!
QueryView is certainly a versatile tool, and one that we’ve found useful for everyone, from complete SQL novices who want a powerful interface on data, to SQL experts who want to set up complex parent-child QueryView deep dive relationships. We’re expanding its functionality all the time, so make sure to continue checking the blog for further updates!
- Deploying Composable Resources via Powershell - December 1, 2022
- Composable APIs via Powershell - December 1, 2022
- Enabling Long Paths in Composable - April 6, 2022
