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)

DESCRIPTION

A Query object represents rows from a database (from one or more tables). This module makes it easy, not only to fetch and use the data in the returned rows, but also to modify the query to return a different result set.

METHODS

`new`

  DBIx::DBO::Query->new($dbo, $table1, ...);
  # or
  $dbo->query($table1, ...);

Create a new Query object from the tables specified. In scalar context, just the Query object will be returned. In list context, the Query object and Table objects will be returned for each table specified. Tables can be specified with the same arguments as "new" in DBIx::DBO::Table or another Query can be used as a subquery.

  my($query, $table1, $table2) = DBIx::DBO::Query->new($dbo, 'customers', ['history', 'transactions']);

You can also pass in a Query instead of a Table to use that query as a subquery.

  my $subquery = DBIx::DBO::Query->new($dbo, 'history.transactions');
  my $query = DBIx::DBO::Query->new($dbo, 'customers', $subquery);
  # SELECT * FROM customers, (SELECT * FROM history.transactions) t1;

`reset`

  $query->reset;

Reset the query, start over with a clean slate. Resets the columns to return, removes all the WHERE, DISTINCT, HAVING, LIMIT, GROUP BY & ORDER BY clauses.

NB: This will not remove the JOINs or JOIN ON clauses.

`tables`

Return a list of Table or Query objects that appear in the FROM clause for this query.

`columns`

Return a list of column names that will be returned by fetch.

`column`

  $query->column($alias_or_column_name);
  $query ** $column_name;

Returns a reference to a column for use with other methods. The ** method is a shortcut for the column method.

`show`

  $query->show(@columns);
  $query->show($table1, { COL => $table2 ** 'name', AS => 'name2' });
  $query->show($table1 ** 'id', { FUNC => 'UCASE(?)', COL => 'name', AS => 'alias' }, ...

List which columns to return when we fetch. If called without arguments all columns will be shown, SELECT * .... If you use a Table object, all the columns from that table will be shown, SELECT table.* ... You can also add a subquery by passing that Query as the value with an alias, Eg.

  $query->show({ VAL => $subquery, AS => 'sq' }, ...);
  # SELECT ($subquery_sql) AS sq ...

`distinct`

  $query->distinct(1);

Takes a boolean argument to add or remove the DISTINCT clause for the returned rows.

`join_table`

  $query->join_table($table, $join_type);

Join a table onto the query, creating a Table object if needed. This will perform a comma (", ") join unless $join_type is specified.

Tables can be specified with the same arguments as "new" in DBIx::DBO::Table or another Query can be used as a subquery.

Valid join types are any accepted by the DB. Eg: 'JOIN', 'LEFT', 'RIGHT', undef (for comma join), 'INNER', 'OUTER', ...

Returns the Table or Query object added.

`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".

`open_join_on_bracket`, `close_join_on_bracket`

  $query->open_join_on_bracket($table, 'OR');
  $query->join_on(...
  $query->close_join_on_bracket($table);

Equivalent to open_bracket, but for the JOIN ON clause. The first argument is the table being joined onto.

`where`

Restrict the query with the condition specified (WHERE clause).

  $query->where($expression1, $operator, $expression2);

$operator is one of: '=', '<>', '<', '>', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'BETWEEN', 'NOT BETWEEN', ...

$expressions can be any of the following:

A scalar value: 123 or 'hello' (or for $expression1 a column name: 'id')
```
  $query->where('name', '<>', 'John');
```
A scalar reference: \"22 * 3" (These are passed unquoted in the SQL statement!)
```
  $query->where(\'CONCAT(id, name)', '=', \'"22John"');
```
An array reference: [1, 3, 5] (Used with IN and BETWEEN etc)
```
  $query->where('id', 'NOT IN', [21, 22, 25, 39]);
```

A Column object: $table ** 'id' or $table->column('id')

  $query->where($table1 ** 'id', '=', $table2 ** 'id');

A Query object, to be used as a subquery.
```
  $query->where('id', '>', $subquery);
```
A hash reference: see "Complex_expressions"

Multiple where expressions are combined cleverly using the preferred aggregator 'AND' (unless open_bracket was used to change this). So that when you add where expressions to the query, they will be ANDed together. However some expressions that refer to the same column will automatically be ORed instead where this makes sense, currently: '=', 'IS NULL', '<=>', 'IN' and 'BETWEEN'. Similarly, when the preferred aggregator is 'OR' the following operators will be ANDed together: '!=', 'IS NOT NULL', '<>', 'NOT IN' and 'NOT BETWEEN'.

  $query->where('id', '=', 5);
  $query->where('name', '=', 'Bob');
  $query->where('id', '=', 7);
  $query->where(...
  # Produces: WHERE ("id" = 5 OR "id" = 7) AND "name" = 'Bob' AND ...

`unwhere`

  $query->unwhere();
  $query->unwhere($column);

Removes all previously added "where" restrictions for a column. If no column is provided, the whole WHERE clause is removed.

`open_bracket`, `close_bracket`

  $query->open_bracket('OR');
  $query->where( ...
  $query->where( ...
  $query->close_bracket;

Used to group where expressions together in parenthesis using either 'AND' or 'OR' as the preferred aggregator. All the where calls made between open_bracket and close_bracket will be inside the parenthesis.

Without any parenthesis 'AND' is the preferred aggregator.

`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.

`having`

Restrict the query with the condition specified (HAVING clause). This takes the same arguments as "where".

  $query->having($expression1, $operator, $expression2);

`unhaving`

  $query->unhaving();
  $query->unhaving($column);

Removes all previously added "having" restrictions for a column. If no column is provided, the whole HAVING clause is removed.

`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.

NB. Oracle does not support pagging prior to version 12c, so this has been implemented in software, , but if an offset is given, an extra column "_DBO_ROWNUM_" is added to the Query to achieve this. TODO: Implement the new "FIRST n / NEXT n" clause if connected to a 12c database.

`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 Row object or undefined if there are no more rows.

`row`

  my $row = $query->row;

Returns the Row object for the current row from the query or an empty 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 the number is unknown. This uses the DBI rows method which is unreliable in some situations (See DBI->rows).

`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.

`sql`

  my $sql = $query->sql;

Returns the SQL statement string.

`update`

  $query->update(department => 'Tech');
  $query->update(salary => { FUNC => '? * 1.10', COL => 'salary' }); # 10% raise

Updates every row in the query with the new values specified. Returns the number of rows updated or '0E0' for no rows to ensure the value is true, and returns false if there was an error.

`finish`

  $query->finish;

Calls DBI->finish on the statement handle, if it's active. Restarts cached queries from the first row (if created using the CacheQuery config). This ensures that the next call to "fetch" will return the first row from the query.

Common Methods

These methods are accessible from all DBIx::DBO* objects.

`dbo`

The DBO object.

`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.

`config`

  $query_setting = $query->config($option);
  $query->config($option => $query_setting);

Get or set this Query object's config settings. When setting an option, the previous value is returned. When getting an option's value, if the value is undefined, the DBIx::DBO's value is returned.

See: "Available_config_options" in DBIx::DBO.

Complex expressions

More complex expressions can be passed as hash references. These expressions can be used in the "show", "join_on", "where", "having", "group_by" and "order_by" methods.

  $query->show({ FUNC => 'SUBSTR(?, 1, 1)', COL => 'name', AS => 'initial' });
  # MySQL would produce:  SELECT SUBSTR(`name`, 1, 1) AS `initial` FROM ...
  
  $query->where({ FUNC => "CONCAT(COALESCE(?, 'Mr.'), ' ', ?)", VAL => [$title, $t ** 'name'] }, '=', 'Dr. Jones');
  # MySQL would produce:  ... WHERE CONCAT(COALESCE(?, 'Mr.'), ' ', `name`) = 'Dr. Jones' ...
  
  $query->order_by('id', { FUNC => "COALESCE(?,'?')", COL => 'name', ORDER => 'DESC' });
  # MySQL would produce:  ... ORDER BY `id`, COALESCE(`name`,'?') DESC

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 inserted unquoted into the SQL, possibly containing ? placeholders.
COLLATE => The collation for this value/field.
ORDER => To order by a column (Used only in group_by and order_by).

SUBCLASSING

Classes can easily be created for tables in your database. Assume you want to create a Query and Row class for a "Users" table:

  package My::Users;
  our @ISA = qw(DBIx::DBO::Query);
  
  sub new {
      my($class, $dbo) = @_;
      
      # Create the Query for the "Users" table
      my $self = $class->SUPER::new($dbo, 'Users');
      
      # We could even add some JOINs or other clauses here ...
      
      return $self;
  }
  
  sub _row_class { 'My::User' } # Rows are blessed into this class


  package My::User;
  our @ISA = qw(DBIx::DBO::Row);
  
  sub new {
      my($class, $dbo, $parent) = @_;
      
      $parent ||= My::Users->new($dbo); # The Row will use the same table as it's parent
      
      $class->SUPER::new($dbo, $parent);
  }

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

METHODS

new

reset

tables

columns

column

show

distinct

join_table

join_on

open_join_on_bracket, close_join_on_bracket

where

unwhere

open_bracket, close_bracket

group_by

having

unhaving

order_by

limit

arrayref

hashref

col_arrayref

fetch

row

run

rows

count_rows

found_rows

sql

update

finish