Tutorial: Databases
This tutorial shows us how to find data from single column in a database tableFinding a Single Column
In mySQL, the similar command of finding columns is simply the SELECT command. Given a table of around 1 million records, a find option would be useful to filter out rows and observe a single column of data. As an example, say that we have a data table regarding furniture brought monthly at Ikea and we want to find what the most popular furniture consumers buy; using a find will single out a column so we can observe all the furniture people end up buying per month. Now, data analysts and manufacturers are able to see what pieces need to get shipped out more and ones that are not as popular can be shipped out less frequently.
For this tutorial, we will be learning about how to find a single column in two different ways: a method using a filter and a method without using a filter. Both methods will be almost identical, but we can demonstrate how to get a more defined search inside of Quorum Studio.
We will want to connect our database once again as well as create a QueryResult object. To do this, we would create a new object called QueryResult, that allows us to use helper actions to hold our search result from our Find() action. Let's call this QueryResult object "result."
use Libraries.Data.Database.Database
use Libraries.Data.Database.Find
use Libraries.Data.Database.QueryResult
use Libraries.Data.Database.QueryMetaData
Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")
QueryResult result
With our Database object, we will be using the actions Find(text table) / Find(text table, text filter). With our ResultQuery object, we will be using the Next(), HasNext(), and GetText(text columnName).
| Action / Object | Description | Usage |
|---|---|---|
| QueryResult objectName | The QueryResult class allows the user to iterate through a result given back from a database query. The result set that is returned from a query can be huge depending on the amount of data on the database so the default method of going through a result is iterate through it row by row rather than return all data at once. | QueryResult result |
| databaseObject:Find(text table, text filter) | This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table where the filter expression is true. | database:Find("festival_list","ticket_prices < 300") |
| databaseObject:Find(text table) | This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table. | database:Find("festival_list") |
| queryResultObj:HasNext() | This action returns true if there is another row in the query result to iterate to. | result:HasNext() |
| queryResultObj:Next() | This action moves the QueryResult to the next row. | result:Next() |
| queryResultObj:GetText(text/number column) | This action returns a column from the current row as text. | result:GetText("festtivalName") |
| queryResult:GetInteger(text/number column) | This action returns a column from the current row as an integer. | result:GetInteger("ticket_prices") |
Using no filter
When we call Find() using our database object, it essentially returns the results of the query, and in this case, our entire table. To actually have our query saved, we would assign the result using our QueryResult object we have made earlier. Let's call Find() using our database object and inside the parameter, let's pass in our "grocerylist" table and save it onto our 'result' object.
QueryResult result = database:Find("grocerylist")Using a filter
When we call Find() using our database object, it essentially returns the results of the query, and in this case, all the rows that are included in our filter. To actually have our query saved, we would assign the result using our QueryResult object we have made earlier. Let's call Find() using our database object and inside the parameters, let's pass in our "grocerylist" table as our first parameter and the expression "foodID = 1" in the second parameter and save it onto our 'result' object. What this is doing is filtering our entire table so it only saves a select amount of records into the query.
QueryResult result = database:Find("grocerylist", "FoodID = 1")Our query is loaded onto our object, but now, we want to iterate through the list and output its contents. To do this, we would use a loop as well as our QueryResult actions. In Quorum Studio, loops are controlled by a repeat; we will we using a repeat-while loop where the syntax is:
repeat while CONDITION
// stuff
end
In this case, our condition will be the result:HasNext() which tests if there is any content left inside our query; it returns true when there are items to be iterated through and false when there is no more content inside our query. Inside our repeat-while loop, we will call result:Next() which actually iterates through the query and then we will output the "FoodName" column using result:GetText(text columnName) and pass in "FoodName" as the parameter.
repeat while result:HasNext()
result:Next()
output result:GetText("FoodName")
end
Congrats! We finally know how to find data within a single column. To view the entire code, it will be displayed below.
use Libraries.Data.Database.Database
use Libraries.Data.Database.Find
use Libraries.Data.Database.QueryResult
use Libraries.Data.Database.QueryMetaData
Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")
QueryResult result = database:Find("grocerylist")
repeat while result:HasNext()
result:Next()
output result:GetText("FoodName")
end
Next Tutorial
In the next tutorial, we will discuss MultipleColumns, which describes how to find and summarize mutliple columns of data.