The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Class-0.05006
River stage three • 342 direct dependents • 452 total dependents
289 ++
/ DBIx::Class::ResultSet

NAME

DBIx::Class::ResultSet - Responsible for fetching and creating resultset.

SYNOPSIS

  my $rs   = $schema->resultset('User')->search(registered => 1);
  my @rows = $schema->resultset('Foo')->search(bar => 'baz');

DESCRIPTION

The resultset is also known as an iterator. It is responsible for handling queries that may return an arbitrary number of rows, e.g. via "search" or a has_many relationship.

In the examples below, the following table classes are used:

  package MyApp::Schema::Artist;
  use base qw/DBIx::Class/;
  __PACKAGE__->table('artist');
  __PACKAGE__->add_columns(qw/artistid name/);
  __PACKAGE__->set_primary_key('artistid');
  __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
  1;

  package MyApp::Schema::CD;
  use base qw/DBIx::Class/;
  __PACKAGE__->table('artist');
  __PACKAGE__->add_columns(qw/cdid artist title year/);
  __PACKAGE__->set_primary_key('cdid');
  __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
  1;

METHODS

new($source, \%$attrs)

The resultset constructor. Takes a source object (usually a DBIx::Class::ResultSourceProxy::Table) and an attribute hash (see "ATRRIBUTES" below). Does not perform any queries -- these are executed as needed by the other methods.

Generally you won't need to construct a resultset manually. You'll automatically get one from e.g. a "search" called in scalar context:

  my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
  my @obj    = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
  my $new_rs = $rs->search({ foo => 3 });

If you need to pass in additional attributes but no additional condition, call it as search({}, \%attrs);.

  # "SELECT foo, bar FROM $class_table"
  my @all = $class->search({}, { cols => [qw/foo bar/] });

search_literal

  my @obj    = $rs->search_literal($literal_where_cond, @bind);
  my $new_rs = $rs->search_literal($literal_where_cond, @bind);

Pass a literal chunk of SQL to be added to the conditional part of the resultset.

find(@colvalues), find(\%cols, \%attrs?)

Finds a row based on its primary key or unique constraint. For example:

  my $cd = $schema->resultset('CD')->find(5);

Also takes an optional key attribute, to search by a specific key or unique constraint. For example:

  my $cd = $schema->resultset('CD')->find_or_create(
    {
      artist => 'Massive Attack',
      title  => 'Mezzanine',
    },
    { key => 'artist_title' }
  );

See also "find_or_create" and "update_or_create".

  $rs->search_related('relname', $cond?, $attrs?);

Search the specified relationship. Optionally specify a condition for matching records.

cursor

Returns a storage-driven cursor to the given resultset.

search_like

Perform a search, but use LIKE instead of equality as the condition. Note that this is simply a convenience method; you most likely want to use "search" with specific operators.

For more information, see DBIx::Class::Manual::Cookbook.

slice($first, $last)

Returns a subset of elements from the resultset.

next

Returns the next element in the resultset (undef is there is none).

Can be used to efficiently iterate over records in the resultset:

  my $rs = $schema->resultset('CD')->search({});
  while (my $cd = $rs->next) {
    print $cd->title;
  }

result_source

Returns a reference to the result source for this recordset.

count

Performs an SQL COUNT with the same query as the resultset was built with to find the number of elements. If passed arguments, does a search on the resultset and counts the results of that.

Note: When using count with group_by, DBIX::Class emulates GROUP BY using COUNT( DISTINCT( columns ) ). Some databases (notably SQLite) do not support DISTINCT with multiple columns. If you are using such a database, you should only use columns from the main table in your group_by clause.

count_literal

Calls "search_literal" with the passed arguments, then "count".

all

Returns all elements in the resultset. Called implictly if the resultset is returned in list context.

reset

Resets the resultset's cursor, so you can iterate through the elements again.

first

Resets the resultset and returns the first element.

update(\%values)

Sets the specified columns in the resultset to the supplied values.

update_all(\%values)

Fetches all objects and updates them one at a time. Note that update_all will run cascade triggers while "update" will not.

delete

Deletes the contents of the resultset from its result source.

delete_all

Fetches all objects and deletes them one at a time. Note that delete_all will run cascade triggers while "delete" will not.

pager

Returns a Data::Page object for the current resultset. Only makes sense for queries with a page attribute.

page($page_num)

Returns a new resultset for the specified page.

new_result(\%vals)

Creates a result in the resultset's result class.

create(\%vals)

Inserts a record into the resultset and returns the object.

Effectively a shortcut for ->new_result(\%vals)->insert.

find_or_create(\%vals, \%attrs?)

  $class->find_or_create({ key => $val, ... });

Searches for a record matching the search condition; if it doesn't find one, creates one and returns that instead.

  my $cd = $schema->resultset('CD')->find_or_create({
    cdid   => 5,
    artist => 'Massive Attack',
    title  => 'Mezzanine',
    year   => 2005,
  });

Also takes an optional key attribute, to search by a specific key or unique constraint. For example:

  my $cd = $schema->resultset('CD')->find_or_create(
    {
      artist => 'Massive Attack',
      title  => 'Mezzanine',
    },
    { key => 'artist_title' }
  );

See also "find" and "update_or_create".

update_or_create

  $class->update_or_create({ key => $val, ... });

First, search for an existing row matching one of the unique constraints (including the primary key) on the source of this resultset. If a row is found, update it with the other given column values. Otherwise, create a new row.

Takes an optional key attribute to search on a specific unique constraint. For example:

  # In your application
  my $cd = $schema->resultset('CD')->update_or_create(
    {
      artist => 'Massive Attack',
      title  => 'Mezzanine',
      year   => 1998,
    },
    { key => 'artist_title' }
  );

If no key is specified, it searches on all unique constraints defined on the source, including the primary key.

If the key is specified as primary, search only on the primary key.

See also "find" and "find_or_create".

throw_exception

See Schema's throw_exception

ATTRIBUTES

The resultset takes various attributes that modify its behavior. Here's an overview of them:

order_by

Which column(s) to order the results by. This is currently passed through directly to SQL, so you can give e.g. foo DESC for a descending order.

cols (arrayref)

Shortcut to request a particular set of columns to be retrieved. Adds me. onto the start of any column without a . in it and sets select from that, then auto-populates as from select as normal.

include_columns (arrayref)

Shortcut to include additional columns in the returned results - for example

  { include_columns => ['foo.name'], join => ['foo'] }

would add a 'name' column to the information passed to object inflation

select (arrayref)

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:

  $rs = $schema->resultset('Foo')->search(
    {},
    {
      select => [
        'column_name',
        { count => 'column_to_count' },
        { sum => 'column_to_sum' }
      ]
    }
  );

When you use function/stored procedure names and do not supply an as attribute, the column names returned are storage-dependent. E.g. MySQL would return a column named count(column_to_count) in the above example.

as (arrayref)

Indicates column names for object inflation. This is used in conjunction with select, usually when select contains one or more function or stored procedure names:

  $rs = $schema->resultset('Foo')->search(
    {},
    {
      select => [
        'column1',
        { count => 'column2' }
      ],
      as => [qw/ column1 column2_count /]
    }
  );

  my $foo = $rs->first(); # get the first Foo

If the object against which the search is performed already has an accessor matching a column name specified in as, the value can be retrieved using the accessor as normal:

  my $column1 = $foo->column1();

If on the other hand an accessor does not exist in the object, you need to use get_column instead:

  my $column2_count = $foo->get_column('column2_count');

You can create your own accessors if required - see DBIx::Class::Manual::Cookbook for details.

join

Contains a list of relationships that should be joined for this query. For example:

  # Get CDs by Nine Inch Nails
  my $rs = $schema->resultset('CD')->search(
    { 'artist.name' => 'Nine Inch Nails' },
    { join => 'artist' }
  );

Can also contain a hash reference to refer to the other relation's relations. For example:

  package MyApp::Schema::Track;
  use base qw/DBIx::Class/;
  __PACKAGE__->table('track');
  __PACKAGE__->add_columns(qw/trackid cd position title/);
  __PACKAGE__->set_primary_key('trackid');
  __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
  1;

  # In your application
  my $rs = $schema->resultset('Artist')->search(
    { 'track.title' => 'Teardrop' },
    {
      join     => { cd => 'track' },
      order_by => 'artist.name',
    }
  );

If the same join is supplied twice, it will be aliased to <rel>_2 (and similarly for a third time). For e.g.

  my $rs = $schema->resultset('Artist')->search(
    { 'cds.title'   => 'Foo',
      'cds_2.title' => 'Bar' },
    { join => [ qw/cds cds/ ] });

will return a set of all artists that have both a cd with title Foo and a cd with title Bar.

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

prefetch arrayref/hashref

Contains one or more relationships that should be fetched along with the main query (when they are accessed afterwards they will have already been "prefetched"). This is useful for when you know you will need the related objects, because it saves at least one query:

  my $rs = $schema->resultset('Tag')->search(
    {},
    {
      prefetch => {
        cd => 'artist'
      }
    }
  );

The initial search results in SQL like the following:

  SELECT tag.*, cd.*, artist.* FROM tag
  JOIN cd ON tag.cd = cd.cdid
  JOIN artist ON cd.artist = artist.artistid

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. If you're prefetching to depth (e.g. { cd => { artist => 'label' } or similar), you'll need to specify the join as well.

prefetch can be used with the following relationship types: belongs_to, has_one (or if you're using add_relationship, any relationship declared with an accessor type of 'single' or 'filter').

from (arrayref)

The from attribute gives you manual control over the FROM clause of SQL statements generated by DBIx::Class, allowing you to express custom JOIN clauses.

NOTE: Use this on your own risk. This allows you to shoot off your foot! join will usually do what you need and it is strongly recommended that you avoid using from unless you cannot achieve the desired result using join.

In simple terms, from works as follows:

    [
        { <alias> => <table>, -join-type => 'inner|left|right' }
        [] # nested JOIN (optional)
        { <table.column> = <foreign_table.foreign_key> }
    ]

    JOIN
        <alias> <table>
        [JOIN ...]
    ON <table.column> = <foreign_table.foreign_key>

An easy way to follow the examples below is to remember the following:

    Anything inside "[]" is a JOIN
    Anything inside "{}" is a condition for the enclosing JOIN

The following examples utilize a "person" table in a family tree application. In order to express parent->child relationships, this table is self-joined:

    # Person->belongs_to('father' => 'Person');
    # Person->belongs_to('mother' => 'Person');

from can be used to nest joins. Here we return all children with a father, then search against all mothers of those children:

  $rs = $schema->resultset('Person')->search(
      {},
      {
          alias => 'mother', # alias columns in accordance with "from"
          from => [
              { mother => 'person' },
              [
                  [
                      { child => 'person' },
                      [
                          { father => 'person' },
                          { 'father.person_id' => 'child.father_id' }
                      ]
                  ],
                  { 'mother.person_id' => 'child.mother_id' }
              ],                
          ]
      },
  );

  # Equivalent SQL:
  # SELECT mother.* FROM person mother
  # JOIN (
  #   person child
  #   JOIN person father
  #   ON ( father.person_id = child.father_id )
  # )
  # ON ( mother.person_id = child.mother_id )

The type of any join can be controlled manually. To search against only people with a father in the person table, we could explicitly use INNER JOIN:

    $rs = $schema->resultset('Person')->search(
        {},
        {
            alias => 'child', # alias columns in accordance with "from"
            from => [
                { child => 'person' },
                [
                    { father => 'person', -join-type => 'inner' },
                    { 'father.id' => 'child.father_id' }
                ],
            ]
        },
    );

    # Equivalent SQL:
    # SELECT child.* FROM person child
    # INNER JOIN person father ON child.father_id = father.id

page

For a paged resultset, specifies which page to retrieve. Leave unset for an unpaged resultset.

rows

For a paged resultset, how many rows per page:

  rows => 10

Can also be used to simulate an SQL LIMIT.

group_by (arrayref)

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

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

distinct

Set to 1 to group by all columns.

For more examples of using these attributes, see DBIx::Class::Manual::Cookbook.