Libraries.Data.Database.Find Documentation

The Find query represents the SELECT command of the SQL (Structured Query Language) language. This query allows for accessing data in the database. This query also allows operatations to be performed on the data before returning it, such as Sum, Maximum, or Count.

Example Code

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find

Database database
database:Connect("localhost", "myDatabase", "user", "SuperSecret456")

Find query
query:SetTable("employees")
query:AddColumn("lastName")
query:AddColumn("firstName")

QueryResult results = database:Find(query)

Inherits from: Libraries.Language.Object, Libraries.Data.Database.Query, Libraries.Data.Database.Support.RemappableName

Summary

Actions Summary Table

ActionsDescription
AddColumn(text name)This action adds a column target.
AddColumnFromTable(text name, text table)This action adds a column target.
AddColumnNameRemap(text table, text column, text newName)This action allows you to give a different name to database column.
AddColumnNameRemap(text column, text newName)This action allows you to give a different name to database column.
AddColumnWithNameRemap(text table, text column, text newName)This action allows you to give a different name to database column.
AddColumnWithNameRemap(text column, text newName)This action allows you to give a different name to database column.
AddCount(text name)Add a column where, instead of displaying it, we count the number of rows that match with the specified criteria.
AddCount(text name, boolean distinct)Add a column where, instead of displaying it, we count the number of rows that match with the specified criteria.
AddCrossJoin(text table)This action will add an Cross Join to the query.
AddGroup(text name)This action adds a column to group rows by.
AddGroup(text name, text table)This action adds a column to group rows by.
AddGroupFromTable(text name, text table)This action adds a column to group rows by.
AddInnerJoin(text table, text condition)This action will add an Inner Join to the query.
AddLeftJoin(text table, text condition)This action will add an Left Join to the query.
AddMaximum(text name)Add a column where, instead of displaying it, we find the maximum value.
AddMean(text name, boolean distinct)Add a column where, instead of displaying it, we average its values.
AddMean(text name)Add a column where, instead of displaying it, we average its values.
AddMinimum(text name)Add a column where, instead of displaying it, we find the minimum value.
AddNameRemap(text newName, Libraries.Data.Database.Support.RemappableName original)Adds objects that have been remapped to single table for reference in actions called when building the query to text.
AddPreparedParameter(Libraries.Data.Database.Support.Parameter param)At the plugin level prepared statements are used to add a layer of security from injection attacks.
AddRightJoin(text table, text condition)This action will add an Right Join to the query.
AddSort(text name, text table)Adds a sort command, which is ascending by default, but can be set to descending.
AddSort(text name, boolean ascending)Adds a sort command, which is ascending by default, but can be set to descending.
AddSort(text name)Adds a sort command, which is ascending by default.
AddSortFromTable(text name, text table, boolean ascending)Adds a sort command, which is ascending by default, but can be set to descending.
AddSubquery(text name, Libraries.Data.Database.Find subquery)Adds a subquery to this query.
AddSum(text name, boolean distinct)Add a column where, instead of displaying it, we sum its values.
AddSum(text name)Add a column where, instead of displaying it, we sum its values.
AddTableNameRemap(text table, text newName)This adds a name that can be used in the place of a table.
AddTableReference(text table)Internal.
Compare(Libraries.Language.Object object)This action compares two object hash codes and returns an integer.
ConvertToStructuredQueryLanguage()This action returns the query as text in Structured Query Language (SQL).
EmptyColumns()This action removes all column targets.
EmptyPreparedParameters()Clears the prepared parameter list.
EmptySorts()This action removes all sorts.
EmptyTableReferences()Empties the table references hash table.
Equals(Libraries.Language.Object object)This action determines if two objects are equal based on their hash code values.
Filter(text value)Some queries make use of the filter which is an expression checked against the values of the columns in the rows of a database table.
GetColumnListSize()This action returns how many columns have been added to this Query
GetColumnType(text column)INTENDED FOR INTERNAL USE ONLY only works if the query has already been built since it needs metadata
GetColumnType(integer position)INTENDED FOR INTERNAL USE ONLY only works if the query has already been built since it needs metadata
GetDatabaseMetaData()Returns the meta data object set for this query
GetHashCode()This action gets the hash code for an object.
GetNameRemaps()Grabs the table containing all aliases that have been set in this query
GetPreparedParameters()Used at the plugin level to get all parameters that need to be set in the prepared statement
GetRawFilter()Returns the filter as given by the user.
GetRemappedName()Returns the alias
GetTable()Returns the table this query is meant to be ran against
GetTableReferences()Internal.
GetTranslatedFilter()Returns the filter as translated by the quorum compiler.
HasColumn(text name)This action checks if a column is in the list of targets.
IsDistinct()Returns true if the column targets will only return distinct values
RemoveColumn(text name)This action removes a specific column from the list of targets.
SetDatabaseMetaData(Libraries.Data.Database.DatabaseMetaData meta)Sets the DatabaseMetaData for this query.
SetDistinct(boolean distinct)This actions sets whether or not the column targets are distinct.
SetRemappedName(text remappedName)Sets the alias
SetTable(text table)This action sets the table of the query.
SetTable(text table, text newName)This action sets the table of the query.
SetTableToSubquery(Libraries.Data.Database.Find derivedTable, text newName)This action sets the table of a Find query to the result of another Find query.
SetTableWithName(text table, text newName)This action sets the table of the query.
ToText()By default convert to structured query language.

Actions Documentation

AddColumn(text name)

This action adds a column target. The column targets will be the columns in a database table to fetch data from.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

Parameters

AddColumnFromTable(text name, text table)

This action adds a column target. The column targets will be the columns in a database table to fetch data from. The exact table the column is from can be added in the case the name might be ambiguous when using joins.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumnFromTable("city", "offices")

Parameters

AddColumnNameRemap(text table, text column, text newName)

This action allows you to give a different name to database column. Using this action does not mean that the column will be the column in the QueryResult. To use this as a column for the result you can use the name given here in AddColumn. Adding a name remap will also allow you to use the name in an expression such as the expression for Filter.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumnNameRemap("offices", "city", "c")
        query:AddColumn("c")

Parameters

AddColumnNameRemap(text column, text newName)

This action allows you to give a different name to database column. Using this action does not mean that the column will be the column in the QueryResult. To use this as a column for the result you can use the name given here in AddColumn. Adding a name remap will also allow you to use the name in an expression such as the expression for Filter. If the table is not specifed the column is assumed to come from the table set in SetTable.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumnNameRemap("city", "c")
        query:AddColumn("c")

Parameters

AddColumnWithNameRemap(text table, text column, text newName)

This action allows you to give a different name to database column. This action differs from AddColumnNameRemap in that it will both add the remap and add the column to the column list for the result. Adding a name remap will also allow you to use the name in an expression such as the expression for Filter. If the table is not specifed the column is assumed to come from the table set in SetTable.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumnWithNameRemap("offices", "city", "c")

Parameters

AddColumnWithNameRemap(text column, text newName)

This action allows you to give a different name to database column. This action differs from AddColumnNameRemap in that it will both add the remap and add the column to the column list for the result. Adding a name remap will also allow you to use the name in an expression such as the expression for Filter. If the table is not specifed the column is assumed to come from the table set in SetTable.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumnWithNameRemap("offices", "city", "c")

Parameters

AddCount(text name)

Add a column where, instead of displaying it, we count the number of rows that match with the specified criteria.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddCount("city")

Parameters

AddCount(text name, boolean distinct)

Add a column where, instead of displaying it, we count the number of rows that match with the specified criteria.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddCount("city", true)

Parameters

AddCrossJoin(text table)

This action will add an Cross Join to the query. If this is the first or only join added then the join will be between the table specified in SetTable and the table given here on the given condition. The table in SetTable or the result from a previous join is considered the first table. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members")
        query:AddCrossJoin("committees")

Parameters

AddGroup(text name)

This action adds a column to group rows by. Normally a Find will return every row that passes the given filter but if there is a group by column then the rows will be put into summary rows by the value of the column given here. This is useful for aggregate functions since the function will run on each group.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddColumn("productLine")
        query:AddCount("quantityInStock")
        query:AddGroup("productLine")

Parameters

AddGroup(text name, text table)

This action adds a column to group rows by. Normally a Find will return every row that passes the given filter but if there is a group by column then the rows will be put into summary rows by the value of the column given here. This is useful for aggregate functions since the function will run on each group. The table the group comes from can also be added.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddColumn("productLine")
        query:AddCount("quantityInStock")
        query:AddGroup("productLine", "products")

Parameters

AddGroupFromTable(text name, text table)

This action adds a column to group rows by. Normally a Find will return every row that passes the given filter but if there is a group by column then the rows will be put into summary rows by the value of the column given here. This is useful for aggregate functions since the function will run on each group. The table the group comes from can also be added.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddColumn("productLine")
        query:AddCount("quantityInStock")
        query:AddGroupFromTable("productLine", "products")

Parameters

AddInnerJoin(text table, text condition)

This action will add an Inner Join to the query. If this is the first or only join added then the join will be between the table specified in SetTable and the table given here on the given condition. The table in SetTable or the result from a previous join is considered the first table. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members")
        query:AddInnerJoin("committees", "members:name = committees:name")

Parameters

AddLeftJoin(text table, text condition)

This action will add an Left Join to the query. If this is the first or only join added then the join will be between the table specified in SetTable and the table given here on the given condition. The table in SetTable or the result from a previous join is considered the first table. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members")
        query:AddLeftJoin("committees", "members:name = committees:name")

Parameters

AddMaximum(text name)

Add a column where, instead of displaying it, we find the maximum value.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddMaximum("MSRP")

Parameters

AddMean(text name, boolean distinct)

Add a column where, instead of displaying it, we average its values.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddMean("MSRP", true)

Parameters

AddMean(text name)

Add a column where, instead of displaying it, we average its values.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddMean("MSRP")

Parameters

AddMinimum(text name)

Add a column where, instead of displaying it, we find the minimum value.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddMinimum("MSRP")

Parameters

AddNameRemap(text newName, Libraries.Data.Database.Support.RemappableName original)

Adds objects that have been remapped to single table for reference in actions called when building the query to text. Examples of RemappableNames are columns, tables, and subqueries.

Parameters

AddPreparedParameter(Libraries.Data.Database.Support.Parameter param)

At the plugin level prepared statements are used to add a layer of security from injection attacks. Every parameter in the prepared paramter array will be stripped from the actual query text and set appropriately at the plugin level.

Parameters

AddRightJoin(text table, text condition)

This action will add an Right Join to the query. If this is the first or only join added then the join will be between the table specified in SetTable and the table given here on the given condition. The table in SetTable or the result from a previous join is considered the first table. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members")
        query:AddRightJoin("committees", "members:name = committees:name")

Parameters

AddSort(text name, text table)

Adds a sort command, which is ascending by default, but can be set to descending. If the table that contains the columns should be specified it can be added. If a table is not specified the table set in SetTable will be used.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:AddSort("city", "offices")

Parameters

AddSort(text name, boolean ascending)

Adds a sort command, which is ascending by default, but can be set to descending.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:AddSort("city", false)

Parameters

AddSort(text name)

Adds a sort command, which is ascending by default.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:AddSort("city")

Parameters

AddSortFromTable(text name, text table, boolean ascending)

Adds a sort command, which is ascending by default, but can be set to descending. If the table that contains the columns should be specified it can be added. If a table is not specified the table set in SetTable will be used.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:AddSortFromTable("city", "offices", false)

Parameters

AddSubquery(text name, Libraries.Data.Database.Find subquery)

Adds a subquery to this query. Only Find queries are supported for now. A Subquery is a query that exists another outer query. An example could be a Find where the table to search through is actually the result of another query.

Parameters

AddSum(text name, boolean distinct)

Add a column where, instead of displaying it, we sum its values.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddSum("quantityInStock", true)

Parameters

AddSum(text name)

Add a column where, instead of displaying it, we sum its values.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("products")
        query:AddSum("quantityInStock", true)

Parameters

AddTableNameRemap(text table, text newName)

This adds a name that can be used in the place of a table. This action does not set the table of this query but only a name that can be referenced if the table is added.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("orders", "o")
        query:AddTableNameRemap("members", "m")
        query:AddInnerJoin("m", "o:name = m:name")

Parameters

AddTableReference(text table)

Internal. Queries keep track of what tables are referenced to check for ambiguous column names

Parameters

Compare(Libraries.Language.Object object)

This action compares two object hash codes and returns an integer. The result is larger if this hash code is larger than the object passed as a parameter, smaller, or equal. In this case, -1 means smaller, 0 means equal, and 1 means larger. This action was changed in Quorum 7 to return an integer, instead of a CompareResult object, because the previous implementation was causing efficiency issues.

Example Code

Object o
        Object t
        integer result = o:Compare(t) //1 (larger), 0 (equal), or -1 (smaller)

Parameters

Return

integer: The Compare result, Smaller, Equal, or Larger.

ConvertToStructuredQueryLanguage()

This action returns the query as text in Structured Query Language (SQL). Not intended to be used directly by the user, this is more for internals of the API to make calls to the database

Return

text:

EmptyColumns()

This action removes all column targets.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:EmptyColumns()

EmptyPreparedParameters()

Clears the prepared parameter list. Necessary because running a query again without emptying the list will cause the list of parameters to not shrink

EmptySorts()

This action removes all sorts.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddSort("city")

        query:EmptySorts()

EmptyTableReferences()

Empties the table references hash table.

Equals(Libraries.Language.Object object)

This action determines if two objects are equal based on their hash code values.

Example Code

use Libraries.Language.Object
        use Libraries.Language.Types.Text
        Object o
        Text t
        boolean result = o:Equals(t)

Parameters

Return

boolean: True if the hash codes are equal and false if they are not equal.

Filter(text value)

Some queries make use of the filter which is an expression checked against the values of the columns in the rows of a database table. The filter is written using Quorum syntax and will be type checked to see if it's valid. Column names can be used and the names made by the user using name remaps. An example filter would be "priority < 4" where priority is a column that exists and can be mapped to an integer.

Example Code

use Libraries.Data.Database.Delete

        Delete query
        query:SetTable("orders")
        query:Filter("priority < 4")

Parameters

GetColumnListSize()

This action returns how many columns have been added to this Query

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        integer columns = query:GetColumnListSize()

Return

integer: Returns the numbers of columns that have been added

GetColumnType(text column)

INTENDED FOR INTERNAL USE ONLY only works if the query has already been built since it needs metadata

Parameters

Return

Libraries.Language.Compile.Symbol.Type:

GetColumnType(integer position)

INTENDED FOR INTERNAL USE ONLY only works if the query has already been built since it needs metadata

Parameters

Return

Libraries.Language.Compile.Symbol.Type:

GetDatabaseMetaData()

Returns the meta data object set for this query

Return

Libraries.Data.Database.DatabaseMetaData:

GetHashCode()

This action gets the hash code for an object.

Example Code

Object o
        integer hash = o:GetHashCode()

Return

integer: The integer hash code of the object.

GetNameRemaps()

Grabs the table containing all aliases that have been set in this query

Return

Libraries.Containers.HashTable:

GetPreparedParameters()

Used at the plugin level to get all parameters that need to be set in the prepared statement

Return

Libraries.Containers.Array:

GetRawFilter()

Returns the filter as given by the user. The filter will be converted to a syntax accepted by the database server and this action returns whatever the user originally typed

Return

text:

GetRemappedName()

Returns the alias

Return

text:

GetTable()

Returns the table this query is meant to be ran against

Return

text:

GetTableReferences()

Internal. Used by other actions to grab what tables are referenced to check if column names are valid.

Return

Libraries.Containers.HashTable:

GetTranslatedFilter()

Returns the filter as translated by the quorum compiler. The filter will be converted to a syntax accepted by the database server and this action returns whatever the user originally typed

Return

text:

HasColumn(text name)

This action checks if a column is in the list of targets.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        boolean hasCity = query:HasColumn("city")

Parameters

Return

boolean: Returns true if the column was found, false otherwise.

IsDistinct()

Returns true if the column targets will only return distinct values

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")
        query:SetDistinct(true)

        boolean uniqueFind = query:IsDistinct()

Return

boolean:

RemoveColumn(text name)

This action removes a specific column from the list of targets.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:RemoveColumn("city")

Parameters

Return

boolean: Returns true if the column was removed, false otherwise

SetDatabaseMetaData(Libraries.Data.Database.DatabaseMetaData meta)

Sets the DatabaseMetaData for this query. This is necessary for checking things like if a table referenced exists or if a filter has valid column references.

Parameters

SetDistinct(boolean distinct)

This actions sets whether or not the column targets are distinct. If set to true the rows returned from each column target will be unique.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("offices")
        query:AddColumn("city")

        query:SetDistinct(true)

Parameters

SetRemappedName(text remappedName)

Sets the alias

Parameters

SetTable(text table)

This action sets the table of the query. Most queries run against a single table and this action is used to set which table the query will affect or search.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members")

Parameters

SetTable(text table, text newName)

This action sets the table of the query. Most queries run against a single table and this action is used to set which table the query will affect or search. This action also lets you rename the table so in filters a different name can be used.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTable("members", "m")

Parameters

SetTableToSubquery(Libraries.Data.Database.Find derivedTable, text newName)

This action sets the table of a Find query to the result of another Find query. A name must be given to the Subquery and that name can be used in an expression to refer to a column from the result of the subquery.

Example Code

use Libraries.Data.Database.Find

        Find derived
        derived:SetTable("payments")

        Find super
        super:AddColumn("customerNumber")
        super:AddColumn("checkNumber")
        super:AddColumn("amount")
        super:SetTableToSubquery(derived, "pay")
        super:Filter("amount > 10")

Parameters

SetTableWithName(text table, text newName)

This action sets the table of the query. Most queries run against a single table and this action is used to set which table the query will affect or search. This action also lets you rename the table so in filters a different name can be used.

Example Code

use Libraries.Data.Database.Find

        Find query
        query:SetTableWithName("members", "m")

Parameters

ToText()

By default convert to structured query language.

Return

text: