lib/DBIx/Class/Manual/Cookbook.pod

   1 =head1 NAME
   2
   3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
   4
   5 =head1 RECIPES
   6
   7 =head2 Searching
   8
   9 =head3 Paged results
  10
  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:
  13
  14   my $rs = $schema->resultset('Artist')->search(
  15     {},
  16     {
  17       page => 1,  # page to return (defaults to 1)
  18       rows => 10, # number of results per page
  19     },
  20   );
  21
  22   return $rs->all(); # all records for page 1
  23
  24 The C<page> attribute does not have to be specified in your search:
  25
  26   my $rs = $schema->resultset('Artist')->search(
  27     {},
  28     {
  29       rows => 10,
  30     }
  31   );
  32
  33   return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
  34
  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:
  37
  38   return $rs->pager();
  39
  40 =head3 Complex WHERE clauses
  41
  42 Sometimes you need to formulate a query using specific operators:
  43
  44   my @albums = $schema->resultset('Album')->search({
  45     artist => { 'like', '%Lamb%' },
  46     title  => { 'like', '%Fear of Fours%' },
  47   });
  48
  49 This results in something like the following C<WHERE> clause:
  50
  51   WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
  52
  53 Other queries might require slightly more complex logic:
  54
  55   my @albums = $schema->resultset('Album')->search({
  56     -or => [
  57       -and => [
  58         artist => { 'like', '%Smashing Pumpkins%' },
  59         title  => 'Siamese Dream',
  60       ],
  61       artist => 'Starchildren',
  62     ],
  63   });
  64
  65 This results in the following C<WHERE> clause:
  66
  67   WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
  68     OR artist = 'Starchildren'
  69
  70 For more information on generating complex queries, see
  71 L<SQL::Abstract/WHERE CLAUSES>.
  72
  73 =head3 Using specific columns
  74
  75 When you only want selected columns from a table, you can use C<cols> to
  76 specify which ones you need:
  77
  78   my $rs = $schema->resultset('Artist')->search(
  79     {},
  80     {
  81       cols => [qw/ name /]
  82     }
  83   );
  84
  85   # Equivalent SQL:
  86   # SELECT artist.name FROM artist
  87
  88 =head3 Using database functions or stored procedures
  89
  90 The combination of C<select> and C<as> can be used to return the result of a
  91 database function or stored procedure as a column value. You use C<select> to
  92 specify the source for your column value (e.g. a column name, function, or
  93 stored procedure name). You then use C<as> to set the column name you will use
  94 to access the returned value:
  95
  96   my $rs = $schema->resultset('Artist')->search(
  97     {},
  98     {
  99       select => [ 'name', { LENGTH => 'name' } ],
 100       as     => [qw/ name name_length /],
 101     }
 102   );
 103
 104   # Equivalent SQL:
 105   # SELECT name name, LENGTH( name ) name_length
 106   # FROM artist
 107
 108 If your alias exists as a column in your base class (i.e. it was added with
 109 C<add_columns>), you just access it as normal. Our C<Artist> class has a C<name>
 110 column, so we just use the C<name> accessor:
 111
 112   my $artist = $rs->first();
 113   my $name = $artist->name();
 114
 115 If on the other hand the alias does not correspond to an existing column, you
 116 can get the value using the C<get_column> accessor:
 117
 118   my $name_length = $artist->get_column('name_length');
 119
 120 If you don't like using C<get_column>, you can always create an accessor for
 121 any of your aliases using either of these:
 122
 123   # Define accessor manually:
 124   sub name_length { shift->get_column('name_length'); }
 125
 126   # Or use DBIx::Class::AccessorGroup:
 127   __PACKAGE__->mk_group_accessors('column' => 'name_length');
 128
 129 =head3 SELECT DISTINCT with multiple columns
 130
 131   my $rs = $schema->resultset('Foo')->search(
 132     {},
 133     {
 134       select => [
 135         { distinct => [ $source->columns ] }
 136       ],
 137       as => [ $source->columns ]
 138     }
 139   );
 140
 141 =head3 SELECT COUNT(DISTINCT colname)
 142
 143   my $rs = $schema->resultset('Foo')->search(
 144     {},
 145     {
 146       select => [
 147         { count => { distinct => 'colname' } }
 148       ],
 149       as => [ 'count' ]
 150     }
 151   );
 152
 153 =head3 Grouping results
 154
 155 L<DBIx::Class> supports C<GROUP BY> as follows:
 156
 157   my $rs = $schema->resultset('Artist')->search(
 158     {},
 159     {
 160       join     => [qw/ cds /],
 161       select   => [ 'name', { count => 'cds.cdid' } ],
 162       as       => [qw/ name cd_count /],
 163       group_by => [qw/ name /]
 164     }
 165   );
 166
 167   # Equivalent SQL:
 168   # SELECT name, COUNT( cds.cdid ) FROM artist me
 169   # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
 170   # GROUP BY name
 171
 172 =head2 Using joins and prefetch
 173
 174 You can use the C<join> attribute to allow searching on, or sorting your
 175 results by, one or more columns in a related table. To return all CDs matching
 176 a particular artist name:
 177
 178   my $rs = $schema->resultset('CD')->search(
 179     {
 180       'artist.name' => 'Bob Marley'
 181     },
 182     {
 183       join => [qw/artist/], # join the artist table
 184     }
 185   );
 186
 187   # Equivalent SQL:
 188   # SELECT cd.* FROM cd
 189   # JOIN artist ON cd.artist = artist.id
 190   # WHERE artist.name = 'Bob Marley'
 191
 192 If required, you can now sort on any column in the related tables by including
 193 it in your C<order_by> attribute:
 194
 195   my $rs = $schema->resultset('CD')->search(
 196     {
 197       'artist.name' => 'Bob Marley'
 198     },
 199     {
 200       join     => [qw/ artist /],
 201       order_by => [qw/ artist.name /]
 202     }
 203   };
 204
 205   # Equivalent SQL:
 206   # SELECT cd.* FROM cd
 207   # JOIN artist ON cd.artist = artist.id
 208   # WHERE artist.name = 'Bob Marley'
 209   # ORDER BY artist.name
 210
 211 Note that the C<join> attribute should only be used when you need to search or
 212 sort using columns in a related table. Joining related tables when you only
 213 need columns from the main table will make performance worse!
 214
 215 Now let's say you want to display a list of CDs, each with the name of the
 216 artist. The following will work fine:
 217
 218   while (my $cd = $rs->next) {
 219     print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
 220   }
 221
 222 There is a problem however. We have searched both the C<cd> and C<artist> tables
 223 in our main query, but we have only returned data from the C<cd> table. To get
 224 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
 225 to the database:
 226
 227   SELECT artist.* FROM artist WHERE artist.id = ?
 228
 229 A statement like the one above will run for each and every CD returned by our
 230 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
 231 queries!
 232
 233 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
 234 This allows you to fetch results from a related table as well as the main table
 235 for your class:
 236
 237   my $rs = $schema->resultset('CD')->search(
 238     {
 239       'artist.name' => 'Bob Marley'
 240     },
 241     {
 242       join     => [qw/ artist /],
 243       order_by => [qw/ artist.name /],
 244       prefetch => [qw/ artist /] # return artist data too!
 245     }
 246   );
 247
 248   # Equivalent SQL (note SELECT from both "cd" and "artist"):
 249   # SELECT cd.*, artist.* FROM cd
 250   # JOIN artist ON cd.artist = artist.id
 251   # WHERE artist.name = 'Bob Marley'
 252   # ORDER BY artist.name
 253
 254 The code to print the CD list remains the same:
 255
 256   while (my $cd = $rs->next) {
 257     print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
 258   }
 259
 260 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
 261 so no additional SQL statements are executed. You now have a much more
 262 efficient query.
 263
 264 Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
 265 C<has_many> relationships. You will get an error along the lines of "No
 266 accessor for prefetched ..." if you try.
 267
 268 Also note that C<prefetch> should only be used when you know you will
 269 definitely use data from a related table. Pre-fetching related tables when you
 270 only need columns from the main table will make performance worse!
 271
 272 =head3 Multi-step joins
 273
 274 Sometimes you want to join more than one relationship deep. In this example,
 275 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
 276 contain a specific string:
 277
 278   # Relationships defined elsewhere:
 279   # Artist->has_many('cds' => 'CD', 'artist');
 280   # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
 281
 282   my $rs = $schema->resultset('Artist')->search(
 283     {
 284       'liner_notes.notes' => { 'like', '%some text%' },
 285     },
 286     {
 287       join => {
 288         'cds' => 'liner_notes'
 289       }
 290     }
 291   );
 292
 293   # Equivalent SQL:
 294   # SELECT artist.* FROM artist
 295   # JOIN ( cd ON artist.id = cd.artist )
 296   # JOIN ( liner_notes ON cd.id = liner_notes.cd )
 297   # WHERE liner_notes.notes LIKE '%some text%'
 298
 299 Joins can be nested to an arbitrary level. So if we decide later that we
 300 want to reduce the number of Artists returned based on who wrote the liner
 301 notes:
 302
 303   # Relationship defined elsewhere:
 304   # LinerNotes->belongs_to('author' => 'Person');
 305
 306   my $rs = $schema->resultset('Artist')->search(
 307     {
 308       'liner_notes.notes' => { 'like', '%some text%' },
 309       'author.name' => 'A. Writer'
 310     },
 311     {
 312       join => {
 313         'cds' => {
 314           'liner_notes' => 'author'
 315         }
 316       }
 317     }
 318   );
 319
 320   # Equivalent SQL:
 321   # SELECT artist.* FROM artist
 322   # JOIN ( cd ON artist.id = cd.artist )
 323   # JOIN ( liner_notes ON cd.id = liner_notes.cd )
 324   # JOIN ( author ON author.id = liner_notes.author )
 325   # WHERE liner_notes.notes LIKE '%some text%'
 326   # AND author.name = 'A. Writer'
 327
 328 =head2 Transactions
 329
 330 As of version 0.04001, there is improved transaction support in
 331 L<DBIx::Class::Storage::DBI>.  Here is an example of the recommended
 332 way to use it:
 333
 334   my $genus = Genus->find(12);
 335   eval {
 336     MyDB->txn_begin;
 337     $genus->add_to_species({ name => 'troglodyte' });
 338     $genus->wings(2);
 339     $genus->update;
 340     cromulate($genus); # Can have a nested transation
 341     MyDB->txn_commit;
 342   };
 343   if ($@) {
 344     # Rollback might fail, too
 345     eval {
 346       MyDB->txn_rollback
 347     };
 348   }
 349
 350 Currently, a nested commit will do nothing and a nested rollback will
 351 die.  The code at each level must be sure to call rollback in the case
 352 of an error, to ensure that the rollback will propagate to the top
 353 level and be issued.  Support for savepoints and for true nested
 354 transactions (for databases that support them) will hopefully be added
 355 in the future.
 356
 357 =head2 Many-to-many relationships
 358
 359 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
 360
 361   package My::DB;
 362   # ... set up connection ...
 363
 364   package My::User;
 365   use base 'My::DB';
 366   __PACKAGE__->table('user');
 367   __PACKAGE__->add_columns(qw/id name/);
 368   __PACKAGE__->set_primary_key('id');
 369   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
 370   __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
 371
 372   package My::UserAddress;
 373   use base 'My::DB';
 374   __PACKAGE__->table('user_address');
 375   __PACKAGE__->add_columns(qw/user address/);
 376   __PACKAGE__->set_primary_key(qw/user address/);
 377   __PACKAGE__->belongs_to('user' => 'My::User');
 378   __PACKAGE__->belongs_to('address' => 'My::Address');
 379
 380   package My::Address;
 381   use base 'My::DB';
 382   __PACKAGE__->table('address');
 383   __PACKAGE__->add_columns(qw/id street town area_code country/);
 384   __PACKAGE__->set_primary_key('id');
 385   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
 386   __PACKAGE__->many_to_many('users' => 'user_address', 'user');
 387
 388   $rs = $user->addresses(); # get all addresses for a user
 389   $rs = $address->users(); # get all users for an address
 390
 391 =head2 Setting default values for a row
 392
 393 It's as simple as overriding the C<new> method.  Note the use of
 394 C<next::method>.
 395
 396   sub new {
 397     my ( $class, $attrs ) = @_;
 398
 399     $attrs->{foo} = 'bar' unless defined $attrs->{foo};
 400
 401     $class->next::method($attrs);
 402   }
 403
 404 =head2 Stringification
 405
 406 Employ the standard stringification technique by using the C<overload>
 407 module.  Replace C<foo> with the column/method of your choice.
 408
 409   use overload '""' => 'foo', fallback => 1;
 410
 411 =head2 Disconnecting cleanly
 412
 413 If you find yourself quitting an app with Control-C a lot during
 414 development, you might like to put the following signal handler in
 415 your main database class to make sure it disconnects cleanly:
 416
 417   $SIG{INT} = sub {
 418     __PACKAGE__->storage->dbh->disconnect;
 419   };
 420
 421 =head2 Schema import/export
 422
 423 This functionality requires you to have L<SQL::Translator> (also known as
 424 "SQL Fairy") installed.
 425
 426 To create a DBIx::Class schema from an existing database:
 427
 428  sqlt --from DBI
 429       --to DBIx::Class::File
 430       --prefix "MySchema" > MySchema.pm
 431
 432 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
 433 schema to MySQL's dialect of SQL:
 434
 435   sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
 436
 437 And import using the mysql client:
 438
 439   mysql -h "host" -D "database" -u "user" -p < Schema1.sql
 440
 441 =head2 Easy migration from class-based to schema-based setup
 442
 443 You want to start using the schema-based approach to L<DBIx::Class>
 444 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
 445 of existing classes that you don't want to move by hand. Try this nifty script
 446 instead:
 447
 448   use MyDB;
 449   use SQL::Translator;
 450
 451   my $schema = MyDB->schema_instance;
 452
 453   my $translator           =  SQL::Translator->new(
 454       debug                => $debug          ||  0,
 455       trace                => $trace          ||  0,
 456       no_comments          => $no_comments    ||  0,
 457       show_warnings        => $show_warnings  ||  0,
 458       add_drop_table       => $add_drop_table ||  0,
 459       validate             => $validate       ||  0,
 460       parser_args          => {
 461          'DBIx::Schema'    => $schema,
 462                               }
 463       producer_args   => {
 464           'prefix'         => 'My::Schema',
 465                          }
 466   );
 467
 468   $translator->parser('DBIx::Class');
 469   $translator->producer('DBIx::Class::File');
 470
 471   my $output = $translator->translate(@args) or die
 472           "Error: " . $translator->error;
 473
 474   print $output;
 475
 476 You could use L<Module::Find> to search for all subclasses in the MyDB::*
 477 namespace, which is currently left as an excercise for the reader.
 478
 479 =head2 Schema versioning
 480
 481 The following example shows simplistically how you might use DBIx::Class to
 482 deploy versioned schemas to your customers. The basic process is as follows:
 483
 484 1) Create a DBIx::Class schema
 485 2) Save the schema
 486 3) Deploy to customers
 487 4) Modify schema to change functionality
 488 5) Deploy update to customers
 489
 490 =head3 Create a DBIx::Class schema
 491
 492 This can either be done manually, or generated from an existing database as
 493 described under C<Schema import/export>.
 494
 495 =head3 Save the schema
 496
 497 Use C<sqlt> to transform your schema into an SQL script suitable for your
 498 customer's database. E.g. for MySQL:
 499
 500   sqlt --from DBIx::Class
 501        --to MySQL
 502        --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
 503
 504 If you need to target databases from multiple vendors, just generate an SQL
 505 script suitable for each. To support PostgreSQL too:
 506
 507   sqlt --from DBIx::Class
 508        --to PostgreSQL
 509        --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
 510
 511 =head3 Deploy to customers
 512
 513 There are several ways you could deploy your schema. These are probably
 514 beyond the scope of this recipe, but might include:
 515
 516 1) Require customer to apply manually using their RDBMS.
 517 2) Package along with your app, making database dump/schema update/tests
 518 all part of your install.
 519
 520 =head3 Modify the schema to change functionality
 521
 522 As your application evolves, it may be necessary to modify your schema to
 523 change functionality. Once the changes are made to your schema in DBIx::Class,
 524 export the modified schema as before, taking care not to overwrite the original:
 525
 526   sqlt --from DBIx::Class
 527        --to MySQL
 528        --DBIx::Class "Anything.pm" > Schema2.mysql.sql
 529
 530 Next, use sqlt-diff to create an SQL script that will update the customer's
 531 database schema:
 532
 533   sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
 534
 535 =head3 Deploy update to customers
 536
 537 The schema update can be deployed to customers using the same method as before.
 538
 539 =cut