Returns an SQL "select" query string (in scalar context) or an SQL "select" query string with placeholders and a reference to an array of bind values (in list context) constructed based on PARAMS. Valid PARAMS are described below.

A reference to an array of extra SQL clauses to add to the "WHERE" portion of the query string. This is the obligatory "escape hatch" for clauses that are not supported by arguments to the query parameter.

A DBI database handle already connected to the correct database. If this argument is omitted, an attempt will be made to extract a database handle from the db argument. If this fails, or if there is no db argument, a fatal error will occur.

The query parameters, passed as a reference to an array of name/value pairs, scalar references, or array references. PARAMS may include an arbitrary list of selection parameters used to modify the "WHERE" clause of the SQL select statement. Any query parameter that is not in one of the forms described below will cause a fatal error.

Valid selection parameters are described below, along with the SQL clause they add to the select statement.

Any of the operations described above can have "_sql" appended to indicate that the corresponding values are to be "inlined" (i.e., included in the SQL query as-is, with no quoting of any kind). This is useful for comparing two columns. For example, this query:

query => [ legs => { gt_sql => 'eyes' } ]

would produce this SQL:

SELECT ... FROM animals WHERE legs > eyes

where "legs" and "eyes" are both left unquoted.

The same NAME string may be repeated multiple times. (This is the primary reason that the query is a reference to an array of name/value pairs, rather than a reference to a hash, which would only allow each NAME once.) Example:

query =>
[
age => { gt => 10 },
age => { lt => 20 },
]

The string "NAME" can take many forms, each of which eventually resolves to a database column (COLUMN in the examples above).

Literal SQL can be included by providing a reference to a scalar:

\'mycol > 123'

To use placeholders and bind values, pass a reference to an array containing a scalar reference to the literal SQL with placeholders as the first item, followed by a list of values to bind:

A bare column name. If the query includes more than one table, the column name may be ambiguous if it appears in two or more tables. In that case, a fatal error will occur. To solve this, use one of the less ambiguous forms below.

A column name and a table alias joined by a dot. The table alias is in the form "tN", where "N" is a number starting from 1. See the documentation for tables parameter below to learn how table aliases are assigned to tables.

A get_set column method name from a Rose::DB::Object-derived class fronting one of the tables being queried. There may be ambiguity here if the same method name is defined on more than one of the classes involved in the query. In such a case, the method will be mapped to the first Rose::DB::Object-derived class that contains a method by that name, considered in the order that the tables are provided in the tables parameter.

Un-prefixed column or method names that are ambiguous (i.e., exist in more than one of the tables being queried) are considered to be part of the primary table ("t1").

Finally, in the case of apparently intractable ambiguity, like when a table name is the same as another table's alias, remember that you can always use the "tn_"-prefixed column name aliases, which are unique within a given query.

All of these clauses are joined by logic (default: "AND") in the final query. Example:

Here a DateTime object and a loosely formatted date are passed as values. Provided the Rose::DB::Object-derived object method that services the "date" column can handle such values, they will be parsed and formatted as appropriate for the current database.

The advantage of this approach is that the query values do not have to be so rigorously specified, nor do they have to be in a database-specific format.

The disadvantage is that all of this parsing and formatting is done for every query value, and that adds additional overhead to each call.

Usually, this overhead is dwarfed by the time required for the database to service the query, and, perhaps more importantly, the reduced maintenance headache and busywork required to properly format all query values.

The names of the columns to select from the table. COLUMNS may be a string of comma-separated column names, or a reference to an array of column names. If this parameter is omitted, it defaults to all of the columns in all of the tables participating in the query (according to the value of the columns argument).

Furthermore, if there is no explicit value for the select parameter and if the unique_aliases parameter is set to true, then each selected column is aliased with a "tN_" prefix in a multi-table query. Example:

SELECT
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
foo AS t1,
bar AS t2
WHERE
...

These unique aliases provide a technique of last resort for unambiguously addressing a column in a query clause.

If true, then each selected column will be given a unique alias by prefixing it with its table alias and an underscore. The default value is false. See the documentation for the tables parameter above for an example.