$sql = build_select(columns => 
                        {
                          table1 => [ 'col1', 'col2', ... ],
                          table2 => [ 'col1', 'col2', ... ],
                          ...
                        });
    

This argument is required.

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

Simple equality:

    'NAME'  => "foo"        # COLUMN = 'foo'
    '!NAME' => "foo"        # NOT(COLUMN = 'foo')
    'NAME'  => [ "a", "b" ] # COLUMN IN ('a', 'b')
    '!NAME' => [ "a", "b" ] # COLUMN NOT(IN ('a', 'b'))
    

Is/is not null:

    'NAME'  => undef            # COLUMN IS NULL
    '!NAME' => undef            # COLUMN IS NOT NULL
    'NAME'  => { eq => undef }  # COLUMN IS NULL
    'NAME'  => { ne => undef }  # COLUMN IS NOT NULL
    

Comparisons:

    NAME => { OP => "foo" } # COLUMN OP 'foo'
    # (COLUMN OP 'foo' OR COLUMN OP 'goo')
    NAME => { OP => [ "foo", "goo" ] }
    

"OP" can be any of the following:

    OP                  SQL operator
    -------------       ------------
    similar             SIMILAR TO
    match               ~
    imatch              ~*
    regex, regexp       REGEXP
    like                LIKE
    ilike               ILIKE
    rlike               RLIKE
    is                  IS
    is_not              IS NOT
    ne                  <>
    eq                  =
    lt                  <
    gt                  >
    le                  <=
    ge                  >=
    

Ranges:

    NAME => { between => [ 1, 99 ] } # COLUMN BETWEEN 1 AND 99
    NAME => { gt_lt => [ 1, 99 ] } # (COLUMN > 1 AND < 99)
    NAME => { gt_le => [ 1, 99 ] } # (COLUMN > 1 AND <= 99)
    NAME => { ge_lt => [ 1, 99 ] } # (COLUMN >= 1 AND < 99)
    NAME => { ge_le => [ 1, 99 ] } # (COLUMN >= 1 AND <= 99)
    

If a value is a reference to a scalar, that scalar is "inlined" without any quoting.

    'NAME' => \"foo"        # COLUMN = foo
    'NAME' => [ "a", \"b" ] # COLUMN IN ('a', b)
    

Undefined values are translated to the keyword NULL when included in a multi-value comparison.

    'NAME' => [ "a", undef ] # COLUMN IN ('a', NULL)
    

Set operations:

    ### Informix (default) ###
    # A IN COLUMN
    'NAME' => { in_set => 'A' } 
    # NOT(A IN COLUMN)
    '!NAME' => { in_set => 'A' } 
    # (A IN COLUMN OR B IN COLUMN)
    'NAME' => { in_set => [ 'A', 'B'] } 
    'NAME' => { any_in_set => [ 'A', 'B'] } 
    # NOT(A IN COLUMN) AND NOT(B IN COLUMN)
    '!NAME' => { in_set => [ 'A', 'B'] } 
    '!NAME' => { any_in_set => [ 'A', 'B'] } 
    # (A IN COLUMN AND B IN COLUMN)
    'NAME' => { all_in_set => [ 'A', 'B'] } 
    # NOT(A IN COLUMN AND B IN COLUMN)
    '!NAME' => { all_in_set => [ 'A', 'B'] } 
    ### MySQL (requires db parameter)  ###
    # FIND_IN_SET(A, COLUMN) > 0
    'NAME' => { in_set => 'A' } 
    # NOT(FIND_IN_SET(A, COLUMN) > 0)
    '!NAME' => { in_set => 'A' } 
    # (FIND_IN_SET(A, COLUMN) > 0 OR FIND_IN_SET(B, COLUMN) > 0)
    'NAME' => { in_set => [ 'A', 'B'] } 
    'NAME' => { any_in_set => [ 'A', 'B'] } 
    # NOT(FIND_IN_SET(A, COLUMN) > 0) AND NOT(FIND_IN_SET(B, COLUMN) > 0)
    '!NAME' => { in_set => [ 'A', 'B'] } 
    '!NAME' => { any_in_set => [ 'A', 'B'] } 
    # (FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
    'NAME' => { all_in_set => [ 'A', 'B'] } 
    # NOT(FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
    '!NAME' => { all_in_set => [ 'A', 'B'] }
    

Array operations:

    # A = ANY(COLUMN)
    'NAME' => { in_array => 'A' } 
    # NOT(A = ANY(COLUMN))
    '!NAME' => { in_array => 'A' } 
    # (A = ANY(COLUMN) OR B = ANY(COLUMN))
    'NAME' => { in_array => [ 'A', 'B'] } 
    'NAME' => { any_in_array => [ 'A', 'B'] } 
    # NOT(A = ANY(COLUMN) OR B = ANY(COLUMN))
    '!NAME' => { in_array => [ 'A', 'B'] } 
    '!NAME' => { any_in_array => [ 'A', 'B'] } 
    # (A = ANY(COLUMN) AND B = ANY(COLUMN))
    'NAME' => { all_in_array => [ 'A', 'B'] } 
    # NOT(A = ANY(COLUMN) AND B = ANY(COLUMN))
    '!NAME' => { all_in_array => [ 'A', 'B'] }
    

PostgreSQL ltree operations:

    OP                  SQL operator
    -------------       ------------
    ltree_ancestor      @>
    ltree_descendant    <@
    ltree_query         ~
    ltree_ltxtquery     @
    ltree_concat        ||
    

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:

    [ \'mycol > ?' => 123 ]
    

    $sql = build_select
    (
      query_is_sql => 1,
      query =>
      [
        date => { lt => '2003-12-25 20:00:00' },
      ],
      ...
    );
    

Here the date value "2003-12-25 20:00:00" must be in the format that the current database expects for columns of that data type.

But if query_is_sql is false or omitted, then any query value that can be handled by the Rose::DB::Object-derived object method that services the corresponding database column is valid. (Note that this is only possible when this method is called from one of the built-in Rose::DB::Object::Manager methods, e.g., get_objects().)

Example:

    $dt = DateTime->new(year => 2001, month => 1, day => 31);
    $sql = build_select
    (
      query =>
      [
        date => { gt => $dt },
        date => { lt => '12/25/2003 8pm' },
      ],
      ...
    );
    

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.

If an item in the referenced array is itself a reference to a scalar, then that item will be dereferenced and passed through unmodified.

If more than one table is in the list, then each table is aliased to "tN", where N is an ascending number starting with 1. The tables are numbered according to their order in TABLES. Example:

    $sql = build_select(tables => [ 'foo', 'bar', 'baz' ], ...);
    print $sql;
    # SELECT ... FROM
    #   foo AS t1,
    #   bar AS t2,
    #   baz AS t3
    # ...
    

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.