.\" Automatically generated by Pod::Man 2.25 (Pod::Simple 3.16)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.ie \nF \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    nr % 0
.    rr F
.\}
.el \{\
.    de IX
..
.\}
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "Rose::DB::Object::QueryBuilder 3pm"
.TH Rose::DB::Object::QueryBuilder 3pm "2012-04-15" "perl v5.14.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Rose::DB::Object::QueryBuilder \- Build SQL queries on behalf of Rose::DB::Object::Manager.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\&    use Rose::DB::Object::QueryBuilder qw(build_select);
\&
\&    # Build simple query
\&    $sql = build_select
\&    (
\&      dbh     => $dbh,
\&      select  => \*(AqCOUNT(*)\*(Aq,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type title date) ] },
\&      query   =>
\&      [
\&        category => [ \*(Aqsports\*(Aq, \*(Aqscience\*(Aq ],
\&        type     => \*(Aqnews\*(Aq,
\&        title    => { like => [ \*(Aq%million%\*(Aq, 
\&                                \*(Aq%resident%\*(Aq ] },
\&      ],
\&      query_is_sql => 1);
\&
\&    $sth = $dbh\->prepare($sql);
\&    $sth\->execute;
\&    $count = $sth\->fetchrow_array;
\&
\&    ...
\&
\&    # Return query with placeholders, plus bind values
\&    ($sql, $bind) = build_select
\&    (
\&      dbh     => $dbh,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type title date) ] },
\&      query   =>
\&      [
\&        category => [ \*(Aqsports\*(Aq, \*(Aqscience\*(Aq ],
\&        type     => \*(Aqnews\*(Aq,
\&        title    => { like => [ \*(Aq%million%\*(Aq, 
\&                                \*(Aq%resident%\*(Aq ] },
\&      ],
\&      query_is_sql => 1,
\&      sort_by      => \*(Aqtitle DESC, category\*(Aq,
\&      limit        => 5);
\&
\&    $sth = $dbh\->prepare($sql);
\&    $sth\->execute(@$bind);
\&
\&    while($row = $sth\->fetchrow_hashref) { ... }
\&
\&    ...
\&
\&    # Coerce query values into the right format
\&    ($sql, $bind) = build_select
\&    (
\&      db      => $db,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type title date) ] },
\&      classes => { articles => \*(AqArticle\*(Aq },
\&      query   =>
\&      [
\&        type     => \*(Aqnews\*(Aq,
\&        date     => { lt => \*(Aqnow\*(Aq },
\&        date     => { gt => DateTime\->new(...) },
\&      ],
\&      sort_by      => \*(Aqtitle DESC, category\*(Aq,
\&      limit        => 5);
\&
\&    $sth = $dbh\->prepare($sql);
\&    $sth\->execute(@$bind);
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Rose::DB::Object::QueryBuilder is used to build \s-1SQL\s0 queries, primarily in service of the Rose::DB::Object::Manager class.  It (optionally) exports two functions: \fIbuild_select()\fR and \fIbuild_where_clause()\fR.
.SH "FUNCTIONS"
.IX Header "FUNCTIONS"
.IP "\fBbuild_select \s-1PARAMS\s0\fR" 4
.IX Item "build_select PARAMS"
Returns an \s-1SQL\s0 \*(L"select\*(R" query string (in scalar context) or an \s-1SQL\s0 \*(L"select\*(R" query string with placeholders and a reference to an array of bind values (in list context) constructed based on \s-1PARAMS\s0.  Valid \s-1PARAMS\s0 are described below.
.RS 4
.IP "\fBclauses \s-1CLAUSES\s0\fR" 4
.IX Item "clauses CLAUSES"
A reference to an array of extra \s-1SQL\s0 clauses to add to the \*(L"\s-1WHERE\s0\*(R" portion of the query string.  This is the obligatory \*(L"escape hatch\*(R" for clauses that are not supported by arguments to the query parameter.
.IP "\fBcolumns \s-1HASHREF\s0\fR" 4
.IX Item "columns HASHREF"
A reference to a hash keyed by table name, each of which points to a reference to an array of the names of the columns in that table.  Example:
.Sp
.Vb 6
\&    $sql = build_select(columns => 
\&                        {
\&                          table1 => [ \*(Aqcol1\*(Aq, \*(Aqcol2\*(Aq, ... ],
\&                          table2 => [ \*(Aqcol1\*(Aq, \*(Aqcol2\*(Aq, ... ],
\&                          ...
\&                        });
.Ve
.Sp
This argument is required.
.IP "\fBdb \s-1DB\s0\fR" 4
.IX Item "db DB"
A Rose::DB\-derived object.  This argument is required if query_is_sql is false or omitted.
.IP "\fBdbh \s-1DBH\s0\fR" 4
.IX Item "dbh DBH"
A \s-1DBI\s0 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.
.IP "\fBgroup_by \s-1CLAUSE\s0\fR" 4
.IX Item "group_by CLAUSE"
A fully formed \s-1SQL\s0 \*(L"\s-1GROUP\s0 \s-1BY\s0 ...\*(R" clause, sans the words \*(L"\s-1GROUP\s0 \s-1BY\s0\*(R", or a reference to an array of strings to be joined with a comma and appended to the \*(L"\s-1GROUP\s0 \s-1BY\s0\*(R" clause.
.IP "\fBlimit \s-1NUMBER\s0\fR" 4
.IX Item "limit NUMBER"
A number to use in the \*(L"\s-1LIMIT\s0 ...\*(R" clause.
.IP "\fBlogic \s-1LOGIC\s0\fR" 4
.IX Item "logic LOGIC"
A string indicating the logic that will be used to join the statements in the \s-1WHERE\s0 clause.  Valid values for \s-1LOGIC\s0 are \*(L"\s-1AND\s0\*(R" and \*(L"\s-1OR\s0\*(R".  If omitted, it defaults to \*(L"\s-1AND\s0\*(R".
.IP "\fBpretty \s-1BOOL\s0\fR" 4
.IX Item "pretty BOOL"
If true, the \s-1SQL\s0 returned will have slightly nicer formatting.
.IP "\fBquery \s-1PARAMS\s0\fR" 4
.IX Item "query PARAMS"
The query parameters, passed as a reference to an array of name/value pairs, scalar references, or array references.  \s-1PARAMS\s0 may include an arbitrary list of selection parameters used to modify the \*(L"\s-1WHERE\s0\*(R" clause of the \s-1SQL\s0 select statement.  Any query parameter that is not in one of the forms described below will cause a fatal error.
.Sp
Valid selection parameters are described below, along with the \s-1SQL\s0 clause they add to the select statement.
.Sp
Simple equality:
.Sp
.Vb 2
\&    \*(AqNAME\*(Aq  => "foo"        # COLUMN = \*(Aqfoo\*(Aq
\&    \*(Aq!NAME\*(Aq => "foo"        # NOT(COLUMN = \*(Aqfoo\*(Aq)
\&
\&    \*(AqNAME\*(Aq  => [ "a", "b" ] # COLUMN IN (\*(Aqa\*(Aq, \*(Aqb\*(Aq)
\&    \*(Aq!NAME\*(Aq => [ "a", "b" ] # COLUMN NOT(IN (\*(Aqa\*(Aq, \*(Aqb\*(Aq))
.Ve
.Sp
Is/is not null:
.Sp
.Vb 2
\&    \*(AqNAME\*(Aq  => undef            # COLUMN IS NULL
\&    \*(Aq!NAME\*(Aq => undef            # COLUMN IS NOT NULL
\&
\&    \*(AqNAME\*(Aq  => { eq => undef }  # COLUMN IS NULL
\&    \*(AqNAME\*(Aq  => { ne => undef }  # COLUMN IS NOT NULL
.Ve
.Sp
Comparisons:
.Sp
.Vb 1
\&    NAME => { OP => "foo" } # COLUMN OP \*(Aqfoo\*(Aq
\&
\&    # (COLUMN OP \*(Aqfoo\*(Aq OR COLUMN OP \*(Aqgoo\*(Aq)
\&    NAME => { OP => [ "foo", "goo" ] }
.Ve
.Sp
\&\*(L"\s-1OP\s0\*(R" can be any of the following:
.Sp
.Vb 10
\&    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                  >=
.Ve
.Sp
Ranges:
.Sp
.Vb 1
\&    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)
.Ve
.Sp
If a value is a reference to a scalar, that scalar is \*(L"inlined\*(R" without any quoting.
.Sp
.Vb 2
\&    \*(AqNAME\*(Aq => \e"foo"        # COLUMN = foo
\&    \*(AqNAME\*(Aq => [ "a", \e"b" ] # COLUMN IN (\*(Aqa\*(Aq, b)
.Ve
.Sp
Undefined values are translated to the keyword \s-1NULL\s0 when included in a multi-value comparison.
.Sp
.Vb 1
\&    \*(AqNAME\*(Aq => [ "a", undef ] # COLUMN IN (\*(Aqa\*(Aq, NULL)
.Ve
.Sp
Set operations:
.Sp
.Vb 1
\&    ### Informix (default) ###
\&
\&    # A IN COLUMN
\&    \*(AqNAME\*(Aq => { in_set => \*(AqA\*(Aq } 
\&
\&    # NOT(A IN COLUMN)
\&    \*(Aq!NAME\*(Aq => { in_set => \*(AqA\*(Aq } 
\&
\&    # (A IN COLUMN OR B IN COLUMN)
\&    \*(AqNAME\*(Aq => { in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(AqNAME\*(Aq => { any_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(A IN COLUMN) AND NOT(B IN COLUMN)
\&    \*(Aq!NAME\*(Aq => { in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(Aq!NAME\*(Aq => { any_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # (A IN COLUMN AND B IN COLUMN)
\&    \*(AqNAME\*(Aq => { all_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(A IN COLUMN AND B IN COLUMN)
\&    \*(Aq!NAME\*(Aq => { all_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    ### MySQL (requires db parameter)  ###
\&
\&    # FIND_IN_SET(A, COLUMN) > 0
\&    \*(AqNAME\*(Aq => { in_set => \*(AqA\*(Aq } 
\&
\&    # NOT(FIND_IN_SET(A, COLUMN) > 0)
\&    \*(Aq!NAME\*(Aq => { in_set => \*(AqA\*(Aq } 
\&
\&    # (FIND_IN_SET(A, COLUMN) > 0 OR FIND_IN_SET(B, COLUMN) > 0)
\&    \*(AqNAME\*(Aq => { in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(AqNAME\*(Aq => { any_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(FIND_IN_SET(A, COLUMN) > 0) AND NOT(FIND_IN_SET(B, COLUMN) > 0)
\&    \*(Aq!NAME\*(Aq => { in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(Aq!NAME\*(Aq => { any_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # (FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
\&    \*(AqNAME\*(Aq => { all_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
\&    \*(Aq!NAME\*(Aq => { all_in_set => [ \*(AqA\*(Aq, \*(AqB\*(Aq] }
.Ve
.Sp
Array operations:
.Sp
.Vb 2
\&    # A = ANY(COLUMN)
\&    \*(AqNAME\*(Aq => { in_array => \*(AqA\*(Aq } 
\&
\&    # NOT(A = ANY(COLUMN))
\&    \*(Aq!NAME\*(Aq => { in_array => \*(AqA\*(Aq } 
\&
\&    # (A = ANY(COLUMN) OR B = ANY(COLUMN))
\&    \*(AqNAME\*(Aq => { in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(AqNAME\*(Aq => { any_in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(A = ANY(COLUMN) OR B = ANY(COLUMN))
\&    \*(Aq!NAME\*(Aq => { in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&    \*(Aq!NAME\*(Aq => { any_in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # (A = ANY(COLUMN) AND B = ANY(COLUMN))
\&    \*(AqNAME\*(Aq => { all_in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] } 
\&
\&    # NOT(A = ANY(COLUMN) AND B = ANY(COLUMN))
\&    \*(Aq!NAME\*(Aq => { all_in_array => [ \*(AqA\*(Aq, \*(AqB\*(Aq] }
.Ve
.Sp
PostgreSQL ltree operations:
.Sp
.Vb 7
\&    OP                  SQL operator
\&    \-\-\-\-\-\-\-\-\-\-\-\-\-       \-\-\-\-\-\-\-\-\-\-\-\-
\&    ltree_ancestor      @>
\&    ltree_descendant    <@
\&    ltree_query         ~
\&    ltree_ltxtquery     @
\&    ltree_concat        ||
.Ve
.Sp
Any of the operations described above can have \*(L"_sql\*(R" appended to indicate that the corresponding values are to be \*(L"inlined\*(R" (i.e., included in the \s-1SQL\s0 query as-is, with no quoting of any kind).  This is useful for comparing two columns.  For example, this query:
.Sp
.Vb 1
\&    query => [ legs => { gt_sql => \*(Aqeyes\*(Aq } ]
.Ve
.Sp
would produce this \s-1SQL:\s0
.Sp
.Vb 1
\&    SELECT ... FROM animals WHERE legs > eyes
.Ve
.Sp
where \*(L"legs\*(R" and \*(L"eyes\*(R" are both left unquoted.
.Sp
The same \s-1NAME\s0 string may be repeated multiple times.  (This is the primary reason that the query is a reference to an \fIarray\fR of name/value pairs, rather than a reference to a hash, which would only allow each \s-1NAME\s0 once.)  Example:
.Sp
.Vb 5
\&    query =>
\&    [
\&      age => { gt => 10 },
\&      age => { lt => 20 },
\&    ]
.Ve
.Sp
The string \*(L"\s-1NAME\s0\*(R" can take many forms, each of which eventually resolves to a database column (\s-1COLUMN\s0 in the examples above).
.Sp
Literal \s-1SQL\s0 can be included by providing a reference to a scalar:
.Sp
.Vb 1
\&    \e\*(Aqmycol > 123\*(Aq
.Ve
.Sp
To use placeholders and bind values, pass a reference to an array containing a scalar reference to the literal \s-1SQL\s0 with placeholders as the first item, followed by a list of values to bind:
.Sp
.Vb 1
\&    [ \e\*(Aqmycol > ?\*(Aq => 123 ]
.Ve
.RS 4
.ie n .IP """column""" 4
.el .IP "\f(CWcolumn\fR" 4
.IX Item "column"
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.
.ie n .IP """table.column""" 4
.el .IP "\f(CWtable.column\fR" 4
.IX Item "table.column"
A column name and a table name joined by a dot.  This is the \*(L"fully qualified\*(R" column name.
.ie n .IP """tN.column""" 4
.el .IP "\f(CWtN.column\fR" 4
.IX Item "tN.column"
A column name and a table alias joined by a dot.  The table alias is in the form \*(L"tN\*(R", where \*(L"N\*(R" is a number starting from 1.  See the documentation for tables parameter below to learn how table aliases are assigned to tables.
.ie n .IP "Any of the above prefixed with ""!""" 4
.el .IP "Any of the above prefixed with ``!''" 4
.IX Item "Any of the above prefixed with !"
This indicates the negation of the specified condition.
.RE
.RS 4
.Sp
If query_is_sql is false or omitted, then \s-1NAME\s0 can also take on these additional forms:
.ie n .IP """method""" 4
.el .IP "\f(CWmethod\fR" 4
.IX Item "method"
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 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.
.ie n .IP """!method""" 4
.el .IP "\f(CW!method\fR" 4
.IX Item "!method"
This indicates the negation of the specified condition.
.RE
.RS 4
.Sp
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 (\*(L"t1\*(R").
.Sp
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 \*(L"tn_\*(R"\-prefixed column name aliases, which are unique within a given query.
.Sp
All of these clauses are joined by logic (default: \*(L"\s-1AND\s0\*(R") in the final query.  Example:
.Sp
.Vb 10
\&    $sql = build_select
\&    (
\&      dbh     => $dbh,
\&      select  => \*(Aqid, title\*(Aq,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type title) ] },
\&      query   =>
\&      [
\&        category => [ \*(Aqsports\*(Aq, \*(Aqscience\*(Aq ],
\&        type     => \*(Aqnews\*(Aq,
\&        title    => { like => [ \*(Aq%million%\*(Aq, 
\&                                \*(Aq%resident%\*(Aq ] },
\&      ],
\&      query_is_sql => 1);
.Ve
.Sp
The above returns an \s-1SQL\s0 statement something like this:
.Sp
.Vb 7
\&    SELECT id, title FROM articles WHERE
\&      category IN (\*(Aqsports\*(Aq, \*(Aqscience\*(Aq)
\&      AND
\&      type = \*(Aqnews\*(Aq
\&      AND
\&      (title LIKE \*(Aq%million%\*(Aq OR title LIKE \*(Aq%resident%\*(Aq)
\&    LIMIT 5
.Ve
.Sp
Nested boolean logic is possible using the special keywords \f(CW\*(C`and\*(C'\fR and \f(CW\*(C`or\*(C'\fR (case insensitive).  Example:
.Sp
.Vb 10
\&    $sql = build_select
\&    (
\&      dbh     => $dbh,
\&      select  => \*(Aqid, title\*(Aq,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type title) ] },
\&      query   =>
\&      [
\&        or =>
\&        [
\&          and => [ category => undef, type => \*(Aqaux\*(Aq ],
\&          category => [ \*(Aqsports\*(Aq, \*(Aqscience\*(Aq ],
\&        ],
\&        type     => \*(Aqnews\*(Aq,
\&        title    => { like => [ \*(Aq%million%\*(Aq, 
\&                                \*(Aq%resident%\*(Aq ] },
\&      ],
\&      query_is_sql => 1);
.Ve
.Sp
which returns an \s-1SQL\s0 statement something like this:
.Sp
.Vb 12
\&    SELECT id, title FROM articles WHERE
\&      (
\&        (
\&          category IS NULL AND
\&          type = \*(Aqaux\*(Aq
\&        ) 
\&        OR category IN (\*(Aqsports\*(Aq, \*(Aqscience\*(Aq)
\&      )
\&      AND
\&      type = \*(Aqnews\*(Aq
\&      AND
\&      (title LIKE \*(Aq%million%\*(Aq OR title LIKE \*(Aq%resident%\*(Aq)
.Ve
.Sp
The \f(CW\*(C`and\*(C'\fR and \f(CW\*(C`or\*(C'\fR keywords can be used multiple times within a query (just like all other \s-1NAME\s0 specifiers described earlier) and can be arbitrarily nested.
.Sp
If you have a column named \*(L"and\*(R" or \*(L"or\*(R", you'll have to use the fully-qualified (table.column) or alias-qualified (tN.column) forms in order to address that column.
.Sp
If query_is_sql is false or omitted, all of the parameter values are passed through the \f(CW\*(C`parse_value()\*(C'\fR and \f(CW\*(C`format_value()\*(C'\fR methods of their corresponding Rose::DB::Object::Metadata::Column\-dervied column objects.
.Sp
If a column object returns true from its \fImanager_uses_method()\fR method, then its parameter value is passed through the corresponding Rose::DB::Object\-derived object method instead.
.Sp
Example:
.Sp
.Vb 1
\&    $dt = DateTime\->new(year => 2001, month => 1, day => 31);
\&
\&    $sql = build_select
\&    (
\&      db      => $db,
\&      select  => \*(Aqid, category\*(Aq,
\&      tables  => [ \*(Aqarticles\*(Aq ],
\&      columns => { articles => [ qw(id category type date) ] },
\&      classes => { articles => \*(AqArticle\*(Aq },
\&      query   =>
\&      [
\&        type  => \*(Aqnews\*(Aq,
\&        date  => { lt => \*(Aq12/25/2003 8pm\*(Aq },
\&        date  => { gt => $dt },
\&      ],
\&      sort_by => \*(Aqid DESC, category\*(Aq,
\&      limit   => 5);
.Ve
.Sp
The above returns an \s-1SQL\s0 statement something like this:
.Sp
.Vb 8
\&    SELECT id, category FROM articles WHERE
\&      type = \*(Aqnews\*(Aq
\&      AND
\&      date < \*(Aq2003\-12\-25 20:00:00\*(Aq
\&      AND
\&      date > \*(Aq2001\-01\-31 00:00:00\*(Aq
\&    ORDER BY id DESC, category
\&    LIMIT 5
.Ve
.Sp
Finally, here's an example using more than one table:
.Sp
.Vb 1
\&    $dt = DateTime\->new(year => 2001, month => 1, day => 31);
\&
\&    $sql = build_select
\&    (
\&      db      => $db,
\&      tables  => [ \*(Aqarticles\*(Aq, \*(Aqcategories\*(Aq ],
\&      columns =>
\&      {
\&        articles   => [ qw(id name category_id date) ],
\&        categories => [ qw(id name description) ],
\&      },
\&      classes =>
\&      {
\&        articles   => \*(AqArticle\*(Aq,
\&        categories => \*(AqCategory\*(Aq,
\&      },
\&      query   =>
\&      [
\&        \*(Aq!t1.name\*(Aq => { like => \*(Aq%foo%\*(Aq },
\&        t2.name    => \*(Aqnews\*(Aq,
\&        date       => { lt => \*(Aq12/25/2003 8pm\*(Aq },
\&        date       => { gt => $dt },
\&      ],
\&      clauses =>
\&      [
\&        \*(Aqt1.category_id = t2.id\*(Aq,
\&      ],
\&      sort_by      => \*(Aqarticles.name DESC, t2.name\*(Aq,
\&      limit        => 5);
.Ve
.Sp
The above returns an \s-1SQL\s0 statement something like this:
.Sp
.Vb 10
\&    SELECT
\&      t1.id,
\&      t1.name,
\&      t1.category_id,
\&      t1.date,
\&      t2.id,
\&      t2.name,
\&      t2.description
\&    FROM
\&      articles   t1,
\&      categories t2
\&    WHERE
\&      t1.category_id = t2.id
\&      AND
\&      NOT(t1.name LIKE \*(Aq%foo%\*(Aq)
\&      AND
\&      t2.name = \*(Aqnews\*(Aq
\&      AND
\&      t1.date < \*(Aq2003\-12\-25 20:00:00\*(Aq
\&      AND
\&      t1.date > \*(Aq2001\-01\-31 00:00:00\*(Aq
\&    ORDER BY articles.name DESC, t2.name
\&    LIMIT 5
.Ve
.RE
.IP "\fBquery_is_sql \s-1BOOL\s0\fR" 4
.IX Item "query_is_sql BOOL"
If omitted, this boolean flag is false.  If true, then the values of the query parameters are taken as literal strings that are suitable for direct use in \s-1SQL\s0 queries.  Example:
.Sp
.Vb 9
\&    $sql = build_select
\&    (
\&      query_is_sql => 1,
\&      query =>
\&      [
\&        date => { lt => \*(Aq2003\-12\-25 20:00:00\*(Aq },
\&      ],
\&      ...
\&    );
.Ve
.Sp
Here the date value \*(L"2003\-12\-25 20:00:00\*(R" must be in the format that the current database expects for columns of that data type.
.Sp
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., \fIget_objects()\fR.)
.Sp
Example:
.Sp
.Vb 1
\&    $dt = DateTime\->new(year => 2001, month => 1, day => 31);
\&
\&    $sql = build_select
\&    (
\&      query =>
\&      [
\&        date => { gt => $dt },
\&        date => { lt => \*(Aq12/25/2003 8pm\*(Aq },
\&      ],
\&      ...
\&    );
.Ve
.Sp
Here a DateTime object and a loosely formatted date are passed as values.  Provided the Rose::DB::Object\-derived object method that services the \*(L"date\*(R" column can handle such values, they will be parsed and formatted as appropriate for the current database.
.Sp
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.
.Sp
The disadvantage is that all of this parsing and formatting is done for every query value, and that adds additional overhead to each call.
.Sp
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.
.IP "\fBselect \s-1COLUMNS\s0\fR" 4
.IX Item "select COLUMNS"
The names of the columns to select from the table.  \s-1COLUMNS\s0 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).
.IP "\fBsort_by [ \s-1CLAUSE\s0 | \s-1ARRAYREF\s0 ]\fR" 4
.IX Item "sort_by [ CLAUSE | ARRAYREF ]"
A fully formed \s-1SQL\s0 \*(L"\s-1ORDER\s0 \s-1BY\s0 ...\*(R" clause, sans the words \*(L"\s-1ORDER\s0 \s-1BY\s0\*(R", or a reference to an array of strings to be joined with a comma and appended to the \*(L"\s-1ORDER\s0 \s-1BY\s0\*(R" clause.
.Sp
If an item in the referenced array is itself a reference to a scalar, then that item will be dereferenced and passed through unmodified.
.IP "\fBtables \s-1TABLES\s0\fR" 4
.IX Item "tables TABLES"
A reference to an array of table names.  This argument is required.  A fatal error will occur if it is omitted.
.Sp
If more than one table is in the list, then each table is aliased to \*(L"tN\*(R", where N is an ascending number starting with 1.  The tables are numbered according to their order in \s-1TABLES\s0.  Example:
.Sp
.Vb 1
\&    $sql = build_select(tables => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq, \*(Aqbaz\*(Aq ], ...);
\&
\&    print $sql;
\&
\&    # SELECT ... FROM
\&    #   foo AS t1,
\&    #   bar AS t2,
\&    #   baz AS t3
\&    # ...
.Ve
.Sp
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 \*(L"tN_\*(R" prefix in a multi-table query.  Example:
.Sp
.Vb 10
\&    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
\&      ...
.Ve
.Sp
These unique aliases provide a technique of last resort for unambiguously addressing a column in a query clause.
.IP "\fBunique_aliases \s-1BOOL\s0\fR" 4
.IX Item "unique_aliases BOOL"
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.
.RE
.RS 4
.RE
.IP "\fBbuild_where_clause \s-1PARAMS\s0\fR" 4
.IX Item "build_where_clause PARAMS"
This works the same as the \fIbuild_select()\fR function, except that it only returns the \*(L"\s-1WHERE\s0\*(R" clause of the \s-1SQL\s0 query, sans the word \*(L"\s-1WHERE\s0\*(R" and prefixed with a single space.
.SH "AUTHOR"
.IX Header "AUTHOR"
John C. Siracusa (siracusa@gmail.com)
.SH "LICENSE"
.IX Header "LICENSE"
Copyright (c) 2010 by John C. Siracusa.  All rights reserved.  This program is
free software; you can redistribute it and/or modify it under the same terms
as Perl itself.