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

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.

Parameters

  • text name: The name of the column that will be targeted

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column that will be targeted
  • text table: The name of the table the column is from

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The table the column is from
  • text column: The name of the column you wish to remap
  • text newName: The new name that can be used elsewhere in the query in the place of the actual name

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text column: The name of the column you wish to remap
  • text newName: The new name that can be used elsewhere in the query in the place of the actual name

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The table the column is from
  • text column: The name of the column you wish to remap
  • text newName: The new name that can be used elsewhere in the query in the place of the actual name

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text column: The name of the column you wish to remap
  • text newName: The new name that can be used elsewhere in the query in the place of the actual name

Example

use Libraries.Data.Database.Find

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

AddCount(text name)

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

Parameters

  • text name: The name of the column used to count

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column used to count
  • boolean distinct: If true the count will only count unique values

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The name of the table to join

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column used to group the rows

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column used to group the rows
  • text table: The table that contains the given column name

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column used to group the rows
  • text table: The table that contains the given column name

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The name of the table to join
  • text condition: The given condition to join the tables on

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The name of the table to join
  • text condition: The given condition to join the tables on

Example

use Libraries.Data.Database.Find

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

AddMaximum(text name)

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

Parameters

  • text name: The name of the column to find the maximum of

Example

use Libraries.Data.Database.Find

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

AddMean(text name, boolean distinct)

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

Parameters

  • text name: The name of the column to average
  • boolean distinct: If true the mean will only be of unique values

Example

use Libraries.Data.Database.Find

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

AddMean(text name)

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

Parameters

  • text name: The name of the column to average

Example

use Libraries.Data.Database.Find

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

AddMinimum(text name)

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

Parameters

  • text name: The name of the column to find the minimum of

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The name of the table to join
  • text condition: The given condition to join the tables on

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text name: The name of the column used to sort
  • text table: The table that contains the given column name

Example

use Libraries.Data.Database.Find

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

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

AddSort(text name, boolean ascending)

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

Parameters

  • text name: The name of the column used to sort
  • boolean ascending: If true the sort will be ascending, if false it will be descending

Example

use Libraries.Data.Database.Find

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

query:AddSort("city", false)

AddSort(text name)

Adds a sort command, which is ascending by default.

Parameters

  • text name: The name of the column used to sort

Example

use Libraries.Data.Database.Find

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

query:AddSort("city")

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.

Parameters

  • text name: The name of the column used to sort
  • text table: The table that contains the given column name
  • boolean ascending: If true the sort will be ascending, if false it will be descending

Example

use Libraries.Data.Database.Find

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

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

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.

Parameters

  • text name: The name of the column to sum
  • boolean distinct: If true the sum will only be of unique values

Example

use Libraries.Data.Database.Find

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

AddSum(text name)

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

Parameters

  • text name: The name of the column to sum

Example

use Libraries.Data.Database.Find

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

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.

Parameters

  • text table: The name of the table to set for this query
  • text newName

Example

use Libraries.Data.Database.Find

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

AddTableReference(text table)

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

Parameters

  • text table

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.

Parameters

Return

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

Example

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

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

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

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.

Parameters

Return

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

Example

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