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::DBI> 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 $class->next::method($attrs);
581 For more information about C<next::method>, look in the L<Class::C3>
582 documentation. See also L<DBIx::Class::Manual::Component> for more
583 ways to write your own base classes to do this.
585 People looking for ways to do "triggers" with DBIx::Class are probably
586 just looking for this.
588 =head2 Stringification
590 Employ the standard stringification technique by using the C<overload>
593 To make an object stringify itself as a single column, use something
594 like this (replace C<foo> with the column/method of your choice):
596 use overload '""' => sub { shift->name}, fallback => 1;
598 For more complex stringification, you can use an anonymous subroutine:
600 use overload '""' => sub { $_[0]->name . ", " .
601 $_[0]->address }, fallback => 1;
603 =head3 Stringification Example
605 Suppose we have two tables: C<Product> and C<Category>. The table
608 Product(id, Description, category)
609 Category(id, Description)
611 C<category> is a foreign key into the Category table.
613 If you have a Product object C<$obj> and write something like
617 things will not work as expected.
619 To obtain, for example, the category description, you should add this
620 method to the class defining the Category table:
622 use overload "" => sub {
625 return $self->Description;
628 =head2 Disconnecting cleanly
630 If you find yourself quitting an app with Control-C a lot during
631 development, you might like to put the following signal handler in
632 your main database class to make sure it disconnects cleanly:
635 __PACKAGE__->storage->disconnect;
638 =head2 Schema import/export
640 This functionality requires you to have L<SQL::Translator> (also known as
641 "SQL Fairy") installed.
643 To create a DBIx::Class schema from an existing database:
646 --to DBIx::Class::File
647 --prefix "MySchema" > MySchema.pm
649 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
650 schema to MySQL's dialect of SQL:
652 sqlt --from SQL::Translator::Parser::DBIx::Class
654 --DBIx::Class "MySchema.pm" > Schema1.sql
656 And import using the mysql client:
658 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
660 =head2 Easy migration from class-based to schema-based setup
662 You want to start using the schema-based approach to L<DBIx::Class>
663 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
664 of existing classes that you don't want to move by hand. Try this nifty script
670 my $schema = MyDB->schema_instance;
672 my $translator = SQL::Translator->new(
673 debug => $debug || 0,
674 trace => $trace || 0,
675 no_comments => $no_comments || 0,
676 show_warnings => $show_warnings || 0,
677 add_drop_table => $add_drop_table || 0,
678 validate => $validate || 0,
680 'DBIx::Schema' => $schema,
683 'prefix' => 'My::Schema',
687 $translator->parser('SQL::Translator::Parser::DBIx::Class');
688 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
690 my $output = $translator->translate(@args) or die
691 "Error: " . $translator->error;
695 You could use L<Module::Find> to search for all subclasses in the MyDB::*
696 namespace, which is currently left as an exercise for the reader.
698 =head2 Schema versioning
700 The following example shows simplistically how you might use DBIx::Class to
701 deploy versioned schemas to your customers. The basic process is as follows:
707 Create a DBIx::Class schema
719 Modify schema to change functionality
723 Deploy update to customers
727 =head3 Create a DBIx::Class schema
729 This can either be done manually, or generated from an existing database as
730 described under C<Schema import/export>.
732 =head3 Save the schema
734 Use C<sqlt> to transform your schema into an SQL script suitable for your
735 customer's database. E.g. for MySQL:
737 sqlt --from SQL::Translator::Parser::DBIx::Class
739 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
741 If you need to target databases from multiple vendors, just generate an SQL
742 script suitable for each. To support PostgreSQL too:
744 sqlt --from SQL::Translator::DBIx::Class
746 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
748 =head3 Deploy to customers
750 There are several ways you could deploy your schema. These are probably
751 beyond the scope of this recipe, but might include:
757 Require customer to apply manually using their RDBMS.
761 Package along with your app, making database dump/schema update/tests
762 all part of your install.
766 =head3 Modify the schema to change functionality
768 As your application evolves, it may be necessary to modify your schema to
769 change functionality. Once the changes are made to your schema in DBIx::Class,
770 export the modified schema as before, taking care not to overwrite the original:
772 sqlt --from SQL::Translator::DBIx::Class
774 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
776 Next, use sqlt-diff to create an SQL script that will update the customer's
779 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
781 =head3 Deploy update to customers
783 The schema update can be deployed to customers using the same method as before.
785 =head2 Setting limit dialect for SQL::Abstract::Limit
787 In some cases, SQL::Abstract::Limit cannot determine the dialect of
788 the remote SQL server by looking at the database handle. This is a
789 common problem when using the DBD::JDBC, since the DBD-driver only
790 know that in has a Java-driver available, not which JDBC driver the
791 Java component has loaded. This specifically sets the limit_dialect
792 to Microsoft SQL-server (See more names in SQL::Abstract::Limit
795 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
797 The JDBC bridge is one way of getting access to a MSSQL server from a platform
798 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
800 =head2 Setting quoting for the generated SQL.
802 If the database contains column names with spaces and/or reserved words, they
803 need to be quoted in the SQL queries. This is done using:
805 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
806 __PACKAGE__->storage->sql_maker->name_sep('.');
808 The first sets the quote characters. Either a pair of matching
809 brackets, or a C<"> or C<'>:
811 __PACKAGE__->storage->sql_maker->quote_char('"');
813 Check the documentation of your database for the correct quote
814 characters to use. C<name_sep> needs to be set to allow the SQL
815 generator to put the quotes the correct place.
817 =head2 Overloading methods
819 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
820 method calls. You have to use calls to C<next::method> to overload methods.
821 More information on using L<Class::C3> with L<DBIx::Class> can be found in
822 L<DBIx::Class::Manual::Component>.
824 =head3 Changing one field whenever another changes
826 For example, say that you have three columns, C<id>, C<number>, and
827 C<squared>. You would like to make changes to C<number> and have
828 C<squared> be automagically set to the value of C<number> squared.
829 You can accomplish this by overriding C<store_column>:
832 my ( $self, $name, $value ) = @_;
833 if ($name eq 'number') {
834 $self->squared($value * $value);
836 $self->next::method($name, $value);
839 Note that the hard work is done by the call to C<next::method>, which
840 redispatches your call to store_column in the superclass(es).
842 =head3 Automatically creating related objects
844 You might have a class C<Artist> which has many C<CD>s. Further, if you
845 want to create a C<CD> object every time you insert an C<Artist> object.
846 You can accomplish this by overriding C<insert> on your objects:
849 my ( $self, @args ) = @_;
850 $self->next::method(@args);
851 $self->cds->new({})->fill_from_artist($self)->insert;
855 where C<fill_from_artist> is a method you specify in C<CD> which sets
856 values in C<CD> based on the data in the C<Artist> object you pass in.
858 =head2 Debugging DBIx::Class objects with Data::Dumper
860 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
861 be hard to find the pertinent data in all the data it can generate.
862 Specifically, if one naively tries to use it like so,
866 my $cd = $schema->resultset('CD')->find(1);
869 several pages worth of data from the CD object's schema and result source will
870 be dumped to the screen. Since usually one is only interested in a few column
871 values of the object, this is not very helpful.
873 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
874 it. Simply define a hook that L<Data::Dumper> will call on the object before
875 dumping it. For example,
882 result_source => undef,
890 local $Data::Dumper::Freezer = '_dumper_hook';
892 my $cd = $schema->resultset('CD')->find(1);
894 # dumps $cd without its ResultSource
896 If the structure of your schema is such that there is a common base class for
897 all your table classes, simply put a method similar to C<_dumper_hook> in the
898 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
899 will automagically clean up your data before printing it. See
900 L<Data::Dumper/EXAMPLES> for more information.
902 =head2 Retrieving a row object's Schema
904 It is possible to get a Schema object from a row object like so:
906 my $schema = $cd->result_source->schema;
907 # use the schema as normal:
908 my $artist_rs = $schema->resultset('Artist');
910 This can be useful when you don't want to pass around a Schema object to every
915 When you enable L<DBIx::Class::Storage::DBI>'s debugging it prints the SQL
916 executed as well as notifications of query completion and transaction
917 begin/commit. If you'd like to profile the SQL you can subclass the
918 L<DBIx::Class::Storage::Statistics> class and write your own profiling
921 package My::Profiler;
924 use base 'DBIx::Class::Storage::Statistics';
926 use Time::HiRes qw(time);
935 print "Executing $sql: ".join(', ', @params)."\n";
944 printf("Execution took %0.4f seconds.\n", time() - $start);
950 You can then install that class as the debugging object:
952 __PACKAGE__->storage()->debugobj(new My::Profiler());
953 __PACKAGE__->storage()->debug(1);
955 A more complicated example might involve storing each execution of SQL in an
963 my $elapsed = time() - $start;
964 push(@{ $calls{$sql} }, {
970 You could then create average, high and low execution times for an SQL
971 statement and dig down to see if certain parameters cause aberrant behavior.
973 =head2 Getting the value of the primary key for the last database insert
975 AKA getting last_insert_id
977 If you are using PK::Auto, this is straightforward:
979 my $foo = $rs->create(\%blah);
981 my $id = $foo->id; # foo->my_primary_key_field will also work.
983 If you are not using autoincrementing primary keys, this will probably
984 not work, but then you already know the value of the last primary key anyway.
986 =head2 Dynamic Sub-classing DBIx::Class proxy classes
987 (AKA multi-class object inflation from one table)
989 L<DBIx::Class> classes are proxy classes, therefore some different
990 techniques need to be employed for more than basic subclassing. In
991 this example we have a single user table that carries a boolean bit
992 for admin. We would like like to give the admin users
993 objects(L<DBIx::Class::Row>) the same methods as a regular user but
994 also special admin only methods. It doesn't make sense to create two
995 seperate proxy-class files for this. We would be copying all the user
996 methods into the Admin class. There is a cleaner way to accomplish
999 Overriding the C<inflate_result> method within the User proxy-class
1000 gives us the effect we want. This method is called by
1001 L<DBIx::Class::ResultSet> when inflating a result from storage. So we
1002 grab the object being returned, inspect the values we are looking for,
1003 bless it if it's an admin object, and then return it. See the example
1006 B<Schema Definition>
1010 use base qw/DBIx::Class::Schema/;
1012 __PACKAGE__->load_classes(qw/User/);
1015 B<Proxy-Class definitions>
1017 package DB::Schema::User;
1021 use base qw/DBIx::Class/;
1023 ### Defined what our admin class is for ensure_class_loaded
1024 my $admin_class = __PACKAGE__ . '::Admin';
1026 __PACKAGE__->load_components(qw/Core/);
1028 __PACKAGE__->table('users');
1030 __PACKAGE__->add_columns(qw/user_id email password
1031 firstname lastname active
1034 __PACKAGE__->set_primary_key('user_id');
1036 sub inflate_result {
1038 my $ret = $self->next::method(@_);
1039 if( $ret->admin ) {### If this is an admin rebless for extra functions
1040 $self->ensure_class_loaded( $admin_class );
1041 bless $ret, $admin_class;
1047 print "I am a regular user.\n";
1052 package DB::Schema::User::Admin;
1056 use base qw/DB::Schema::User/;
1060 print "I am an admin.\n";
1066 print "I am doing admin stuff\n";
1070 B<Test File> test.pl
1076 my $user_data = { email => 'someguy@place.com',
1077 password => 'pass1',
1080 my $admin_data = { email => 'someadmin@adminplace.com',
1081 password => 'pass2',
1084 my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
1086 $schema->resultset('User')->create( $user_data );
1087 $schema->resultset('User')->create( $admin_data );
1089 ### Now we search for them
1090 my $user = $schema->resultset('User')->single( $user_data );
1091 my $admin = $schema->resultset('User')->single( $admin_data );
1093 print ref $user, "\n";
1094 print ref $admin, "\n";
1096 print $user->password , "\n"; # pass1
1097 print $admin->password , "\n";# pass2; inherited from User
1098 print $user->hello , "\n";# I am a regular user.
1099 print $admin->hello, "\n";# I am an admin.
1101 ### The statement below will NOT print
1102 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
1103 ### The statement below will print
1104 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
1106 =head2 Skip object creation for faster results
1108 DBIx::Class is not built for speed, it's built for convenience and
1109 ease of use, but sometimes you just need to get the data, and skip the
1110 fancy objects. Luckily this is also fairly easy using
1113 # Define a class which just returns the results as a hashref:
1114 package My::HashRefInflator;
1116 ## $me is the hashref of cols/data from the immediate resultsource
1117 ## $prefetch is a deep hashref of all the data from the prefetched
1121 my ($me, $rest) = @_;
1124 map { ($_ => mk_hash(@{$rest->{$_}})) } keys %$rest
1128 sub inflate_result {
1129 my ($self, $source, $me, $prefetch) = @_;
1130 return mk_hash($me, $prefetch);
1133 # Change the object inflation to a hashref for just this resultset:
1134 $rs->result_class('My::HashRefInflator');
1136 my $datahashref = $rs->next;
1137 foreach my $col (keys %$datahashref) {
1138 if(!ref($datahashref->{$col})) {
1139 # It's a plain value
1141 elsif(ref($datahashref->{$col} eq 'HASH')) {
1142 # It's a related value in a hashref
1146 =head2 Want to know if find_or_create found or created a row?
1148 Just use C<find_or_new> instead, then check C<in_storage>:
1150 my $obj = $rs->find_or_new({ blah => 'blarg' });
1151 unless ($obj->in_storage) {
1153 # do whatever else you wanted if it was a new row
1156 =head3 Wrapping/overloading a column accessor
1158 Problem: Say you have a table "Camera" and want to associate a description
1159 with each camera. For most cameras, you'll be able to generate the description from
1160 the other columns. However, in a few special cases you may want to associate a
1161 custom description with a camera.
1165 In your database schema, define a description field in the "Camera" table that
1166 can contain text and null values.
1168 In DBIC, we'll overload the column accessor to provide a sane default if no
1169 custom description is defined. The accessor will either return or generate the
1170 description, depending on whether the field is null or not.
1172 First, in your "Camera" schema class, define the description field as follows:
1174 __PACKAGE__->add_columns(description => { accessor => '_description' });
1176 Next, we'll define the accessor-wrapper subroutine:
1181 # If there is an update to the column, we'll let the original accessor
1183 return $self->_description(@_) if @_;
1185 # Fetch the column value.
1186 my $description = $self->_description;
1188 # If there's something in the description field, then just return that.
1189 return $description if defined $description && length $descripton;
1191 # Otherwise, generate a description.
1192 return $self->generate_description;