paged resultset, which will fetch only a small number of records at a time:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
page => 1, # page to return (defaults to 1)
rows => 10, # number of results per page
The C<page> attribute does not have to be specified in your search:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
rows => 10,
}
specify which ones you need:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
- cols => [qw/ name /]
+ columns => [qw/ name /]
}
);
to access the returned value:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
select => [ 'name', { LENGTH => 'name' } ],
as => [qw/ name name_length /],
=head3 SELECT DISTINCT with multiple columns
my $rs = $schema->resultset('Foo')->search(
- {},
+ undef,
{
select => [
{ distinct => [ $source->columns ] }
}
);
+ my $count = $rs->next->get_column('count');
+
=head3 SELECT COUNT(DISTINCT colname)
my $rs = $schema->resultset('Foo')->search(
- {},
+ undef,
{
select => [
{ count => { distinct => 'colname' } }
L<DBIx::Class> supports C<GROUP BY> as follows:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
join => [qw/ cds /],
select => [ 'name', { count => 'cds.cdid' } ],
# LEFT JOIN cd cds ON ( cds.artist = me.artistid )
# GROUP BY name
+=head3 Predefined searches
+
+You can write your own DBIx::Class::ResultSet class by inheriting from it
+and define often used searches as methods:
+
+ package My::DBIC::ResultSet::CD;
+ use strict;
+ use warnings;
+ use base 'DBIx::Class::ResultSet';
+
+ sub search_cds_ordered {
+ my ($self) = @_;
+
+ return $self->search(
+ {},
+ { order_by => 'name DESC' },
+ );
+ }
+
+ 1;
+
+To use your resultset, first tell DBIx::Class to create an instance of it
+for you, in your My::DBIC::Schema::CD class:
+
+ __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
+
+Then call your new method in your code:
+
+ my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
+
+
+=head3 Predefined searches without writing a ResultSet class
+
+Alternatively you can automatically generate a DBIx::Class::ResultSet
+class by using the ResultSetManager component and tagging your method
+as ResultSet:
+
+ __PACKAGE__->load_components(qw/ ResultSetManager Core /);
+
+ sub search_cds_ordered : ResultSet {
+ my ($self) = @_;
+ return $self->search(
+ {},
+ { order_by => 'name DESC' },
+ );
+ }
+
+Then call your method in the same way from your code:
+
+ my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
+
=head2 Using joins and prefetch
You can use the C<join> attribute to allow searching on, or sorting your
so no additional SQL statements are executed. You now have a much more
efficient query.
-Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
-C<has_many> relationships. You will get an error along the lines of "No
-accessor for prefetched ..." if you try.
+Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
+C<has_many> relationships.
Also note that C<prefetch> should only be used when you know you will
definitely use data from a related table. Pre-fetching related tables when you
deep using the same syntax as a multi-step join:
my $rs = $schema->resultset('Tag')->search(
- {},
+ undef,
{
prefetch => {
cd => 'artist'
my $genus = $schema->resultset('Genus')->find(12);
+ my $coderef2 = sub {
+ $genus->extinct(1);
+ $genus->update;
+ };
+
my $coderef1 = sub {
- my ($schema, $genus, $code) = @_;
$genus->add_to_species({ name => 'troglodyte' });
$genus->wings(2);
$genus->update;
- $schema->txn_do($code, $genus); # Can have a nested transation
+ $schema->txn_do($coderef2); # Can have a nested transaction
return $genus->species;
};
- my $coderef2 = sub {
- my ($genus) = @_;
- $genus->extinct(1);
- $genus->update;
- };
-
my $rs;
eval {
- $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
+ $rs = $schema->txn_do($coderef1);
};
if ($@) { # Transaction failed
$class->next::method($attrs);
}
+For more information about C<next::method>, look in the L<Class::C3>
+documentation. See also L<DBIx::Class::Manual::Component> for more
+ways to write your own base classes to do this.
+
+People looking for ways to do "triggers" with DBIx::Class are probably
+just looking for this.
+
=head2 Stringification
Employ the standard stringification technique by using the C<overload>
validate => $validate || 0,
parser_args => {
'DBIx::Schema' => $schema,
- }
+ },
producer_args => {
'prefix' => 'My::Schema',
- }
+ },
);
- $translator->parser('DBIx::Class');
- $translator->producer('DBIx::Class::File');
+ $translator->parser('SQL::Translator::Parser::DBIx::Class');
+ $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
my $output = $translator->translate(@args) or die
"Error: " . $translator->error;
print $output;
You could use L<Module::Find> to search for all subclasses in the MyDB::*
-namespace, which is currently left as an excercise for the reader.
+namespace, which is currently left as an exercise for the reader.
=head2 Schema versioning
__PACKAGE__->storage->sql_maker->quote_char('"');
-is enough. If the left qoute differs form the right quote, the first
+is enough. If the left quote differs form the right quote, the first
notation should be used. name_sep needs to be set to allow the
SQL generator to put the quotes the correct place.
+=head2 Overloading methods
+
+L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
+method calls. You have to use calls to C<next::method> to overload methods.
+More information on using L<Class::C3> with L<DBIx::Class> can be found in
+L<DBIx::Class::Manual::Component>.
+
+=head3 Changing one field whenever another changes
+
+For example, say that you have three columns, C<id>, C<number>, and
+C<squared>. You would like to make changes to C<number> and have
+C<squared> be automagically set to the value of C<number> squared.
+You can accomplish this by overriding C<store_column>:
+
+ sub store_column {
+ my ( $self, $name, $value ) = @_;
+ if ($name eq 'number') {
+ $self->squared($value * $value);
+ }
+ $self->next::method($name, $value);
+ }
+
+Note that the hard work is done by the call to C<next::method>, which
+redispatches your call to store_column to the superclass(es).
+
+=head3 Automatically creating related objects
+
+You might have a class C<Artist> which has many C<CD>s. Further, you
+want to create a C<CD> object every time you insert an C<Artist> object.
+You can accomplish this by overriding C<insert> on your objects:
+
+ sub insert {
+ my ( $self, @args ) = @_;
+ $self->next::method(@args);
+ $self->cds->new({})->fill_from_artist($self)->insert;
+ return $self;
+ }
+
+where C<fill_from_artist> is a method you specify in C<CD> which sets
+values in C<CD> based on the data in the C<Artist> object you pass in.
+
+=head2 Debugging DBIx::Class objects with Data::Dumper
+
+L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
+be hard to find the pertinent data in all the data it can generate.
+Specifically, if one naively tries to use it like so,
+
+ use Data::Dumper;
+
+ my $cd = $schema->resultset('CD')->find(1);
+ print Dumper($cd);
+
+several pages worth of data from the CD object's schema and result source will
+be dumped to the screen. Since usually one is only interested in a few column
+values of the object, this is not very helpful.
+
+Luckily, it is possible to modify the data before L<Data::Dumper> outputs
+it. Simply define a hook that L<Data::Dumper> will call on the object before
+dumping it. For example,
+
+ package My::DB::CD;
+
+ sub _dumper_hook {
+ $_[0] = bless {
+ %{ $_[0] },
+ result_source => undef,
+ }, ref($_[0]);
+ }
+
+ [...]
+
+ use Data::Dumper;
+
+ $Data::Dumper::Freezer = '_dumper_hook';
+
+ my $cd = $schema->resultset('CD')->find(1);
+ print Dumper($cd);
+ # dumps $cd without its ResultSource
+
+If the structure of your schema is such that there is a common base class for
+all your table classes, simply put a method similar to C<_dumper_hook> in the
+base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
+will automagically clean up your data before printing it. See
+L<Data::Dumper/EXAMPLES> for more information.
+
+=head2 Retrieving a row object's Schema
+
+It is possible to get a Schema object from a row object like so,
+
+ my $schema = $cd->result_source->schema;
+ my $artist_rs = $schema->resultset('Artist');
+ # for example
+
+This can be useful when you don't want to pass around a Schema object to every
+method.
+
+=head2 Profiling
+
+When you enable L<DBIx::Class::Storage::DBI>'s debugging it prints the SQL
+executed as well as notifications of query completion and transaction
+begin/commit. If you'd like to profile the SQL you can subclass the
+L<DBIx::Class::Storage::Statistics> class and write your own profiling
+mechanism:
+
+ package My::Profiler;
+ use strict;
+
+ use base 'DBIx::Class::Storage::Statistics';
+
+ use Time::HiRes qw(time);
+
+ my $start;
+
+ sub query_start {
+ my $self = shift();
+ my $sql = shift();
+ my $params = @_;
+
+ print "Executing $sql: ".join(', ', @params)."\n";
+ $start = time();
+ }
+
+ sub query_end {
+ my $self = shift();
+ my $sql = shift();
+ my @params = @_;
+
+ printf("Execution took %0.4f seconds.\n", time() - $start);
+ $start = undef;
+ }
+
+ 1;
+
+You can then install that class as the debugging object:
+
+ __PACKAGE__->storage()->debugobj(new My::Profiler());
+ __PACKAGE__->storage()->debug(1);
+
+A more complicated example might involve storing each execution of SQL in an
+array:
+
+ sub query_end {
+ my $self = shift();
+ my $sql = shift();
+ my @params = @_;
+
+ my $elapsed = time() - $start;
+ push(@{ $calls{$sql} }, {
+ params => \@params,
+ elapsed => $elapsed
+ });
+ }
+
+You could then create average, high and low execution times for an SQL
+statement and dig down to see if certain parameters cause aberrant behavior.
+
+=head2 Getting the value of the primary key for the last database insert
+
+AKA getting last_insert_id
+
+If you are using PK::Auto, this is straightforward:
+
+ my $foo = $rs->create(\%blah);
+ # do more stuff
+ my $id = $foo->id; # foo->my_primary_key_field will also work.
+
+If you are not using autoincrementing primary keys, this will probably
+not work, but then you already know the value of the last primary key anyway.
+
=cut
projects / dbsrgits/DBIx-Class.git / blobdiff