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 ) name_length
113 If your alias exists as a column in your base class (i.e. it was added
114 with C<add_columns>), you just access it as normal. Our C<Artist>
115 class has a C<name> column, so we just use the C<name> accessor:
117 my $artist = $rs->first();
118 my $name = $artist->name();
120 If on the other hand the alias does not correspond to an existing column, you
121 have to fetch the value using the C<get_column> accessor:
123 my $name_length = $artist->get_column('name_length');
125 If you don't like using C<get_column>, you can always create an accessor for
126 any of your aliases using either of these:
128 # Define accessor manually:
129 sub name_length { shift->get_column('name_length'); }
131 # Or use DBIx::Class::AccessorGroup:
132 __PACKAGE__->mk_group_accessors('column' => 'name_length');
134 =head3 SELECT DISTINCT with multiple columns
136 my $rs = $schema->resultset('Foo')->search(
140 { distinct => [ $source->columns ] }
142 as => [ $source->columns ]
146 my $count = $rs->next->get_column('count');
148 =head3 SELECT COUNT(DISTINCT colname)
150 my $rs = $schema->resultset('Foo')->search(
154 { count => { distinct => 'colname' } }
160 =head3 Grouping results
162 L<DBIx::Class> supports C<GROUP BY> as follows:
164 my $rs = $schema->resultset('Artist')->search(
168 select => [ 'name', { count => 'cds.cdid' } ],
169 as => [qw/ name cd_count /],
170 group_by => [qw/ name /]
175 # SELECT name, COUNT( cds.cdid ) FROM artist me
176 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
179 =head3 Predefined searches
181 You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
182 and define often used searches as methods:
184 package My::DBIC::ResultSet::CD;
187 use base 'DBIx::Class::ResultSet';
189 sub search_cds_ordered {
192 return $self->search(
194 { order_by => 'name DESC' },
200 To use your resultset, first tell DBIx::Class to create an instance of it
201 for you, in your My::DBIC::Schema::CD class:
203 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
205 Then call your new method in your code:
207 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
210 =head3 Predefined searches without writing a ResultSet class
212 Alternatively you can automatically generate a DBIx::Class::ResultSet
213 class by using the ResultSetManager component and tagging your method
216 __PACKAGE__->load_components(qw/ ResultSetManager Core /);
218 sub search_cds_ordered : ResultSet {
220 return $self->search(
222 { order_by => 'name DESC' },
226 Then call your method in the same way from your code:
228 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
230 =head2 Using joins and prefetch
232 You can use the C<join> attribute to allow searching on, or sorting your
233 results by, one or more columns in a related table. To return all CDs matching
234 a particular artist name:
236 my $rs = $schema->resultset('CD')->search(
238 'artist.name' => 'Bob Marley'
241 join => [qw/artist/], # join the artist table
246 # SELECT cd.* FROM cd
247 # JOIN artist ON cd.artist = artist.id
248 # WHERE artist.name = 'Bob Marley'
250 If required, you can now sort on any column in the related tables by including
251 it in your C<order_by> attribute:
253 my $rs = $schema->resultset('CD')->search(
255 'artist.name' => 'Bob Marley'
258 join => [qw/ artist /],
259 order_by => [qw/ artist.name /]
264 # SELECT cd.* FROM cd
265 # JOIN artist ON cd.artist = artist.id
266 # WHERE artist.name = 'Bob Marley'
267 # ORDER BY artist.name
269 Note that the C<join> attribute should only be used when you need to search or
270 sort using columns in a related table. Joining related tables when you only
271 need columns from the main table will make performance worse!
273 Now let's say you want to display a list of CDs, each with the name of the
274 artist. The following will work fine:
276 while (my $cd = $rs->next) {
277 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
280 There is a problem however. We have searched both the C<cd> and C<artist> tables
281 in our main query, but we have only returned data from the C<cd> table. To get
282 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
285 SELECT artist.* FROM artist WHERE artist.id = ?
287 A statement like the one above will run for each and every CD returned by our
288 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
291 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
292 This allows you to fetch results from related tables in advance:
294 my $rs = $schema->resultset('CD')->search(
296 'artist.name' => 'Bob Marley'
299 join => [qw/ artist /],
300 order_by => [qw/ artist.name /],
301 prefetch => [qw/ artist /] # return artist data too!
305 # Equivalent SQL (note SELECT from both "cd" and "artist"):
306 # SELECT cd.*, artist.* FROM cd
307 # JOIN artist ON cd.artist = artist.id
308 # WHERE artist.name = 'Bob Marley'
309 # ORDER BY artist.name
311 The code to print the CD list remains the same:
313 while (my $cd = $rs->next) {
314 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
317 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
318 so no additional SQL statements are executed. You now have a much more
321 Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
322 C<has_many> relationships.
324 Also note that C<prefetch> should only be used when you know you will
325 definitely use data from a related table. Pre-fetching related tables when you
326 only need columns from the main table will make performance worse!
328 =head3 Multi-step joins
330 Sometimes you want to join more than one relationship deep. In this example,
331 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
332 contain a specific string:
334 # Relationships defined elsewhere:
335 # Artist->has_many('cds' => 'CD', 'artist');
336 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
338 my $rs = $schema->resultset('Artist')->search(
340 'liner_notes.notes' => { 'like', '%some text%' },
344 'cds' => 'liner_notes'
350 # SELECT artist.* FROM artist
351 # JOIN ( cd ON artist.id = cd.artist )
352 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
353 # WHERE liner_notes.notes LIKE '%some text%'
355 Joins can be nested to an arbitrary level. So if we decide later that we
356 want to reduce the number of Artists returned based on who wrote the liner
359 # Relationship defined elsewhere:
360 # LinerNotes->belongs_to('author' => 'Person');
362 my $rs = $schema->resultset('Artist')->search(
364 'liner_notes.notes' => { 'like', '%some text%' },
365 'author.name' => 'A. Writer'
370 'liner_notes' => 'author'
377 # SELECT artist.* FROM artist
378 # JOIN ( cd ON artist.id = cd.artist )
379 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
380 # JOIN ( author ON author.id = liner_notes.author )
381 # WHERE liner_notes.notes LIKE '%some text%'
382 # AND author.name = 'A. Writer'
384 =head2 Multi-step prefetch
386 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
387 deep using the same syntax as a multi-step join:
389 my $rs = $schema->resultset('Tag')->search(
399 # SELECT tag.*, cd.*, artist.* FROM tag
400 # JOIN cd ON tag.cd = cd.cdid
401 # JOIN artist ON cd.artist = artist.artistid
403 Now accessing our C<cd> and C<artist> relationships does not need additional
406 my $tag = $rs->first;
407 print $tag->cd->artist->name;
409 =head2 Columns of data
411 If you want to find the sum of a particular column there are several
412 ways, the obvious one is to use search:
414 my $rs = $schema->resultset('Items')->search(
417 select => [ { sum => 'Cost' } ],
418 as => [ 'total_cost' ],
421 my $tc = $rs->first->get_column('total_cost');
423 Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets
424 returned when you ask the C<ResultSet> for a column using
427 my $cost = $schema->resultset('Items')->get_column('Cost');
430 With this you can also do:
432 my $minvalue = $cost->min;
433 my $maxvalue = $cost->max;
435 Or just iterate through the values of this column only:
437 while ( my $c = $cost->next ) {
441 foreach my $c ($cost->all) {
445 C<ResultSetColumn> only has a limited number of built-in functions, if
446 you need one that it doesn't have, then you can use the C<func> method
449 my $avg = $cost->func('AVERAGE');
451 This will cause the following SQL statement to be run:
453 SELECT AVERAGE(Cost) FROM Items me
455 Which will of course only work if your database supports this function.
456 See L<DBIx::Class::ResultSetColumn> for more documentation.
458 =head2 Using relationships
460 =head3 Create a new row in a related table
462 my $book->create_related('author', { name => 'Fred'});
464 =head3 Search in a related table
466 Only searches for books named 'Titanic' by the author in $author.
468 my $author->search_related('books', { name => 'Titanic' });
470 =head3 Delete data in a related table
472 Deletes only the book named Titanic by the author in $author.
474 my $author->delete_related('books', { name => 'Titanic' });
476 =head3 Ordering a relationship result set
478 If you always want a relation to be ordered, you can specify this when you
479 create the relationship.
481 To order C<< $book->pages >> by descending page_number.
483 Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
489 As of version 0.04001, there is improved transaction support in
490 L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
491 example of the recommended way to use it:
493 my $genus = $schema->resultset('Genus')->find(12);
501 $genus->add_to_species({ name => 'troglodyte' });
504 $schema->txn_do($coderef2); # Can have a nested transaction
505 return $genus->species;
510 $rs = $schema->txn_do($coderef1);
513 if ($@) { # Transaction failed
514 die "the sky is falling!" #
515 if ($@ =~ /Rollback failed/); # Rollback failed
517 deal_with_failed_transaction();
520 Nested transactions will work as expected. That is, only the outermost
521 transaction will actually issue a commit to the $dbh, and a rollback
522 at any level of any transaction will cause the entire nested
523 transaction to fail. Support for savepoints and for true nested
524 transactions (for databases that support them) will hopefully be added
527 =head2 Many-to-many relationships
529 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
532 # ... set up connection ...
536 __PACKAGE__->table('user');
537 __PACKAGE__->add_columns(qw/id name/);
538 __PACKAGE__->set_primary_key('id');
539 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
540 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
542 package My::UserAddress;
544 __PACKAGE__->table('user_address');
545 __PACKAGE__->add_columns(qw/user address/);
546 __PACKAGE__->set_primary_key(qw/user address/);
547 __PACKAGE__->belongs_to('user' => 'My::User');
548 __PACKAGE__->belongs_to('address' => 'My::Address');
552 __PACKAGE__->table('address');
553 __PACKAGE__->add_columns(qw/id street town area_code country/);
554 __PACKAGE__->set_primary_key('id');
555 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
556 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
558 $rs = $user->addresses(); # get all addresses for a user
559 $rs = $address->users(); # get all users for an address
561 =head2 Setting default values for a row
563 It's as simple as overriding the C<new> method. Note the use of
567 my ( $class, $attrs ) = @_;
569 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
571 $class->next::method($attrs);
574 For more information about C<next::method>, look in the L<Class::C3>
575 documentation. See also L<DBIx::Class::Manual::Component> for more
576 ways to write your own base classes to do this.
578 People looking for ways to do "triggers" with DBIx::Class are probably
579 just looking for this.
581 =head2 Stringification
583 Employ the standard stringification technique by using the C<overload>
586 To make an object stringify itself as a single column, use something
587 like this (replace C<foo> with the column/method of your choice):
589 use overload '""' => 'foo', fallback => 1;
591 For more complex stringification, you can use an anonymous subroutine:
593 use overload '""' => sub { $_[0]->name . ", " .
594 $_[0]->address }, fallback => 1;
596 =head3 Stringification Example
598 Suppose we have two tables: C<Product> and C<Category>. The table
601 Product(id, Description, category)
602 Category(id, Description)
604 C<category> is a foreign key into the Category table.
606 If you have a Product object C<$obj> and write something like
610 things will not work as expected.
612 To obtain, for example, the category description, you should add this
613 method to the class defining the Category table:
615 use overload "" => sub {
618 return $self->Description;
621 =head2 Disconnecting cleanly
623 If you find yourself quitting an app with Control-C a lot during
624 development, you might like to put the following signal handler in
625 your main database class to make sure it disconnects cleanly:
628 __PACKAGE__->storage->disconnect;
631 =head2 Schema import/export
633 This functionality requires you to have L<SQL::Translator> (also known as
634 "SQL Fairy") installed.
636 To create a DBIx::Class schema from an existing database:
639 --to DBIx::Class::File
640 --prefix "MySchema" > MySchema.pm
642 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
643 schema to MySQL's dialect of SQL:
645 sqlt --from SQL::Translator::Parser::DBIx::Class
647 --DBIx::Class "MySchema.pm" > Schema1.sql
649 And import using the mysql client:
651 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
653 =head2 Easy migration from class-based to schema-based setup
655 You want to start using the schema-based approach to L<DBIx::Class>
656 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
657 of existing classes that you don't want to move by hand. Try this nifty script
663 my $schema = MyDB->schema_instance;
665 my $translator = SQL::Translator->new(
666 debug => $debug || 0,
667 trace => $trace || 0,
668 no_comments => $no_comments || 0,
669 show_warnings => $show_warnings || 0,
670 add_drop_table => $add_drop_table || 0,
671 validate => $validate || 0,
673 'DBIx::Schema' => $schema,
676 'prefix' => 'My::Schema',
680 $translator->parser('SQL::Translator::Parser::DBIx::Class');
681 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
683 my $output = $translator->translate(@args) or die
684 "Error: " . $translator->error;
688 You could use L<Module::Find> to search for all subclasses in the MyDB::*
689 namespace, which is currently left as an exercise for the reader.
691 =head2 Schema versioning
693 The following example shows simplistically how you might use DBIx::Class to
694 deploy versioned schemas to your customers. The basic process is as follows:
700 Create a DBIx::Class schema
712 Modify schema to change functionality
716 Deploy update to customers
720 =head3 Create a DBIx::Class schema
722 This can either be done manually, or generated from an existing database as
723 described under C<Schema import/export>.
725 =head3 Save the schema
727 Use C<sqlt> to transform your schema into an SQL script suitable for your
728 customer's database. E.g. for MySQL:
730 sqlt --from SQL::Translator::Parser::DBIx::Class
732 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
734 If you need to target databases from multiple vendors, just generate an SQL
735 script suitable for each. To support PostgreSQL too:
737 sqlt --from SQL::Translator::DBIx::Class
739 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
741 =head3 Deploy to customers
743 There are several ways you could deploy your schema. These are probably
744 beyond the scope of this recipe, but might include:
750 Require customer to apply manually using their RDBMS.
754 Package along with your app, making database dump/schema update/tests
755 all part of your install.
759 =head3 Modify the schema to change functionality
761 As your application evolves, it may be necessary to modify your schema to
762 change functionality. Once the changes are made to your schema in DBIx::Class,
763 export the modified schema as before, taking care not to overwrite the original:
765 sqlt --from SQL::Translator::DBIx::Class
767 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
769 Next, use sqlt-diff to create an SQL script that will update the customer's
772 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
774 =head3 Deploy update to customers
776 The schema update can be deployed to customers using the same method as before.
778 =head2 Setting limit dialect for SQL::Abstract::Limit
780 In some cases, SQL::Abstract::Limit cannot determine the dialect of
781 the remote SQL server by looking at the database handle. This is a
782 common problem when using the DBD::JDBC, since the DBD-driver only
783 know that in has a Java-driver available, not which JDBC driver the
784 Java component has loaded. This specifically sets the limit_dialect
785 to Microsoft SQL-server (See more names in SQL::Abstract::Limit
788 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
790 The JDBC bridge is one way of getting access to a MSSQL server from a platform
791 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
793 =head2 Setting quoting for the generated SQL.
795 If the database contains column names with spaces and/or reserved words, they
796 need to be quoted in the SQL queries. This is done using:
798 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
799 __PACKAGE__->storage->sql_maker->name_sep('.');
801 The first sets the quote characters. Either a pair of matching
802 brackets, or a C<"> or C<'>:
804 __PACKAGE__->storage->sql_maker->quote_char('"');
806 Check the documentation of your database for the correct quote
807 characters to use. C<name_sep> needs to be set to allow the SQL
808 generator to put the quotes the correct place.
810 =head2 Overloading methods
812 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
813 method calls. You have to use calls to C<next::method> to overload methods.
814 More information on using L<Class::C3> with L<DBIx::Class> can be found in
815 L<DBIx::Class::Manual::Component>.
817 =head3 Changing one field whenever another changes
819 For example, say that you have three columns, C<id>, C<number>, and
820 C<squared>. You would like to make changes to C<number> and have
821 C<squared> be automagically set to the value of C<number> squared.
822 You can accomplish this by overriding C<store_column>:
825 my ( $self, $name, $value ) = @_;
826 if ($name eq 'number') {
827 $self->squared($value * $value);
829 $self->next::method($name, $value);
832 Note that the hard work is done by the call to C<next::method>, which
833 redispatches your call to store_column in the superclass(es).
835 =head3 Automatically creating related objects
837 You might have a class C<Artist> which has many C<CD>s. Further, if you
838 want to create a C<CD> object every time you insert an C<Artist> object.
839 You can accomplish this by overriding C<insert> on your objects:
842 my ( $self, @args ) = @_;
843 $self->next::method(@args);
844 $self->cds->new({})->fill_from_artist($self)->insert;
848 where C<fill_from_artist> is a method you specify in C<CD> which sets
849 values in C<CD> based on the data in the C<Artist> object you pass in.
851 =head2 Debugging DBIx::Class objects with Data::Dumper
853 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
854 be hard to find the pertinent data in all the data it can generate.
855 Specifically, if one naively tries to use it like so,
859 my $cd = $schema->resultset('CD')->find(1);
862 several pages worth of data from the CD object's schema and result source will
863 be dumped to the screen. Since usually one is only interested in a few column
864 values of the object, this is not very helpful.
866 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
867 it. Simply define a hook that L<Data::Dumper> will call on the object before
868 dumping it. For example,
875 result_source => undef,
883 local $Data::Dumper::Freezer = '_dumper_hook';
885 my $cd = $schema->resultset('CD')->find(1);
887 # dumps $cd without its ResultSource
889 If the structure of your schema is such that there is a common base class for
890 all your table classes, simply put a method similar to C<_dumper_hook> in the
891 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
892 will automagically clean up your data before printing it. See
893 L<Data::Dumper/EXAMPLES> for more information.
895 =head2 Retrieving a row object's Schema
897 It is possible to get a Schema object from a row object like so:
899 my $schema = $cd->result_source->schema;
900 # use the schema as normal:
901 my $artist_rs = $schema->resultset('Artist');
903 This can be useful when you don't want to pass around a Schema object to every
908 When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL
909 executed as well as notifications of query completion and transaction
910 begin/commit. If you'd like to profile the SQL you can subclass the
911 L<DBIx::Class::Storage::Statistics> class and write your own profiling
914 package My::Profiler;
917 use base 'DBIx::Class::Storage::Statistics';
919 use Time::HiRes qw(time);
928 print "Executing $sql: ".join(', ', @params)."\n";
937 printf("Execution took %0.4f seconds.\n", time() - $start);
943 You can then install that class as the debugging object:
945 __PACKAGE__->storage()->debugobj(new My::Profiler());
946 __PACKAGE__->storage()->debug(1);
948 A more complicated example might involve storing each execution of SQL in an
956 my $elapsed = time() - $start;
957 push(@{ $calls{$sql} }, {
963 You could then create average, high and low execution times for an SQL
964 statement and dig down to see if certain parameters cause aberrant behavior.
966 =head2 Getting the value of the primary key for the last database insert
968 AKA getting last_insert_id
970 If you are using PK::Auto, this is straightforward:
972 my $foo = $rs->create(\%blah);
974 my $id = $foo->id; # foo->my_primary_key_field will also work.
976 If you are not using autoincrementing primary keys, this will probably
977 not work, but then you already know the value of the last primary key anyway.
979 =head2 Dynamic Sub-classing DBIx::Class proxy classes
980 (AKA multi-class object inflation from one table)
982 L<DBIx::Class> classes are proxy classes, therefore some different
983 techniques need to be employed for more than basic subclassing. In
984 this example we have a single user table that carries a boolean bit
985 for admin. We would like like to give the admin users
986 objects(L<DBIx::Class::Row>) the same methods as a regular user but
987 also special admin only methods. It doesn't make sense to create two
988 seperate proxy-class files for this. We would be copying all the user
989 methods into the Admin class. There is a cleaner way to accomplish
992 Overriding the C<inflate_result> method within the User proxy-class
993 gives us the effect we want. This method is called by
994 L<DBIx::Class::ResultSet> when inflating a result from storage. So we
995 grab the object being returned, inspect the values we are looking for,
996 bless it if it's an admin object, and then return it. See the example
1003 use base qw/DBIx::Class::Schema/;
1005 __PACKAGE__->load_classes(qw/User/);
1008 B<Proxy-Class definitions>
1010 package DB::Schema::User;
1014 use base qw/DBIx::Class/;
1016 ### Defined what our admin class is for ensure_class_loaded
1017 my $admin_class = __PACKAGE__ . '::Admin';
1019 __PACKAGE__->load_components(qw/Core/);
1021 __PACKAGE__->table('users');
1023 __PACKAGE__->add_columns(qw/user_id email password
1024 firstname lastname active
1027 __PACKAGE__->set_primary_key('user_id');
1029 sub inflate_result {
1031 my $ret = $self->next::method(@_);
1032 if( $ret->admin ) {### If this is an admin rebless for extra functions
1033 $self->ensure_class_loaded( $admin_class );
1034 bless $ret, $admin_class;
1040 print "I am a regular user.\n";
1045 package DB::Schema::User::Admin;
1049 use base qw/DB::Schema::User/;
1053 print "I am an admin.\n";
1059 print "I am doing admin stuff\n";
1063 B<Test File> test.pl
1069 my $user_data = { email => 'someguy@place.com',
1070 password => 'pass1',
1073 my $admin_data = { email => 'someadmin@adminplace.com',
1074 password => 'pass2',
1077 my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
1079 $schema->resultset('User')->create( $user_data );
1080 $schema->resultset('User')->create( $admin_data );
1082 ### Now we search for them
1083 my $user = $schema->resultset('User')->single( $user_data );
1084 my $admin = $schema->resultset('User')->single( $admin_data );
1086 print ref $user, "\n";
1087 print ref $admin, "\n";
1089 print $user->password , "\n"; # pass1
1090 print $admin->password , "\n";# pass2; inherited from User
1091 print $user->hello , "\n";# I am a regular user.
1092 print $admin->hello, "\n";# I am an admin.
1094 ### The statement below will NOT print
1095 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
1096 ### The statement below will print
1097 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
1099 =head2 Skip object creation for faster results
1101 DBIx::Class is not built for speed, it's built for convenience and
1102 ease of use, but sometimes you just need to get the data, and skip the
1103 fancy objects. Luckily this is also fairly easy using
1106 # Define a class which just returns the results as a hashref:
1107 package My::HashRefInflator;
1109 ## $me is the hashref of cols/data from the immediate resultsource
1110 ## $prefetch is a deep hashref of all the data from the prefetched
1114 my ($me, $rest) = @_;
1117 map { ($_ => mk_hash(@{$rest->{$_}})) } keys %$rest
1121 sub inflate_result {
1122 my ($self, $source, $me, $prefetch) = @_;
1123 return mk_hash($me, $prefetch);
1126 # Change the object inflation to a hashref for just this resultset:
1127 $rs->result_class('My::HashRefInflator');
1129 my $datahashref = $rs->next;
1130 foreach my $col (keys %$datahashref) {
1131 if(!ref($datahashref->{$col})) {
1132 # It's a plain value
1134 elsif(ref($datahashref->{$col} eq 'HASH')) {
1135 # It's a related value in a hashref
1139 =head2 Want to know if find_or_create found or created a row?
1141 Just use C<find_or_new> instead, then check C<in_storage>:
1143 my $obj = $rs->find_or_new({ blah => 'blarg' });
1144 unless ($obj->in_storage) {
1146 # do whatever else you wanted if it was a new row