Working with row queries

Certain kinds of SQL results require row queries instead of entity queries. The results of row queries are row objects that provide only the properties that you select.

Entity queries cannot produce certain kinds of SQL results. Entity queries have the following characteristics:
  • They cannot produce the results that SQL aggregate functions provide.
  • They cannot produce results that involve filtering the rows in the dependent table on a SQL outer join.
  • They load the complete object row into the application cache for selected entity instances, even though you often need a only few columns of data from the database.
  • They provide access to related entity instances by making additional queries to the database to access entity instances that are not in the cache. These queries also load entire rows from the database tables.

In many cases, a row query can provide more suitable results than an entity query. A row query uses a single database query on both the main entity and related entities to provide only the subset of properties that you need.

If you need to view the results of your query in a list view, you must convert a row query to another format. List views cannot interpret the results of a row query. Consider whether an entity query on a view entity can provide more straightforward access to a subset of columns on primary and related entities.

See also

Setting up row queries

You set up row queries by passing column selection parameters to the select method on query objects. The columns that you select become the columns of data in the row query result. The columns that you select for a row query need not be the columns to which you applied predicates prior to calling the select method. The columns that you select are the columns of data that you want to access from rows in the row query result.

Names for selected columns

You can always access values for selected columns in a row query result by their position in the list of columns. Alternatively, you can access values by using aliases that you provide when you select the columns. An alias is a String value of your choice, which serves as a key for accessing specific column values from individual rows in the result. If you do not specify an alias, the default name of a column has the entity.property syntax. An alias replaces this default name.

Aggregate functions in row queries

You set up an aggregate row query by applying database aggregate functions to selected columns. For an aggregate row query, the result contains values that are aggregated from the table rows used in the query instead of the rows themselves. The database performs the aggregation as part of executing the query.