If a resultset is used in a numeric context it returns the "count". However, if it is used in a boolean context it is always true. So if you want to check if a resultset has any results, you must use if $rs != 0.

EXAMPLES

Chaining resultsets

Let's say you've got a query that needs to be run to return some data to the user. But, you have an authorization system in place that prevents certain users from seeing certain information. So, you want to construct the basic query in one method, but add constraints to it in another.

ResultSet subclassing with Moose and similar constructor-providers

Using Moose or Moo in your ResultSet classes is usually overkill, but you may find it useful if your ResultSets contain a lot of business logic (e.g. has xml_parser, has json, etc) or if you just prefer to organize your code via roles.

In order to write custom ResultSet classes with Moo you need to use the following template. The BUILDARGS is necessary due to the unusual signature of the constructor provided by DBIC ->new($source, \%args).

If you want to build your custom ResultSet classes with Moose, you need a similar, though a little more elaborate template in order to interface the inlining of the Moose-provided object constructor, with the DBIC one.

The MooseX::NonMoose is necessary so that the Moose constructor does not entirely overwrite the DBIC one (in contrast Moo does this automatically). Alternatively, you can skip MooseX::NonMoose and get by with just Moose instead by doing:

Please also keep in mind that many internals call "new_result" directly, so overloading this method with the idea of intercepting new result object creation will not work. See also warning pertaining to "create".

search_rs

This method does the same exact thing as search() except it will always return a resultset, even in list context.

search_literal

CAVEAT: search_literal is provided for Class::DBI compatibility and should only be used in that context. search_literal is a convenience method. It is equivalent to calling $schema->search(\[]), but if you want to ensure columns are bound correctly, use "search".

find

Finds and returns a single row based on supplied criteria. Takes either a hashref with the same format as "create" (including inference of foreign keys from related objects), or a list of primary key values in the same order as the primary columns declaration on the "result_source".

In either case an attempt is made to combine conditions already existing on the resultset with the condition passed to this method.

To aid with preparing the correct query for the storage you may supply the key attribute, which is the name of a unique constraint (the unique constraint corresponding to the primary columns is always named primary). If the key attribute has been supplied, and DBIC is unable to construct a query that satisfies the named unique constraint fully ( non-NULL values for each column member of the constraint) an exception is thrown.

If no key is specified, the search is carried over all unique constraints which are fully defined by the available condition.

If no such constraint is found, find currently defaults to a simple search->(\%column_values) which may or may not do what you expect. Note that this fallback behavior may be deprecated in further versions. If you need to search with arbitrary conditions - use "search". If the query resulting from this fallback produces more than one row, a warning to the effect is issued, though only the first row is constructed and returned as $result_object.

Note that if you have extra concerns about the correctness of the resulting query you need to specify the key attribute and supply the entire condition as an argument to find (since it is not always possible to perform the combination of the resultset condition with the supplied one, especially if the resultset condition contains literal sql).

single

Inflates the first result without creating a cursor if the resultset has any records in it; if not returns undef. Used by "find" as a lean version of "search".

While this method can take an optional search condition (just like "search") being a fast-code-path it does not recognize search attributes. If you need to add extra joins or similar, call "search" and then chain-call "single" on the DBIx::Class::ResultSet returned.

Note

As of 0.08100, this method enforces the assumption that the preceding query returns only one row. If more than one row is returned, you will receive a warning:

Query returned more than one row

In this case, you should be using "next" or "find" instead, or if you really know what you are doing, use the "rows" attribute to explicitly limit the size of the resultset.

This method will also throw an exception if it is called on a resultset prefetching has_many, as such a prefetch implies fetching multiple rows from the database in order to assemble the resulting object.

search_like

# WHERE title LIKE '%blue%'
$cd_rs = $rs->search_like({ title => '%blue%'});

Performs a search, but uses LIKE instead of = as the condition. Note that this is simply a convenience method retained for ex Class::DBI users. You most likely want to use "search" with specific operators.

result_source

An accessor for the primary ResultSource object from which this ResultSet is derived.

result_class

Arguments: $result_class?

Return Value: $result_class

An accessor for the class to use when creating result objects. Defaults to result_source->result_class - which in most cases is the name of the "table" class.

Note that changing the result_class will also remove any components that were originally loaded in the source class via load_components. Any overloaded methods in the original source class will not run.

first

Resets the resultset (causing a fresh query to storage) and returns an object for the first result (or undef if the resultset is empty).

update

Arguments: \%values

Return Value: $underlying_storage_rv

Sets the specified columns in the resultset to the supplied values in a single query. Note that this will not run any accessor/set_column/update triggers, nor will it update any result object instances derived from this resultset (this includes the contents of the resultset cache if any). See "update_all" if you need to execute any on-update triggers or cascades defined either by you or a result component.

The return value is a pass through of what the underlying storage backend returned, and may vary. See "execute" in DBI for the most common case.

update_all

delete

Arguments: none

Return Value: $underlying_storage_rv

Deletes the rows matching this resultset in a single query. Note that this will not run any delete triggers, nor will it alter the in_storage status of any result object instances derived from this resultset (this includes the contents of the resultset cache if any). See "delete_all" if you need to execute any on-delete triggers or cascades defined either by you or a result component.

The return value is a pass through of what the underlying storage backend returned, and may vary. See "execute" in DBI for the most common case.

populate

Accepts either an arrayref of hashrefs or alternatively an arrayref of arrayrefs.

NOTE

The context of this method call has an important effect on what is submitted to storage. In void context data is fed directly to fastpath insertion routines provided by the underlying storage (most often "execute_for_fetch" in DBI), bypassing the new and insert calls on the Result class, including any augmentation of these methods provided by components. For example if you are using something like DBIx::Class::UUIDColumns to create primary keys for you, you will find that your PKs are empty. In this case you will have to explicitly force scalar or list context in order to create those values.

In non-void (scalar or list) context, this method is simply a wrapper for "create". Depending on list or scalar context either a list of Result objects or an arrayref containing these objects is returned.

When supplying data in "arrayref of arrayrefs" invocation style, the first element should be a list of column names and each subsequent element should be a data value in the earlier specified column order. For example:

If you attempt a void-context multi-create as in the example above (each Artist also has the related list of CDs), and do not supply the necessary autoinc foreign key information, this method will proxy to the less efficient "create", and then throw the Result objects away. In this case there are obviously no benefits to using this method over "create".

Find an existing record from this resultset using "find". if none exists, instantiate a new result object and return it. The object will not be saved into your storage until you call "insert" in DBIx::Class::Row on it.

You most likely want this method when looking for existing rows using a unique constraint that is not the primary key, or looking for related rows.

Note: Make sure to read the documentation of "find" and understand the significance of the key attribute, as its lack may skew your search, and subsequently result in spurious new objects.

Note: Take care when using find_or_new with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to find_or_new, even when set to undef.

create

Attempt to create a single new row or a row with multiple related rows in the table represented by the resultset (and related tables). This will not check for duplicate rows before inserting, use "find_or_create" to do that.

To create one row for this resultset, pass a hashref of key/value pairs representing the columns of the table and the values you wish to store. If the appropriate relationships are set up, foreign key fields can also be passed an object representing the foreign row, and the value will be set to its primary key.

To create related objects, pass a hashref of related-object column values keyed on the relationship name. If the relationship is of type multi ("has_many" in DBIx::Class::Relationship) - pass an arrayref of hashrefs. The process will correctly identify columns holding foreign keys, and will transparently populate them from the keys of the corresponding relation. This can be applied recursively, and will work correctly for a structure with an arbitrary depth and width, as long as the relationships actually exists and the correct column data has been supplied.

Instead of hashrefs of plain related data (key/value pairs), you may also pass new or inserted objects. New objects (not inserted yet, see "new_result"), will be inserted into their appropriate tables.

When subclassing ResultSet never attempt to override this method. Since it is a simple shortcut for $self->new_result($attrs)->insert, a lot of the internals simply never call it, so your override will be bypassed more often than not. Override either "new" in DBIx::Class::Row or "insert" in DBIx::Class::Row depending on how early in the "create" process you need to intervene. See also warning pertaining to "new".

Note: Make sure to read the documentation of "find" and understand the significance of the key attribute, as its lack may skew your search, and subsequently result in spurious row creation.

Note: Because find_or_create() reads from the database and then possibly inserts based on the result, this method is subject to a race condition. Another process could create a record in the table after the find has completed and before the create has started. To avoid this problem, use find_or_create() inside a transaction.

Note: Take care when using find_or_create with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to find_or_create, even when set to undef.

Note: Make sure to read the documentation of "find" and understand the significance of the key attribute, as its lack may skew your search, and subsequently result in spurious row creation.

Note: Take care when using update_or_create with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to update_or_create, even when set to undef.

Note: Make sure to read the documentation of "find" and understand the significance of the key attribute, as its lack may skew your search, and subsequently result in spurious new objects.

Note: Take care when using update_or_new with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to update_or_new, even when set to undef.

set_cache

Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset. Note that if the cache is set, the resultset will return the cached objects rather than re-querying the database even if the cache attr is not set.

The contents of the cache can also be populated by using the "prefetch" attribute to "search".

related_resultset

current_source_alias

Arguments: none

Return Value: $source_alias

Returns the current table alias for the result source this resultset is built on, that will be used in the SQL query. Usually it is me.

Currently the source alias that refers to the result set returned by a "search"/"find" family method depends on how you got to the resultset: it's me by default, but eg. "search_related" aliases it to the related result source name (and keeps me referring to the original result set). The long term goal is to make DBIx::Class always alias the current resultset as me (and make this method unnecessary).

as_subselect_rs

Act as a barrier to SQL symbols. The resultset provided will be made into a "virtual view" by including it as a subquery within the from clause. From this point on, any joined tables are inaccessible to ->search on the resultset (as if it were simply where-filtered without joins). For example:

my $rs = $schema->resultset('Bar')->search({'x.name' => 'abc'},{ join => 'x' });
# 'x' now pollutes the query namespace
# So the following works as expected
my $ok_rs = $rs->search({'x.other' => 1});
# But this doesn't: instead of finding a 'Bar' related to two x rows (abc and
# def) we look for one row with contradictory terms and join in another table
# (aliased 'x_2') which we never use
my $broken_rs = $rs->search({'x.name' => 'def'});
my $rs2 = $rs->as_subselect_rs;
# doesn't work - 'x' is no longer accessible in $rs2, having been sealed away
my $not_joined_rs = $rs2->search({'x.other' => 1});
# works as expected: finds a 'table' row related to two x rows (abc and def)
my $correctly_joined_rs = $rs2->search({'x.name' => 'def'});

Another example of when one might use this would be to select a subset of columns in a group by clause:

The old scalarref syntax (i.e. order_by => \'year DESC') is still supported, although you are strongly encouraged to use the hashref syntax as outlined above.

columns

Value: \@columns | \%columns | $column

Shortcut to request a particular set of columns to be retrieved. Each column spec may be a string (a table column name), or a hash (in which case the key is the as value, and the value is used as the select expression). Adds the "current_source_alias" onto the start of any column without a . in it and sets select from that, then auto-populates as from select as normal. (You may also use the cols attribute, as in earlier versions of DBIC, but this is deprecated)

If you want to individually retrieve related columns (in essence perform manual "prefetch") you have to make sure to specify the correct inflation slot chain such that it matches existing relationships:

+columns

NOTE: You MUST explicitly quote '+columns' when using this attribute. Not doing so causes Perl to incorrectly interpret +columns as a bareword with a unary plus operator before it, which is the same as simply columns.

Value: \@extra_columns

Indicates additional columns to be selected from storage. Works the same as "columns" but adds columns to the current selection. (You may also use the include_columns attribute, as in earlier versions of DBIC, but this is deprecated)

would return all CDs and include a 'name' column to the information passed to object inflation. Note that the 'artist' is the name of the column (or relationship) accessor, and 'name' is the name of the column accessor in the related table.

select

Value: \@select_columns

Indicates which columns should be selected from the storage. You can use column names, or in the case of RDBMS back ends, function or stored procedure names:

NOTE: You will almost always need a corresponding "as" attribute when you use "select", to instruct DBIx::Class how to store the result of the column.

Also note that the "as" attribute has nothing to do with the SQL-side AS identifier aliasing. You can alias a function (so you can use it e.g. in an ORDER BY clause), however this is done via the -asselect function attribute supplied as shown in the example above.

+select

NOTE: You MUST explicitly quote '+select' when using this attribute. Not doing so causes Perl to incorrectly interpret +select as a bareword with a unary plus operator before it, which is the same as simply select.

Value: \@extra_select_columns

Indicates additional columns to be selected from storage. Works the same as "select" but adds columns to the current selection, instead of specifying a new explicit list.

as

Value: \@inflation_names

Indicates DBIC-side names for object inflation. That is "as" indicates the slot name in which the column value will be stored within the Row object. The value will then be accessible via this identifier by the get_column method (or via the object accessor if one with the same name already exists) as shown below.

The "as" attribute has nothing to do with the SQL-side identifier aliasing AS. See "select" for details.

+as

NOTE: You MUST explicitly quote '+as' when using this attribute. Not doing so causes Perl to incorrectly interpret +as as a bareword with a unary plus operator before it, which is the same as simply as.

You need to use the relationship (not the table) name in conditions, because they are aliased as such. The current table is aliased as "me", so you need to use me.column_name in order to avoid ambiguity. For example:

will return a set of all artists that have both a cd with title 'Down to Earth' and a cd with title 'Popular'.

If you want to fetch related objects from other tables as well, see "prefetch" below.

NOTE: An internal join-chain pruner will discard certain joins while
constructing the actual SQL query, as long as the joins in question do not
affect the retrieved result. This for example includes 1:1 left joins
that are not part of the restriction specification (WHERE/HAVING) nor are
a part of the query selection.

Will return only as many objects as there are rows in the CD source, even though the result of the query may span many rows. Each of these CD objects will in turn have multiple "Track" objects hidden behind the has_many generated accessor tracks. Without collapse => 1, the return values of this resultset would be as many CD objects as there are tracks (a "Cartesian product"), with each CD object containing exactly one of all fetched Track data.

When a collapse is requested on a non-ordered resultset, an order by some unique part of the main source (the left-most table) is inserted automatically. This is done so that the resultset is allowed to be "lazy" - calling $rs->next will fetch only as many rows as it needs to build the next object with all of its related data.

If an "order_by" is already declared, and orders the resultset in a way that makes collapsing as described above impossible (e.g. ORDER BY has_many_rel.column or ORDER BY RANDOM()), DBIC will automatically switch to "eager" mode and slurp the entire resultset before constructing the first object returned by "next".

Setting this attribute on a resultset that does not join any has_many relations is a no-op.

prefetch

Value: ($rel_name | \@rel_names | \%rel_names)

This attribute is a shorthand for specifying a "join" spec, adding all columns from the joined related sources as "+columns" and setting "collapse" to a true value. It can be thought of as a rough superset of the "join" attribute.

When you have a paged resultset, "count" will only return the number of rows in the page. To get the total, use the "pager" and call total_entries on it.

rows

Value: $rows

Specifies the maximum number of rows for direct retrieval or the number of rows per page if the page attribute or method is used.

offset

Value: $offset

Specifies the (zero-based) row number for the first row to be returned, or the of the first row of the first page if paging is used.

software_limit

Value: (0 | 1)

When combined with "rows" and/or "offset" the generated SQL will not include any limit dialect stanzas. Instead the entire result will be selected as if no limits were specified, and DBIC will perform the limit locally, by artificially advancing and finishing the resulting "cursor".

This is the recommended way of performing resultset limiting when no sane RDBMS implementation is available (e.g. Sybase ASE using the Generic Sub Query hack)

group_by

Value: \@columns

A arrayref of columns to group by. Can include columns of joined tables.

group_by => [qw/ column1 column2 ... /]

having

Value: $condition

The HAVING operator specifies a secondary condition applied to the set after the grouping calculations have been done. In other words it is a constraint just like "where" (and accepting the same SQL::Abstract syntax) applied to the data as it exists after GROUP BY has taken place. Specifying "having" without "group_by" is a logical mistake, and a fatal error on most RDBMS engines.

for

Set to 'update' for a SELECT ... FOR UPDATE or 'shared' for a SELECT ... FOR SHARED. If \$scalar is passed, this is taken directly and embedded in the query.

PREFETCHING

DBIx::Class supports arbitrary related data prefetching from multiple related sources. Any combination of relationship types and column sets are supported. If collapsing is requested, there is an additional requirement of selecting enough data to make every individual object uniquely identifiable.

Here are some more involved examples, based on the following relationship map:

DBIx::Class has no need to go back to the database when we access the cd or artist relationships, which saves us two SQL statements in this case.

Simple prefetches will be joined automatically, so there is no need for a join attribute in the above search.

The "prefetch" attribute can be used with any of the relationship types and multiple prefetches can be specified together. Below is a more complex example that prefetches a CD's artist, its liner notes (if present), the cover image, the tracks on that CD, and the guests on those tracks.

Now the artist, record_label, liner_note, cover_image, tracks, and guests of the CD will all be available through the relationship accessors without the need for additional queries to the database.

CAVEATS

Prefetch does a lot of deep magic. As such, it may not behave exactly as you might expect.

Prefetch uses the "cache" to populate the prefetched relationships. This may or may not be what you want.

If you specify a condition on a prefetched relationship, ONLY those rows that match the prefetched condition will be fetched into that relationship. This means that adding prefetch to a search() may alter what is returned by traversing a relationship. So, if you have Artist->has_many(CDs) and you do

That cmp_ok() may or may not pass depending on the datasets involved. In other words the WHERE condition would apply to the entire dataset, just like it would in regular SQL. If you want to add a condition only to the "right side" of a LEFT JOIN - consider declaring and using a relationship with a custom condition

DBIC BIND VALUES

Because DBIC may need more information to bind values than just the column name and value itself, it uses a special format for both passing and receiving bind values. Each bind value should be composed of an arrayref of [ \%args => $val ]. The format of \%args is currently:

dbd_attrs

If present (in any form), this is what is being passed directly to bind_param. Note that different DBD's expect different bind args. (e.g. DBD::SQLite takes a single numerical type, while DBD::Pg takes a hashref if bind options.)

If this is specified, all other bind options described below are ignored.

sqlt_datatype

If present, this is used to infer the actual bind attribute by passing to $resolved_storage->bind_attribute_by_data_type(). Defaults to the "data_type" from the add_columns column info.

Note that the data type is somewhat freeform (hence the sqlt_ prefix); currently drivers are expected to "Do the Right Thing" when given a common datatype name. (Not ideal, but that's what we got at this point.)

sqlt_size

Currently used to correctly allocate buffers for bind_param_inout(). Defaults to "size" from the add_columns column info, or to a sensible value based on the "data_type".

dbic_colname

Used to fill in missing sqlt_datatype and sqlt_size attributes (if they are explicitly specified they are never overridden). Also used by some weird DBDs, where the column name should be available at bind_param time (e.g. Oracle).

For backwards compatibility and convenience, the following shortcuts are supported: