3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
11 When you expect a large number of results, you can ask L<DBIx::Class> for a
12 paged resultset, which will fetch only a small number of records at a time:
14 my $rs = $schema->resultset('Artist')->search(
17 page => 1, # page to return (defaults to 1)
18 rows => 10, # number of results per page
22 return $rs->all(); # all records for page 1
24 The C<page> attribute does not have to be specified in your search:
26 my $rs = $schema->resultset('Artist')->search(
33 return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
35 In either of the above cases, you can return a L<Data::Page> object for the
36 resultset (suitable for use in e.g. a template) using the C<pager> method:
40 =head3 Complex WHERE clauses
42 Sometimes you need to formulate a query using specific operators:
44 my @albums = $schema->resultset('Album')->search({
45 artist => { 'like', '%Lamb%' },
46 title => { 'like', '%Fear of Fours%' },
49 This results in something like the following C<WHERE> clause:
51 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
53 Other queries might require slightly more complex logic:
55 my @albums = $schema->resultset('Album')->search({
58 artist => { 'like', '%Smashing Pumpkins%' },
59 title => 'Siamese Dream',
61 artist => 'Starchildren',
65 This results in the following C<WHERE> clause:
67 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
68 OR artist = 'Starchildren'
70 For more information on generating complex queries, see
71 L<SQL::Abstract/WHERE CLAUSES>.
73 =head3 Using specific columns
75 When you only want specific columns from a table, you can use
76 C<columns> to specify which ones you need. This is useful to avoid
77 loading columns with large amounts of data that you aren't about to
80 my $rs = $schema->resultset('Artist')->search(
83 columns => [qw/ name /]
88 # SELECT artist.name FROM artist
90 This is a shortcut for C<select> and C<as>, see below. C<columns>
91 cannot be used together with C<select> and C<as>.
93 =head3 Using database functions or stored procedures
95 The combination of C<select> and C<as> can be used to return the result of a
96 database function or stored procedure as a column value. You use C<select> to
97 specify the source for your column value (e.g. a column name, function, or
98 stored procedure name). You then use C<as> to set the column name you will use
99 to access the returned value:
101 my $rs = $schema->resultset('Artist')->search(
104 select => [ 'name', { LENGTH => 'name' } ],
105 as => [qw/ name name_length /],
110 # SELECT name name, LENGTH( name )
113 Note that the C< as > attribute has absolutely nothing to with the sql
114 syntax C< SELECT foo AS bar > (see the documentation in
115 L<DBIx::Class::ResultSet/ATTRIBUTES>). If your alias exists as a
116 column in your base class (i.e. it was added with C<add_columns>), you
117 just access it as normal. Our C<Artist> class has a C<name> column, so
118 we just use the C<name> accessor:
120 my $artist = $rs->first();
121 my $name = $artist->name();
123 If on the other hand the alias does not correspond to an existing column, you
124 have to fetch the value using the C<get_column> accessor:
126 my $name_length = $artist->get_column('name_length');
128 If you don't like using C<get_column>, you can always create an accessor for
129 any of your aliases using either of these:
131 # Define accessor manually:
132 sub name_length { shift->get_column('name_length'); }
134 # Or use DBIx::Class::AccessorGroup:
135 __PACKAGE__->mk_group_accessors('column' => 'name_length');
137 =head3 SELECT DISTINCT with multiple columns
139 my $rs = $schema->resultset('Foo')->search(
143 { distinct => [ $source->columns ] }
145 as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-)
149 my $count = $rs->next->get_column('count');
151 =head3 SELECT COUNT(DISTINCT colname)
153 my $rs = $schema->resultset('Foo')->search(
157 { count => { distinct => 'colname' } }
163 =head3 Grouping results
165 L<DBIx::Class> supports C<GROUP BY> as follows:
167 my $rs = $schema->resultset('Artist')->search(
171 select => [ 'name', { count => 'cds.cdid' } ],
172 as => [qw/ name cd_count /],
173 group_by => [qw/ name /]
178 # SELECT name, COUNT( cds.cdid ) FROM artist me
179 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
182 Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
183 are in any way unsure about the use of the attributes above (C< join
184 >, C< select >, C< as > and C< group_by >).
186 =head3 Predefined searches
188 You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
189 and define often used searches as methods:
191 package My::DBIC::ResultSet::CD;
194 use base 'DBIx::Class::ResultSet';
196 sub search_cds_ordered {
199 return $self->search(
201 { order_by => 'name DESC' },
207 To use your resultset, first tell DBIx::Class to create an instance of it
208 for you, in your My::DBIC::Schema::CD class:
210 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
212 Then call your new method in your code:
214 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
217 =head3 Predefined searches without writing a ResultSet class
219 Alternatively you can automatically generate a DBIx::Class::ResultSet
220 class by using the ResultSetManager component and tagging your method
223 __PACKAGE__->load_components(qw/ ResultSetManager Core /);
225 sub search_cds_ordered : ResultSet {
227 return $self->search(
229 { order_by => 'name DESC' },
233 Then call your method in the same way from your code:
235 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
237 =head2 Using joins and prefetch
239 You can use the C<join> attribute to allow searching on, or sorting your
240 results by, one or more columns in a related table. To return all CDs matching
241 a particular artist name:
243 my $rs = $schema->resultset('CD')->search(
245 'artist.name' => 'Bob Marley'
248 join => [qw/artist/], # join the artist table
253 # SELECT cd.* FROM cd
254 # JOIN artist ON cd.artist = artist.id
255 # WHERE artist.name = 'Bob Marley'
257 If required, you can now sort on any column in the related tables by including
258 it in your C<order_by> attribute:
260 my $rs = $schema->resultset('CD')->search(
262 'artist.name' => 'Bob Marley'
265 join => [qw/ artist /],
266 order_by => [qw/ artist.name /]
271 # SELECT cd.* FROM cd
272 # JOIN artist ON cd.artist = artist.id
273 # WHERE artist.name = 'Bob Marley'
274 # ORDER BY artist.name
276 Note that the C<join> attribute should only be used when you need to search or
277 sort using columns in a related table. Joining related tables when you only
278 need columns from the main table will make performance worse!
280 Now let's say you want to display a list of CDs, each with the name of the
281 artist. The following will work fine:
283 while (my $cd = $rs->next) {
284 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
287 There is a problem however. We have searched both the C<cd> and C<artist> tables
288 in our main query, but we have only returned data from the C<cd> table. To get
289 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
292 SELECT artist.* FROM artist WHERE artist.id = ?
294 A statement like the one above will run for each and every CD returned by our
295 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
298 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
299 This allows you to fetch results from related tables in advance:
301 my $rs = $schema->resultset('CD')->search(
303 'artist.name' => 'Bob Marley'
306 join => [qw/ artist /],
307 order_by => [qw/ artist.name /],
308 prefetch => [qw/ artist /] # return artist data too!
312 # Equivalent SQL (note SELECT from both "cd" and "artist"):
313 # SELECT cd.*, artist.* FROM cd
314 # JOIN artist ON cd.artist = artist.id
315 # WHERE artist.name = 'Bob Marley'
316 # ORDER BY artist.name
318 The code to print the CD list remains the same:
320 while (my $cd = $rs->next) {
321 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
324 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
325 so no additional SQL statements are executed. You now have a much more
328 Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
329 C<has_many> relationships.
331 Also note that C<prefetch> should only be used when you know you will
332 definitely use data from a related table. Pre-fetching related tables when you
333 only need columns from the main table will make performance worse!
335 =head3 Multi-step joins
337 Sometimes you want to join more than one relationship deep. In this example,
338 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
339 contain a specific string:
341 # Relationships defined elsewhere:
342 # Artist->has_many('cds' => 'CD', 'artist');
343 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
345 my $rs = $schema->resultset('Artist')->search(
347 'liner_notes.notes' => { 'like', '%some text%' },
351 'cds' => 'liner_notes'
357 # SELECT artist.* FROM artist
358 # JOIN ( cd ON artist.id = cd.artist )
359 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
360 # WHERE liner_notes.notes LIKE '%some text%'
362 Joins can be nested to an arbitrary level. So if we decide later that we
363 want to reduce the number of Artists returned based on who wrote the liner
366 # Relationship defined elsewhere:
367 # LinerNotes->belongs_to('author' => 'Person');
369 my $rs = $schema->resultset('Artist')->search(
371 'liner_notes.notes' => { 'like', '%some text%' },
372 'author.name' => 'A. Writer'
377 'liner_notes' => 'author'
384 # SELECT artist.* FROM artist
385 # JOIN ( cd ON artist.id = cd.artist )
386 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
387 # JOIN ( author ON author.id = liner_notes.author )
388 # WHERE liner_notes.notes LIKE '%some text%'
389 # AND author.name = 'A. Writer'
391 =head2 Multi-step prefetch
393 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
394 deep using the same syntax as a multi-step join:
396 my $rs = $schema->resultset('Tag')->search(
406 # SELECT tag.*, cd.*, artist.* FROM tag
407 # JOIN cd ON tag.cd = cd.cdid
408 # JOIN artist ON cd.artist = artist.artistid
410 Now accessing our C<cd> and C<artist> relationships does not need additional
413 my $tag = $rs->first;
414 print $tag->cd->artist->name;
416 =head2 Columns of data
418 If you want to find the sum of a particular column there are several
419 ways, the obvious one is to use search:
421 my $rs = $schema->resultset('Items')->search(
424 select => [ { sum => 'Cost' } ],
425 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL
428 my $tc = $rs->first->get_column('total_cost');
430 Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets
431 returned when you ask the C<ResultSet> for a column using
434 my $cost = $schema->resultset('Items')->get_column('Cost');
437 With this you can also do:
439 my $minvalue = $cost->min;
440 my $maxvalue = $cost->max;
442 Or just iterate through the values of this column only:
444 while ( my $c = $cost->next ) {
448 foreach my $c ($cost->all) {
452 C<ResultSetColumn> only has a limited number of built-in functions, if
453 you need one that it doesn't have, then you can use the C<func> method
456 my $avg = $cost->func('AVERAGE');
458 This will cause the following SQL statement to be run:
460 SELECT AVERAGE(Cost) FROM Items me
462 Which will of course only work if your database supports this function.
463 See L<DBIx::Class::ResultSetColumn> for more documentation.
465 =head2 Using relationships
467 =head3 Create a new row in a related table
469 my $book->create_related('author', { name => 'Fred'});
471 =head3 Search in a related table
473 Only searches for books named 'Titanic' by the author in $author.
475 my $author->search_related('books', { name => 'Titanic' });
477 =head3 Delete data in a related table
479 Deletes only the book named Titanic by the author in $author.
481 my $author->delete_related('books', { name => 'Titanic' });
483 =head3 Ordering a relationship result set
485 If you always want a relation to be ordered, you can specify this when you
486 create the relationship.
488 To order C<< $book->pages >> by descending page_number.
490 Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
496 As of version 0.04001, there is improved transaction support in
497 L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
498 example of the recommended way to use it:
500 my $genus = $schema->resultset('Genus')->find(12);
508 $genus->add_to_species({ name => 'troglodyte' });
511 $schema->txn_do($coderef2); # Can have a nested transaction
512 return $genus->species;
517 $rs = $schema->txn_do($coderef1);
520 if ($@) { # Transaction failed
521 die "the sky is falling!" #
522 if ($@ =~ /Rollback failed/); # Rollback failed
524 deal_with_failed_transaction();
527 Nested transactions will work as expected. That is, only the outermost
528 transaction will actually issue a commit to the $dbh, and a rollback
529 at any level of any transaction will cause the entire nested
530 transaction to fail. Support for savepoints and for true nested
531 transactions (for databases that support them) will hopefully be added
534 =head2 Many-to-many relationships
536 This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
539 # ... set up connection ...
543 __PACKAGE__->table('user');
544 __PACKAGE__->add_columns(qw/id name/);
545 __PACKAGE__->set_primary_key('id');
546 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
547 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
549 package My::UserAddress;
551 __PACKAGE__->table('user_address');
552 __PACKAGE__->add_columns(qw/user address/);
553 __PACKAGE__->set_primary_key(qw/user address/);
554 __PACKAGE__->belongs_to('user' => 'My::User');
555 __PACKAGE__->belongs_to('address' => 'My::Address');
559 __PACKAGE__->table('address');
560 __PACKAGE__->add_columns(qw/id street town area_code country/);
561 __PACKAGE__->set_primary_key('id');
562 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
563 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
565 $rs = $user->addresses(); # get all addresses for a user
566 $rs = $address->users(); # get all users for an address
568 =head2 Setting default values for a row
570 It's as simple as overriding the C<new> method. Note the use of
574 my ( $class, $attrs ) = @_;
576 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
578 my $new = $class->next::method($attrs);
583 For more information about C<next::method>, look in the L<Class::C3>
584 documentation. See also L<DBIx::Class::Manual::Component> for more
585 ways to write your own base classes to do this.
587 People looking for ways to do "triggers" with DBIx::Class are probably
588 just looking for this.
590 =head2 Stringification
592 Employ the standard stringification technique by using the C<overload>
595 To make an object stringify itself as a single column, use something
596 like this (replace C<foo> with the column/method of your choice):
598 use overload '""' => sub { shift->name}, fallback => 1;
600 For more complex stringification, you can use an anonymous subroutine:
602 use overload '""' => sub { $_[0]->name . ", " .
603 $_[0]->address }, fallback => 1;
605 =head3 Stringification Example
607 Suppose we have two tables: C<Product> and C<Category>. The table
610 Product(id, Description, category)
611 Category(id, Description)
613 C<category> is a foreign key into the Category table.
615 If you have a Product object C<$obj> and write something like
619 things will not work as expected.
621 To obtain, for example, the category description, you should add this
622 method to the class defining the Category table:
624 use overload "" => sub {
627 return $self->Description;
630 =head2 Disconnecting cleanly
632 If you find yourself quitting an app with Control-C a lot during
633 development, you might like to put the following signal handler in
634 your main database class to make sure it disconnects cleanly:
637 __PACKAGE__->storage->disconnect;
640 =head2 Schema import/export
642 This functionality requires you to have L<SQL::Translator> (also known as
643 "SQL Fairy") installed.
645 To create a DBIx::Class schema from an existing database:
648 --to DBIx::Class::File
649 --prefix "MySchema" > MySchema.pm
651 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
652 schema to MySQL's dialect of SQL:
654 sqlt --from SQL::Translator::Parser::DBIx::Class
656 --DBIx::Class "MySchema.pm" > Schema1.sql
658 And import using the mysql client:
660 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
662 =head2 Easy migration from class-based to schema-based setup
664 You want to start using the schema-based approach to L<DBIx::Class>
665 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
666 of existing classes that you don't want to move by hand. Try this nifty script
672 my $schema = MyDB->schema_instance;
674 my $translator = SQL::Translator->new(
675 debug => $debug || 0,
676 trace => $trace || 0,
677 no_comments => $no_comments || 0,
678 show_warnings => $show_warnings || 0,
679 add_drop_table => $add_drop_table || 0,
680 validate => $validate || 0,
682 'DBIx::Schema' => $schema,
685 'prefix' => 'My::Schema',
689 $translator->parser('SQL::Translator::Parser::DBIx::Class');
690 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
692 my $output = $translator->translate(@args) or die
693 "Error: " . $translator->error;
697 You could use L<Module::Find> to search for all subclasses in the MyDB::*
698 namespace, which is currently left as an exercise for the reader.
700 =head2 Schema versioning
702 The following example shows simplistically how you might use DBIx::Class to
703 deploy versioned schemas to your customers. The basic process is as follows:
709 Create a DBIx::Class schema
721 Modify schema to change functionality
725 Deploy update to customers
729 =head3 Create a DBIx::Class schema
731 This can either be done manually, or generated from an existing database as
732 described under C<Schema import/export>.
734 =head3 Save the schema
736 Use C<sqlt> to transform your schema into an SQL script suitable for your
737 customer's database. E.g. for MySQL:
739 sqlt --from SQL::Translator::Parser::DBIx::Class
741 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
743 If you need to target databases from multiple vendors, just generate an SQL
744 script suitable for each. To support PostgreSQL too:
746 sqlt --from SQL::Translator::DBIx::Class
748 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
750 =head3 Deploy to customers
752 There are several ways you could deploy your schema. These are probably
753 beyond the scope of this recipe, but might include:
759 Require customer to apply manually using their RDBMS.
763 Package along with your app, making database dump/schema update/tests
764 all part of your install.
768 =head3 Modify the schema to change functionality
770 As your application evolves, it may be necessary to modify your schema to
771 change functionality. Once the changes are made to your schema in DBIx::Class,
772 export the modified schema as before, taking care not to overwrite the original:
774 sqlt --from SQL::Translator::DBIx::Class
776 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
778 Next, use sqlt-diff to create an SQL script that will update the customer's
781 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
783 =head3 Deploy update to customers
785 The schema update can be deployed to customers using the same method as before.
787 =head2 Setting limit dialect for SQL::Abstract::Limit
789 In some cases, SQL::Abstract::Limit cannot determine the dialect of
790 the remote SQL server by looking at the database handle. This is a
791 common problem when using the DBD::JDBC, since the DBD-driver only
792 know that in has a Java-driver available, not which JDBC driver the
793 Java component has loaded. This specifically sets the limit_dialect
794 to Microsoft SQL-server (See more names in SQL::Abstract::Limit
797 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
799 The JDBC bridge is one way of getting access to a MSSQL server from a platform
800 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
802 =head2 Setting quoting for the generated SQL.
804 If the database contains column names with spaces and/or reserved words, they
805 need to be quoted in the SQL queries. This is done using:
807 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
808 __PACKAGE__->storage->sql_maker->name_sep('.');
810 The first sets the quote characters. Either a pair of matching
811 brackets, or a C<"> or C<'>:
813 __PACKAGE__->storage->sql_maker->quote_char('"');
815 Check the documentation of your database for the correct quote
816 characters to use. C<name_sep> needs to be set to allow the SQL
817 generator to put the quotes the correct place.
819 =head2 Overloading methods
821 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
822 method calls. You have to use calls to C<next::method> to overload methods.
823 More information on using L<Class::C3> with L<DBIx::Class> can be found in
824 L<DBIx::Class::Manual::Component>.
826 =head3 Changing one field whenever another changes
828 For example, say that you have three columns, C<id>, C<number>, and
829 C<squared>. You would like to make changes to C<number> and have
830 C<squared> be automagically set to the value of C<number> squared.
831 You can accomplish this by overriding C<store_column>:
834 my ( $self, $name, $value ) = @_;
835 if ($name eq 'number') {
836 $self->squared($value * $value);
838 $self->next::method($name, $value);
841 Note that the hard work is done by the call to C<next::method>, which
842 redispatches your call to store_column in the superclass(es).
844 =head3 Automatically creating related objects
846 You might have a class C<Artist> which has many C<CD>s. Further, if you
847 want to create a C<CD> object every time you insert an C<Artist> object.
848 You can accomplish this by overriding C<insert> on your objects:
851 my ( $self, @args ) = @_;
852 $self->next::method(@args);
853 $self->cds->new({})->fill_from_artist($self)->insert;
857 where C<fill_from_artist> is a method you specify in C<CD> which sets
858 values in C<CD> based on the data in the C<Artist> object you pass in.
860 =head2 Debugging DBIx::Class objects with Data::Dumper
862 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
863 be hard to find the pertinent data in all the data it can generate.
864 Specifically, if one naively tries to use it like so,
868 my $cd = $schema->resultset('CD')->find(1);
871 several pages worth of data from the CD object's schema and result source will
872 be dumped to the screen. Since usually one is only interested in a few column
873 values of the object, this is not very helpful.
875 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
876 it. Simply define a hook that L<Data::Dumper> will call on the object before
877 dumping it. For example,
884 result_source => undef,
892 local $Data::Dumper::Freezer = '_dumper_hook';
894 my $cd = $schema->resultset('CD')->find(1);
896 # dumps $cd without its ResultSource
898 If the structure of your schema is such that there is a common base class for
899 all your table classes, simply put a method similar to C<_dumper_hook> in the
900 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
901 will automagically clean up your data before printing it. See
902 L<Data::Dumper/EXAMPLES> for more information.
904 =head2 Retrieving a row object's Schema
906 It is possible to get a Schema object from a row object like so:
908 my $schema = $cd->result_source->schema;
909 # use the schema as normal:
910 my $artist_rs = $schema->resultset('Artist');
912 This can be useful when you don't want to pass around a Schema object to every
917 When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL
918 executed as well as notifications of query completion and transaction
919 begin/commit. If you'd like to profile the SQL you can subclass the
920 L<DBIx::Class::Storage::Statistics> class and write your own profiling
923 package My::Profiler;
926 use base 'DBIx::Class::Storage::Statistics';
928 use Time::HiRes qw(time);
937 print "Executing $sql: ".join(', ', @params)."\n";
946 printf("Execution took %0.4f seconds.\n", time() - $start);
952 You can then install that class as the debugging object:
954 __PACKAGE__->storage()->debugobj(new My::Profiler());
955 __PACKAGE__->storage()->debug(1);
957 A more complicated example might involve storing each execution of SQL in an
965 my $elapsed = time() - $start;
966 push(@{ $calls{$sql} }, {
972 You could then create average, high and low execution times for an SQL
973 statement and dig down to see if certain parameters cause aberrant behavior.
975 =head2 Getting the value of the primary key for the last database insert
977 AKA getting last_insert_id
979 If you are using PK::Auto, this is straightforward:
981 my $foo = $rs->create(\%blah);
983 my $id = $foo->id; # foo->my_primary_key_field will also work.
985 If you are not using autoincrementing primary keys, this will probably
986 not work, but then you already know the value of the last primary key anyway.
988 =head2 Dynamic Sub-classing DBIx::Class proxy classes
989 (AKA multi-class object inflation from one table)
991 L<DBIx::Class> classes are proxy classes, therefore some different
992 techniques need to be employed for more than basic subclassing. In
993 this example we have a single user table that carries a boolean bit
994 for admin. We would like like to give the admin users
995 objects(L<DBIx::Class::Row>) the same methods as a regular user but
996 also special admin only methods. It doesn't make sense to create two
997 seperate proxy-class files for this. We would be copying all the user
998 methods into the Admin class. There is a cleaner way to accomplish
1001 Overriding the C<inflate_result> method within the User proxy-class
1002 gives us the effect we want. This method is called by
1003 L<DBIx::Class::ResultSet> when inflating a result from storage. So we
1004 grab the object being returned, inspect the values we are looking for,
1005 bless it if it's an admin object, and then return it. See the example
1008 B<Schema Definition>
1012 use base qw/DBIx::Class::Schema/;
1014 __PACKAGE__->load_classes(qw/User/);
1017 B<Proxy-Class definitions>
1019 package DB::Schema::User;
1023 use base qw/DBIx::Class/;
1025 ### Defined what our admin class is for ensure_class_loaded
1026 my $admin_class = __PACKAGE__ . '::Admin';
1028 __PACKAGE__->load_components(qw/Core/);
1030 __PACKAGE__->table('users');
1032 __PACKAGE__->add_columns(qw/user_id email password
1033 firstname lastname active
1036 __PACKAGE__->set_primary_key('user_id');
1038 sub inflate_result {
1040 my $ret = $self->next::method(@_);
1041 if( $ret->admin ) {### If this is an admin rebless for extra functions
1042 $self->ensure_class_loaded( $admin_class );
1043 bless $ret, $admin_class;
1049 print "I am a regular user.\n";
1054 package DB::Schema::User::Admin;
1058 use base qw/DB::Schema::User/;
1062 print "I am an admin.\n";
1068 print "I am doing admin stuff\n";
1072 B<Test File> test.pl
1078 my $user_data = { email => 'someguy@place.com',
1079 password => 'pass1',
1082 my $admin_data = { email => 'someadmin@adminplace.com',
1083 password => 'pass2',
1086 my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
1088 $schema->resultset('User')->create( $user_data );
1089 $schema->resultset('User')->create( $admin_data );
1091 ### Now we search for them
1092 my $user = $schema->resultset('User')->single( $user_data );
1093 my $admin = $schema->resultset('User')->single( $admin_data );
1095 print ref $user, "\n";
1096 print ref $admin, "\n";
1098 print $user->password , "\n"; # pass1
1099 print $admin->password , "\n";# pass2; inherited from User
1100 print $user->hello , "\n";# I am a regular user.
1101 print $admin->hello, "\n";# I am an admin.
1103 ### The statement below will NOT print
1104 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
1105 ### The statement below will print
1106 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
1108 =head2 Skip object creation for faster results
1110 DBIx::Class is not built for speed, it's built for convenience and
1111 ease of use, but sometimes you just need to get the data, and skip the
1114 To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
1116 my $rs = $schema->resultset('CD');
1118 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
1120 my $hash_ref = $rs->find(1);
1124 =head2 Get raw data for blindingly fast results
1126 If the C<inflate_result> solution above is not fast enough for you, you
1127 can use a DBIx::Class to return values exactly as they come out of the
1128 data base with none of the convenience methods wrapped round them.
1130 This is used like so:-
1132 my $cursor = $rs->cursor
1133 while (my @vals = $cursor->next) {
1134 # use $val[0..n] here
1137 You will need to map the array offsets to particular columns (you can
1138 use the I<select> attribute of C<search()> to force ordering).
1140 =head2 Want to know if find_or_create found or created a row?
1142 Just use C<find_or_new> instead, then check C<in_storage>:
1144 my $obj = $rs->find_or_new({ blah => 'blarg' });
1145 unless ($obj->in_storage) {
1147 # do whatever else you wanted if it was a new row
1150 =head3 Wrapping/overloading a column accessor
1152 Problem: Say you have a table "Camera" and want to associate a description
1153 with each camera. For most cameras, you'll be able to generate the description from
1154 the other columns. However, in a few special cases you may want to associate a
1155 custom description with a camera.
1159 In your database schema, define a description field in the "Camera" table that
1160 can contain text and null values.
1162 In DBIC, we'll overload the column accessor to provide a sane default if no
1163 custom description is defined. The accessor will either return or generate the
1164 description, depending on whether the field is null or not.
1166 First, in your "Camera" schema class, define the description field as follows:
1168 __PACKAGE__->add_columns(description => { accessor => '_description' });
1170 Next, we'll define the accessor-wrapper subroutine:
1175 # If there is an update to the column, we'll let the original accessor
1177 return $self->_description(@_) if @_;
1179 # Fetch the column value.
1180 my $description = $self->_description;
1182 # If there's something in the description field, then just return that.
1183 return $description if defined $description && length $descripton;
1185 # Otherwise, generate a description.
1186 return $self->generate_description;