NAME
DBIx::DBO::Query - An OO interface to SQL queries and results. Encapsulates an entire query in an object.
SYNOPSIS
# Create a Query object by JOINing 2 tables
my $query = $dbo->query('my_table', 'my_other_table');
# Get the Table objects from the query
my ($table1, $table2) = $query->tables;
# Add a JOIN ON clause
$query->join_on($table1 ** 'login', '=', $table2 ** 'username');
# Find our ancestors, and order by age (oldest first)
$query->where('name', '=', 'Adam');
$query->where('name', '=', 'Eve');
$query->order_by({ COL => 'age', ORDER => 'DESC' });
# New Query using a LEFT JOIN
($query, $table1) = $dbo->query('my_table');
$table2 = $query->join_table('another_table', 'LEFT');
$query->join_on($table1 ** 'parent_id', '=', $table2 ** 'child_id');
# Find those not aged between 20 and 30.
$query->where($table1 ** 'age', '<', 20, FORCE => 'OR'); # Force OR so that we get: (age < 20 OR age > 30)
$query->where($table1 ** 'age', '>', 30, FORCE => 'OR'); # instead of the default: (age < 20 AND age > 30)METHODS
dbh
The read-write DBI handle.
rdbh
The read-only DBI handle, or if there is no read-only connection, the read-write DBI handle.
do
$dbo->do($statement) or die $dbo->dbh->errstr;
$dbo->do($statement, \%attr) or die $dbo->dbh->errstr;
$dbo->do($statement, \%attr, @bind_values) or die ...This provides access to DBI->do method. It defaults to using the read-write DBI handle.
join_table
$query->join_table($table, $join_type);
$query->join_table([$schema, $table], $join_type);
$query->join_table($table_object, $join_type);Join a table onto the query, creating a DBIx::DBO::Table object if needed. This will perform a comma (", ") join unless $join_type is specified.
Returns the Table object.
tables
Return a list of DBIx::DBO::Table objects for this query.
show
$query->show(@columns);
$query->show($table1 ** 'id', {FUNC => 'UCASE(?)', COL => 'name', AS => 'NAME'}, ...Specify which columns to show as an array. If the array is empty all columns will be shown.
join_on
$query->join_on($table_object, $expression1, $operator, $expression2);
$query->join_on($table2, $table1 ** 'id', '=', $table2 ** 'id');Join tables on a specific WHERE clause. The first argument is the table object being joined onto. Then a JOIN ON condition follows, which uses the same arguments as "where".
where
Restrict the query with the condition specified (WHERE clause).
$query->where($expression1, $operator, $expression2);
$query->where($table1 ** 'id', '=', $table2 ** 'id');$operator is one of: '=', '<', '>', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', ...
$expression can be any of the following:
A SCALAR value: C<123> or C<'hello'>
A SCALAR reference: C<\"22 * 3"> (These are passed unqouted in the SQL statement!)
An ARRAY reference: C<[1, 3, 5]> (Used with C<IN> and C<BETWEEN> etc)
A Table Column: C<$table ** 'id'> or C<$table->column('id')>
A Hash reference: (Described below)For a more complex where clause the expression can be passed as a hash reference. Possibly containing SCALARs, ARRAYs or Table Columns.
$query->where('name', '=', { FUNC => 'COALESCE(?,?)', VAL => [$name, 'Unknown'] });
$query->where('string', '=', { FUNC => "CONCAT('Mr. ',?)", COL => 'name' });The keys to the hash in a complex expression are:
VAL A scalar, scalar reference or an array reference.
COL The name of a column or a Column object.
AS An alias name.
FUNC A string to be inseted into the SQL, possibly containing "?" placeholders.
ORDER To order by a column.unwhere
$query->unwhere();
$query->unwhere($column);
$query->unwhere($table1 ** 'id');Removes all previously added where() restrictions for a column. If no column is provided, ALL where() restrictions are removed.
group_by
$query->group_by('column', ...);
$query->group_by($table ** 'column', ...);
$query->group_by({ COL => $table ** 'column', ORDER => 'DESC' }, ...);Group the results by the column(s) listed. This will replace the GROUP BY clause. To remove the GROUP BY clause simply call group_by without any columns.
order_by
$query->order_by('column', ...);
$query->order_by($table ** 'column', ...);
$query->order_by({ COL => $table ** 'column', ORDER => 'DESC' }, ...);Order the results by the column(s) listed. This will replace the ORDER BY clause. To remove the ORDER BY clause simply call order_by without any columns.
limit
$query->limit;
$query->limit($rows);
$query->limit($rows, $offset);Limit the maximum number of rows returned to $rows, optionally skipping the first $offset rows. When called without arguments or if $rows is undefined, the limit is removed.
arrayref
$query->arrayref;
$query->arrayref(\%attr);Run the query using DBI->selectall_arrayref which returns the result as an arrayref. You can specify a slice by including a 'Slice' or 'Columns' attribute in %attr - See DBI->selectall_arrayref.
hashref
$query->hashref($key_field);
$query->hashref($key_field, \%attr);Run the query using DBI->selectall_hashref which returns the result as an hashref. $key_field defines which column, or columns, are used as keys in the returned hash.
col_arrayref
$query->col_arrayref;
$query->col_arrayref(\%attr);Run the query using DBI->selectcol_arrayref which returns the result as an arrayref of the values of each row in one array. By default it pushes all the columns requested by the "show" method onto the result array (this differs from the DBI). Or to specify which columns to include in the result use the 'Columns' attribute in %attr - see DBI->selectcol_arrayref.
fetch
my $row = $query->fetch;Fetch the next row from the query. This will run/rerun the query if needed.
Returns a DBIx::DBO::Row object or undefined if there are no more rows.
row
my $row = $query->row;Returns the DBIx::DBO::Row object for the current row from the query or an empty DBIx::DBO::Row object if there is no current row.
run
$query->run;Run/rerun the query. This is called automatically before fetching the first row.
rows
my $row_count = $query->rows;Count the number of rows returned. Returns undefined if there is an error or the number is unknown.
count_rows
my $row_count = $query->count_rows;Count the number of rows that would be returned. Returns undefined if there is an error.
found_rows
$query->config(CalcFoundRows => 1); # Only applicable to MySQL
my $total_rows = $query->found_rows;Return the number of rows that would have been returned if there was no limit clause. Before runnning the query the CalcFoundRows config option can be enabled for improved performance on supported databases.
Returns undefined if there is an error or is unable to determine the number of found rows.
sth
my $sth = $query->sth;Reutrns the DBI statement handle from the query. This will run/rerun the query if needed.
finish
$query->finish;Calls DBI->finish on the statement handle, if it's active.
sql
my $sql = $query->sql;Returns the SQL query statement string.
config
$query_setting = $dbo->config($option)
$dbo->config($option => $query_setting)Get or set this DBIx::DBO::Query config settings. When setting an option, the previous value is returned.