Tutorial: Databases

Finding Multiple Columns

While finding a single column is useful to a general trend of data, it would be tedious looking at entries column by column. In Quorum, we can use another method of finding that works just like SQL's SELECT command, and can find multiple columns at the same time. For this tutorial, we will be learning about how to find multiple columns and use our Find object to narrow down a single record.

We will want to connect our database once again as well as create a Find object. To do this, we would create a new object called QueryResult, that allows us to use helper actions to filter out data. Let's call this Find object "query."

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")

    Find query

With our Find object, we will be using the actions: SetTable(text tableName), AddColumn(textColumn name), AddColumnFromTable(text columnName, text tableName), AddSum(text columnName), AddCount(text columnName), and AddMaximum(text columnName). With our Database object we will also be calling the Find(Find objectName) to perform the action onto our database.

Now, we want to load in the database table we have been working on, "grocerylist" into the object by passing the string, "grocerylist" into the SetTable() action. Currently it has connected our database with our object, but we need to add column targets which our Find object will be a container holding our data. We will be wanting to add all our columns into "query" such as FoodName, FoodID, FoodCalories, FoodQuantity, and FoodCost but we will be using two different actions to add a column. While AddColumn() is our default action to add a column into our object, we also have AddColumnFromTable() that takes in our column name and table name that overall provides more clarity in what table we are grabbing from in the case we will be using joins. We will be discussing more about joins in a later section. Both functions get the job done, therefore it is a matter of preference and context of what we are working on using our database.

query:SetTable("grocerylist")
    query:AddColumn("FoodName")
    query:AddColumn("FoodID")
    query:AddColumn("FoodCalories")
    query:AddColumn("FoodQuantity")
    query:AddColumnFromTable("FoodCost", "grocerylist")

Within our Find object, we have access to more "filtering" actions that allow us as programmers to make summaries and various conclusions about our data. What this means is that we'll be able to find entries such as the maximum or minimum of a column, count the total number of entries from a whole column, or even calculate the sum of a column. We'll be grabbing some of these central tendencies from our table and work on the actions: AddSum(), AddCount(), and AddMaximum(). Regarding all of these functions, they take a text parameter which would be the column name and find the value we would be looking for; it is important to note that to use these numerical based actions, we must use columns that contain numbers such as integers, decimals, doubles, etc. and they are not mixed or are string-based columns.

Using AddSum(), we want to know exactly how many items we bought in our grocerylist, so let us pass in the column "FoodQuantity" to sum up all the items. Similar to AddSum() we will also be using AddCount(), we want to know how many unique items we bought in our grocerylist, so let's pass in the column "FoodName" to count all the items inside our data table. Finally, we want to know how much was the most calorie dense item in our grocerylist, so let's pass in the column "FoodCalories" into our action, AddMaximum(). Now that we are satisfied with our finds let's actually get these results saved using our QueryResult object.

First, we will want to create our QueryResult which we can name it "results." This object can hold our queries from our database similar to how we found data in a single column. We will be calling the Find() using our database object, but instead of inserting a column name as the parameter, we will be passing in our Find object, "query" which allows us to have more specific query results. We will be saving all of this information into the "results" object.

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'll we using a repeat-while loop where the syntax is:

repeat while CONDITION
// stuff
end

In this case, our condition will be results: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 "FoodID" column using results:GetText(text columnName) and pass in "FoodID" as the parameter. We will be continuing to add the rest of the columns using our GetText()/GetIntger() actions with "results." Note: When outputting our columns, we want to make sure we are using the correct actions: GetText() to output text-based columns and GetInteger() to output any numerical columns.

 repeat while results:HasNext()
     results:Next()
     output "foodID: " + results:GetNumber("FoodID")
     output "foodName: " + results:GetText("FoodName")
     output "foodCost: " + results:GetInteger("FoodCost")
     output "foodCalories: " + results:GetNumber("FoodCalories")
     output "foodQuantity: " + results:GetNumber("FoodQuantity")
    end

Congrats! We finally know how to find data within multiple columns. 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")

    Find query
    query:SetTable("grocerylist")

    query:AddColumn("FoodName")
    query:AddColumn("FoodID")
    query:AddColumn("FoodCalories")
    query:AddColumn("FoodQuantity")
    query:AddColumnFromTable("FoodCost", "grocerylist")

    query:AddSum("FoodQuantity")

    query:AddCount("FoodID")

    query:AddMaximum("FoodCalories")

    QueryResult results = database:Find(query)

    repeat while results:HasNext()
     results:Next()
     output "foodID: " + results:GetNumber("FoodID")
     output "foodName: " + results:GetText("FoodName")
     output "foodCost: " + results:GetInteger("FoodCost")
     output "foodCalories: " + results:GetNumber("FoodCalories")
     output "foodQuantity: " + results:GetNumber("FoodQuantity")
    end

Next Tutorial

In the next tutorial, we will discuss dataframe from database, which describes how to convert a database table into a dataframe.