Column selection types and methods in the query builder API

The query builder API provides multiple ways to access columns in defining a query and in accessing the column values in the result set. The methods that you use depend on the task that you are performing.

Types for column specification in building a query

The query that you specify by using the Query.make method provides access to the properties on the entity type of the query. For example, the following line specifies a query on the Company entity type:

var queryCompany = Query.make(Company)

The PropertyReference class provides type-safe access to the properties on the query entity type. You specify a property reference by using the following syntax: 

EntityType#Property

You can use a property reference to filter query results, join to another entity type, order rows in the result set, or specify a column for a row query. For example, the following line joins the company query to the Address entity type and returns a Table object: 

var tableAddress = queryCompany.join(Company#PrimaryAddress)

A Table object provides the same type-safe access to properties on its entity type as a Query object does. For example, the following lines provide two predicates, one on the query object and one on the table object. These predicates filter the SQL query that is sent to the database to return only companies with the name “Stewart Media” that have a primary address in Chicago. 

queryCompany.compare(Company#Name, Equals, "Stewart Media")
tableAddress.compare(Address#City, Equals, "Chicago")

If you have a query that joins entity types, the Query and the Table objects can perform type-safe access only for properties on their own entity type. If you need to compare property values with each other or filter properties on multiple joined tables to create an OR filter, you must use a ColumnRef object. For example, the following lines create two predicates on the Table object. These predicates filter the SQL query that is sent to the database to return companies with either the name “Stewart Media” or a primary address in Chicago. 

tableAddress.or( \ or1 -> {
  or1.compare(Address#City, Equals, "Chicago")
  or1.compare(queryCompany.getColumnRef("Name"), Equals, "Stewart Media")
})

Some other predicate method signatures take an object that implements the IQueryablePropertyInfo interface as a parameter.

Type

Description

PropertyReference

A class that provides a type-safe reference to a property. Use this class to access properties on the primary entity of a Query, Table, or Restriction instance. You can use an instance of this class to create a join or predicate. You can also use an instance of this class to define a query select column to order the query results by a column in a row query. The following lines use property references to join tables and filter the primary entity of a Query and a Table object. 

var queryCompany = Query.make(Company)
var tableAddress = queryCompany.join(Company#PrimaryAddress)
queryCompany.compare(Company#Name, Equals, "Stewart Media")
tableAddress.compare(Address#City, Equals, "Chicago")

ColumnRef

A class that specifies a column reference to a property on a dependent entity, or a comparison property on the primary entity of a Query, Table, or Restriction instance. A ColumnRef instance does not provide type safety. The following lines use a column reference for a property on a dependent table: 

tableAddress.or( \ or1 -> { 
  or1.compare(Address#City, Equals, "Chicago")
  or1.compare(queryCompany.getColumnRef("Name"), Equals, "Stewart Media")
})

String

Some query builder API method signatures accept a String argument. If a signature that uses a PropertyReference parameter is available, use that type-safe signature instead.

IQueryablePropertyInfo

An interface that provides information about a property. Some query builder API method signatures accept an IQueryablePropertyInfo argument. To create this object, use code like the following: 

var lastNamesArrayList = {"Smith", "Applegate"}
var query = Query.make(Person) 
query.compareIn(Person.LASTNAME_PROP.get(), lastNamesArrayList)

Methods for column specification in building a query

You use the following methods on the QuerySelectColumns class to specify columns in a type-safe way for row queries and ordering methods on result objects. All the methods return objects of type IQuerySelectColumn.

The column selection methods that end with WithAlias have a String as the first parameter. The value that you pass is an alias for the database column, which becomes the column name in the result of the row query.

The column selection methods that do not end with WithAlias have no parameter for column alias in the row query result. To access the column, you must use a numeric position value that starts from 0 or the name of the column as a String of format TABLE.COLUMN_NAME.

Method

Parameter

Description

pathWithAlias

path

Creates a path to a column for ordering query results or to use in a row query. For example, the following lines select columns for a row query and order the results. You use the same syntax for ordering the results of an entity query. 

var query = Query.make(Person)
var results = query.select({ 
  QuerySelectColumns.pathWithAlias("FName", Paths.make(Person#FirstName)), 
  QuerySelectColumns.path( Paths.make(Person#LastName)) 
}).orderBy(QuerySelectColumns.path(Paths.make(Person#LastName)))

alias : String

An alias for the column name in the row query result. This parameter has no purpose for a column that you use to order the query results.

path : PersistentPath

A path expression that references the column to include in the row query result or to order the rows in the result set.

dbFunctionWithAlias

dbFunction

Creates an SQL expression for ordering query results or to use as a column in a row query. For example, the following lines select columns for a row query and order the results. You use the same syntax for ordering the results of an entity query. 

var query = Query.make(Address)
var results = query.select({ 
  QuerySelectColumns.dbFunction(DBFunction.Expr({
          "length (", query.getColumnRef("Address.City"), ")"
  })) 
}).orderBy(QuerySelectColumns.dbFunction(DBFunction.Expr ({
          "length(", query.getColumnRef("Address.City"), ")"
})))
Warning: Using dbFunction can cause the database to perform a whole-table scan. Ensure that use of these methods does not cause an unacceptable delay to the user interface.

alias : String

An alias for the column name in the row query result. This parameter has no purpose for an expression that you use to order the query results.

func : DBFunction

A DBFunction expression that creates a column to include in the row query result or to order the rows in the result set.

Other methods to access columns in building a query include the following:

Method

Parameter

Description

getColumnRef

Creates a reference to an entity field in terms of the database column that stores its values. Returns a ColumnRef object. This method does not support chaining. Use this method to create a column reference for a second property in a comparison predicate or for an argument to a DBFunction method. 

var query = Query.make(Address)
var rowResults = query.select({ 
  QuerySelectColumns.pathWithAlias("Country", Paths.make(Address#Country)), 
  QuerySelectColumns.dbFunctionWithAlias("LatestAddress" , 
          DBFunction.Max(Paths.make(Address#CreateTime))) 
})

propertyName : String

A String of format "Entity.Property".

Column specification in query results

To access a column in the results of an entity query, you use dot notation to access any property in the entire entity graph. For example, the following code prints the display name of a Person entity instance and the name of the user who created that Person instance. The default property is the display name, DisplayName. 

var query = Query.make(Person)
var results = query.select()
for (person in results) {
  print(person.DisplayName + " " + person.CreateUser.Contact)
}

To access a column in the results of a row query, you use the getColumn method to access any column in the row. The results of a row query do not have a default property. For example, the following code is equivalent to the previous code for an entity query. The code creates a row query and prints the name of each person in the database and the name of the user who created that person. 

var query = Query.make(Person)
var results = query.select({
  QuerySelectColumns.pathWithAlias("FName", Paths.make(Person#FirstName)),
  QuerySelectColumns.path(Paths.make(Person#LastName)),
  QuerySelectColumns.path(Paths.make(Person#CreateUser, User#Contact, Person#FirstName)),
  QuerySelectColumns.path(Paths.make(Person#CreateUser, User#Contact, Person#LastName))
})
for (person in results) {
  print(person.getColumn("FName") + " " + person.getColumn("Person.LastName") + " " 
      + person.getColumn(2) + " " + person.getColumn(3))
}

The method that you use to retrieve the value of a column from a row in the result of a row query is the following. Use one of the parameters in the table. The return value of the method is an Object object.

Method

Parameter

Description

getColumn

Retrieves the value of a column from a row in the result of a row query.

alias : String

An alias for the column name in the row query result. Use this parameter if you defined the column by using pathWithAlias or dbFunctionWithAlias.

propertyName : String

A String of format "Entity.Property". Use this parameter if you defined the column by using path or dbFunction.

position : int

A zero-based int that is the numeric position of the column in the argument to the select method that created the results.