NAME

DBIx::RoboQuery::ResultSet - Configure the results to get what you want

VERSION

version 0.030

SYNOPSIS

  my $resultset = DBIx::RoboQuery::ResultSet->new($query, {opt => 'val'});
  # or use $query->resultset

  $resultset->execute;
  my @columns = $resultset->columns;

  my $records = $resultset->hash; # like DBI/fetchall_hashref
  # or $resultset->array          # like DBI/fetchall_arrayref

DESCRIPTION

This is the companion to DBIx::RoboQuery. Most things about the ResultSet can be configured on the query. The ResultSet provides easy access to information about the query and results.

See "SYNOPSIS" in DBIx::RoboQuery for a more thorough example.

METHODS

new

  DBIx::RoboQuery::ResultSet->new($query, opt => 'val');

  # Can also be instantiated from a query object:
  DBIx::RoboQuery->new(sql => $sql)->resultset(opt => 'val');

The first argument should be a DBIx::RoboQuery instance.

The remaining arguments should be a hash or hashref of options. These options will be checked in the passed hash[ref] first. If they do not exist, they will be looked for on the query object.

  my $dbh = DBI->connect();
  $query = DBIx::RoboQuery->new(sql => $sql, dbh => $dbh);

  # These two invocations will produce the same result:
  # The 1st call sets 'dbh' explicitly.
  # The 2nd call will find the 'dbh' attribute on $query.

  DBIx::RoboQuery::ResultSet->new($query, dbh => $dbh);
  DBIx::RoboQuery::ResultSet->new($query);

See "new" in DBIx::RoboQuery for the list of options.

NOTE: The constructor will call $query->sql() (and cache the result) to ensure that the query is fully configured before copying some of its attributes to the new resultset object.

array

  my $array_of_hashes = $resultset->array;
  # or an array of arrays if 'default_slice' is set to an arrayref

Calls fetchall_arrayref(@_) on the DBI statement handle (passing any supplied arguments).

Like fetchall_arrayref, this method will take a slice as the first argument.

* NOTE * : Unlike fetchall_arrayref, With no arguments, or if the first argument is undefined, the method will act as if passed an empty hash ref.

To send the maximum number of desired rows it must be passed as the second argument.

  $resultset->array();        # default is an array of hashrefs
  $resultset->array({});      # same as above
  $resultset->array([]);      # array of arrays
  $resultset->array([0]);     # array of arrays with only first column
  $resultset->array({k=>1});  # array of hashes with only column 'k'

  $resultset->array({}, 5);   # array of hashrefs,  no more than 5
  $resultset->array([], 5);   # array of arrayrefs, no more than 5

To Reiterate : This method takes the same two possible arguments as "fetchall_arrayref" in DBI. However, if no arguments are supplied, an empty {} will be sent to fetchall_arrayref to make it return an array of hash refs.

If this deviation is undesired, you can set default_slice to [] to return to the DBI default. Like many options this can be set on the query or the resultset.

  DBIx::RoboQuery->new(default_slice => [], %opts);

bound_params

See "bound_params" in DBIx::RoboQuery.

bound_values

See "bound_values" in DBIx::RoboQuery.

columns

  my @columns = $resultset->columns;

Return the columns of the result set.

This includes key and non-key columns and excludes dropped columns.

This is only useful after the query has been executed (and will croak if it has not been).

drop_columns

  my @columns = $resultset->drop_columns;

Return a list of the column names being dropped (ignored) from the result set.

execute

  $resultset->execute();

Execute the query against the dbh.

hash

  my $hash_of_hashes = $resultset->hash;

Returns a tree of hashrefs like "fetchall_hashref" in DBI.

Records will be stored (and considered unique) according to the key_columns attribute. If more than one record has the same values for key_columns the last record from the database will be returned.

The preferences attribute can be used to determine which record to select instead of simply the last one received. See "preference" for more information, or "prefer" in DBIx::RoboQuery for how to write and store the preference rules.

An error is thrown if key_columns is empty. "fetchall_hashref" in DBI doesn't check the length of key_columns. An empty array ends up returning a single hash (the last row) instead of the hash tree which can be very confusing and surely is not desired. There are more efficient ways to get the last row if that's really all you want.

key_columns

  my @keys = $resultset->key_columns;

Return a list of the primary key columns from the query.

The key_columns attribute should be set on the query object. This read-only accessor is provided here for convenience and consistency with the other column attributes.

non_key_columns

  my @other = $resultset->non_key_columns;

Return a list of the other columns from the query.

Excludes key columns and dropped columns.

preference

  $resultset->preference($record1, $record2);

This is used internally by the "hash" method to determine which record it should choose when multiple records have the same key value(s).

When "fetchall_hashref" in DBI encounters multiple records having the same key field(s), the last encountered record is the one saved to the hash and returned.

This "last one in wins" logic is preserved in this method for any records that cannot be determined by the specified preference rules.

See "prefer" in DBIx::RoboQuery for details on specifying record preferences.

query

  my $query = $resultset->query;

Returns the query object (in case you lost it).

row_count

Returns the number of rows returned via "array" or "hash". It will return -1 until after one of those methods have been called.

For "array" this is the same as scalar @$rows.

For "hash" it is the number of (non-duplicate) rows (which would be harder to count manually from the hash tree).

times

Returns a hashref of timing info (the length of time each operation took in fractional seconds).

Keys include:

• prepare - Prepare the statement

• execute - Execute the statement

• fetch - Fetch (and transform) all the records

• total - Sum of all times

CAVEATS

While there is some error checking, the module probably assumes you're setting "RaiseError" in DBI to true on your dbh.

If you don't use "RaiseError" in DBI, and you experience problems, please let me know (submit a patch or a bug report).

TODO

• Evaluate preference for "array" if key_columns is set?

AUTHOR

Randy Stauner <rwstauner@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2010 by Randy Stauner.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm DBIx::RoboQuery

perl -MCPAN -e shell
install DBIx::RoboQuery