}
);
- my $count = $rs->next->get_column('count');
-
=head2 SELECT COUNT(DISTINCT colname)
my $rs = $schema->resultset('Foo')->search(
}
);
+ my $count = $rs->next->get_column('count');
+
=head2 Grouping results
L<DBIx::Class> supports C<GROUP BY> as follows:
{},
{
join => [qw/ cds /],
- select => [ 'name', { count => 'cds.cdid' } ],
+ select => [ 'name', { count => 'cds.id' } ],
as => [qw/ name cd_count /],
group_by => [qw/ name /]
}
);
# Equivalent SQL:
- # SELECT name, COUNT( cds.cdid ) FROM artist me
- # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
+ # SELECT name, COUNT( cd.id ) FROM artist
+ # LEFT JOIN cd ON artist.id = cd.artist
# GROUP BY name
Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
To use your resultset, first tell DBIx::Class to create an instance of it
for you, in your My::DBIC::Schema::CD class:
+ # class definition as normal
+ __PACKAGE__->load_components(qw/ Core /);
+ __PACKAGE__->table('cd');
+
+ # tell DBIC to use the custom ResultSet class
__PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
+Note that C<resultset_class> must be called after C<load_components> and C<table>, or you will get errors about missing methods.
+
Then call your new method in your code:
my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
'artist.name' => 'Bob Marley'
},
{
- join => [qw/artist/], # join the artist table
+ join => 'artist', # join the artist table
}
);
'artist.name' => 'Bob Marley'
},
{
- join => [qw/ artist /],
+ join => 'artist',
order_by => [qw/ artist.name /]
}
);
'artist.name' => 'Bob Marley'
},
{
- join => [qw/ artist /],
+ join => 'artist',
order_by => [qw/ artist.name /],
- prefetch => [qw/ artist /] # return artist data too!
+ prefetch => 'artist' # return artist data too!
}
);
definitely use data from a related table. Pre-fetching related tables when you
only need columns from the main table will make performance worse!
+=head2 Multiple joins
+
+In the examples above, the C<join> attribute was a scalar. If you
+pass an array reference instead, you can join to multiple tables. In
+this example, we want to limit the search further, using
+C<LinerNotes>:
+
+ # Relationships defined elsewhere:
+ # CD->belongs_to('artist' => 'Artist');
+ # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
+ my $rs = $schema->resultset('CD')->search(
+ {
+ 'artist.name' => 'Bob Marley'
+ 'liner_notes.notes' => { 'like', '%some text%' },
+ },
+ {
+ join => [qw/ artist liner_notes /],
+ order_by => [qw/ artist.name /],
+ }
+ );
+
+ # Equivalent SQL:
+ # SELECT cd.*, artist.*, liner_notes.* FROM cd
+ # JOIN artist ON cd.artist = artist.id
+ # JOIN liner_notes ON cd.id = liner_notes.cd
+ # WHERE artist.name = 'Bob Marley'
+ # ORDER BY artist.name
+
=head2 Multi-step joins
Sometimes you want to join more than one relationship deep. In this example,
# Equivalent SQL:
# SELECT artist.* FROM artist
- # JOIN ( cd ON artist.id = cd.artist )
- # JOIN ( liner_notes ON cd.id = liner_notes.cd )
+ # LEFT JOIN cd ON artist.id = cd.artist
+ # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
# WHERE liner_notes.notes LIKE '%some text%'
Joins can be nested to an arbitrary level. So if we decide later that we
# Equivalent SQL:
# SELECT artist.* FROM artist
- # JOIN ( cd ON artist.id = cd.artist )
- # JOIN ( liner_notes ON cd.id = liner_notes.cd )
- # JOIN ( author ON author.id = liner_notes.author )
+ # LEFT JOIN cd ON artist.id = cd.artist
+ # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
+ # LEFT JOIN author ON author.id = liner_notes.author
# WHERE liner_notes.notes LIKE '%some text%'
# AND author.name = 'A. Writer'
+=head2 Multi-step and multiple joins
+
+With various combinations of array and hash references, you can join
+tables in any combination you desire. For example, to join Artist to
+CD and Concert, and join CD to LinerNotes:
+
+ # Relationships defined elsewhere:
+ # Artist->has_many('concerts' => 'Concert', 'artist');
+
+ my $rs = $schema->resultset('Artist')->search(
+ { },
+ {
+ join => [
+ {
+ cds => 'liner_notes'
+ },
+ 'concerts'
+ ],
+ }
+ );
+
+ # Equivalent SQL:
+ # SELECT artist.* FROM artist
+ # LEFT JOIN cd ON artist.id = cd.artist
+ # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
+ # LEFT JOIN concert ON artist.id = concert.artist
+
=head2 Multi-step prefetch
From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
# Equivalent SQL:
# SELECT tag.*, cd.*, artist.* FROM tag
- # JOIN cd ON tag.cd = cd.cdid
- # JOIN artist ON cd.artist = artist.artistid
+ # JOIN cd ON tag.cd = cd.id
+ # JOIN artist ON cd.artist = artist.id
Now accessing our C<cd> and C<artist> relationships does not need additional
SQL statements:
### The statement below will print
print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
-=head2 Skip object creation for faster results
+=head2 Skip row object creation for faster results
DBIx::Class is not built for speed, it's built for convenience and
ease of use, but sometimes you just need to get the data, and skip the
$rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
my $hash_ref = $rs->find(1);
-
+
Wasn't that easy?
+=head2 Skip row object creation for faster results, but still inflate
+column values to the corresponding objects
+
+ my $rs = $schema->resultset('CD');
+
+ $rs->result_class(DBIx::Class::ResultClass::HashRefInflator->new (
+ inflate_columns => 1
+ ));
+
+ my $hash_ref = $rs->find(1);
+
=head2 Get raw data for blindingly fast results
If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
Which will of course only work if your database supports this function.
See L<DBIx::Class::ResultSetColumn> for more documentation.
+=head2 Creating a result set from a set of rows
+
+Sometimes you have a (set of) row objects that you want to put into a
+resultset without the need to hit the DB again. You can do that by using the
+L<set_cache|DBIx::Class::Resultset/set_cache> method:
+
+ my @uploadable_groups;
+ while (my $group = $groups->next) {
+ if ($group->can_upload($self)) {
+ push @uploadable_groups, $group;
+ }
+ }
+ my $new_rs = $self->result_source->resultset;
+ $new_rs->set_cache(\@uploadable_groups);
+ return $new_rs;
+
+
=head1 USING RELATIONSHIPS
=head2 Create a new row in a related table
- my $book->create_related('author', { name => 'Fred'});
+ my $author = $book->create_related('author', { name => 'Fred'});
=head2 Search in a related table
Only searches for books named 'Titanic' by the author in $author.
- my $author->search_related('books', { name => 'Titanic' });
+ my $books_rs = $author->search_related('books', { name => 'Titanic' });
=head2 Delete data in a related table
Deletes only the book named Titanic by the author in $author.
- my $author->delete_related('books', { name => 'Titanic' });
+ $author->delete_related('books', { name => 'Titanic' });
=head2 Ordering a relationship result set
If you always want a relation to be ordered, you can specify this when you
create the relationship.
-To order C<< $book->pages >> by descending page_number.
+To order C<< $book->pages >> by descending page_number, create the relation
+as follows:
- Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
+ __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
=head2 Many-to-many relationships
This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
- package My::DB;
- # ... set up connection ...
-
package My::User;
- use base 'My::DB';
+ use base 'DBIx::Class';
+ __PACKAGE__->load_components('Core');
__PACKAGE__->table('user');
__PACKAGE__->add_columns(qw/id name/);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
package My::UserAddress;
- use base 'My::DB';
+ use base 'DBIx::Class';
+ __PACKAGE__->load_components('Core');
__PACKAGE__->table('user_address');
__PACKAGE__->add_columns(qw/user address/);
__PACKAGE__->set_primary_key(qw/user address/);
__PACKAGE__->belongs_to('address' => 'My::Address');
package My::Address;
- use base 'My::DB';
+ use base 'DBIx::Class';
+ __PACKAGE__->load_components('Core');
__PACKAGE__->table('address');
__PACKAGE__->add_columns(qw/id street town area_code country/);
__PACKAGE__->set_primary_key('id');
$genus->add_to_species({ name => 'troglodyte' });
$genus->wings(2);
$genus->update;
- $schema->txn_do($coderef2); # Can have a nested transaction
+ $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit
return $genus->species;
};
The recommend way of achieving this is to use the
L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method:
- perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
+ perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
+ -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
This will create a tree of files rooted at C<./lib/My/Schema/> containing
source definitions for all the tables found in the C<foo> database.
requires that the files for 0.1 as created above are available in the
given directory to diff against.
-=head2 select from dual
+=head2 Select from dual
Dummy tables are needed by some databases to allow calling functions
or expressions that aren't based on table content, for examples of how
while (my $dual = $rs->next) {
print $dual->now."\n";
}
- Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl
-line 23.
+ # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23.
You could of course use 'dummy' in C<as> instead of 'now', or C<add_columns> to
your Dual class for whatever you wanted to select from dual, but that's just
select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}],
# which day of the week were you born on?
- select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'"
-]}, "'day'"]}],
+ select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}],
# select 16 rows from dual
select => [ "'hello'" ],
Add the L<DBIx::Class::Schema::Versioned> schema component to your
Schema class. This will add a new table to your database called
-C<SchemaVersions> which will keep track of which version is installed
+C<dbix_class_schema_vesion> which will keep track of which version is installed
and warn if the user trys to run a newer schema version than the
database thinks it has.
projects / dbsrgits/DBIx-Class.git / blobdiff