X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=6717ea77f74f5453ae2f3f8e47bcdd2a6b9db12d;hb=a5b293612996cda25ce7e7bf1a5a5a23249c7b01;hp=f953b0d29701734d85225deb65347aeff7972949;hpb=16cd5b282a77840ce684f48c5324cfd7418d17ca;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index f953b0d..6717ea7 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -194,8 +194,6 @@ any of your aliases using either of these: } ); - my $count = $rs->next->get_column('count'); - =head2 SELECT COUNT(DISTINCT colname) my $rs = $schema->resultset('Foo')->search( @@ -208,6 +206,8 @@ any of your aliases using either of these: } ); + my $count = $rs->next->get_column('count'); + =head2 Grouping results L supports C as follows: @@ -216,15 +216,15 @@ L supports C as follows: {}, { join => [qw/ cds /], - select => [ 'name', { count => 'cds.cdid' } ], + select => [ 'name', { count => 'cds.id' } ], as => [qw/ name cd_count /], group_by => [qw/ name /] } ); # Equivalent SQL: - # SELECT name, COUNT( cds.cdid ) FROM artist me - # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) + # SELECT name, COUNT( cd.id ) FROM artist + # LEFT JOIN cd ON artist.id = cd.artist # GROUP BY name Please see L documentation if you @@ -255,8 +255,15 @@ and define often used searches as methods: To use your resultset, first tell DBIx::Class to create an instance of it for you, in your My::DBIC::Schema::CD class: + # class definition as normal + __PACKAGE__->load_components(qw/ Core /); + __PACKAGE__->table('cd'); + + # tell DBIC to use the custom ResultSet class __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD'); +Note that C must be called after C and C, or you will get errors about missing methods. + Then call your new method in your code: my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); @@ -305,7 +312,7 @@ a particular artist name: 'artist.name' => 'Bob Marley' }, { - join => [qw/artist/], # join the artist table + join => 'artist', # join the artist table } ); @@ -322,7 +329,7 @@ it in your C attribute: 'artist.name' => 'Bob Marley' }, { - join => [qw/ artist /], + join => 'artist', order_by => [qw/ artist.name /] } ); @@ -363,9 +370,9 @@ This allows you to fetch results from related tables in advance: 'artist.name' => 'Bob Marley' }, { - join => [qw/ artist /], + join => 'artist', order_by => [qw/ artist.name /], - prefetch => [qw/ artist /] # return artist data too! + prefetch => 'artist' # return artist data too! } ); @@ -392,6 +399,34 @@ Also note that C should only be used when you know you will definitely use data from a related table. Pre-fetching related tables when you only need columns from the main table will make performance worse! +=head2 Multiple joins + +In the examples above, the C attribute was a scalar. If you +pass an array reference instead, you can join to multiple tables. In +this example, we want to limit the search further, using +C: + + # Relationships defined elsewhere: + # CD->belongs_to('artist' => 'Artist'); + # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + 'liner_notes.notes' => { 'like', '%some text%' }, + }, + { + join => [qw/ artist liner_notes /], + order_by => [qw/ artist.name /], + } + ); + + # Equivalent SQL: + # SELECT cd.*, artist.*, liner_notes.* FROM cd + # JOIN artist ON cd.artist = artist.id + # JOIN liner_notes ON cd.id = liner_notes.cd + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name + =head2 Multi-step joins Sometimes you want to join more than one relationship deep. In this example, @@ -415,8 +450,8 @@ contain a specific string: # Equivalent SQL: # SELECT artist.* FROM artist - # JOIN ( cd ON artist.id = cd.artist ) - # JOIN ( liner_notes ON cd.id = liner_notes.cd ) + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT JOIN liner_notes ON cd.id = liner_notes.cd # WHERE liner_notes.notes LIKE '%some text%' Joins can be nested to an arbitrary level. So if we decide later that we @@ -442,12 +477,39 @@ notes: # Equivalent SQL: # SELECT artist.* FROM artist - # JOIN ( cd ON artist.id = cd.artist ) - # JOIN ( liner_notes ON cd.id = liner_notes.cd ) - # JOIN ( author ON author.id = liner_notes.author ) + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT JOIN liner_notes ON cd.id = liner_notes.cd + # LEFT JOIN author ON author.id = liner_notes.author # WHERE liner_notes.notes LIKE '%some text%' # AND author.name = 'A. Writer' +=head2 Multi-step and multiple joins + +With various combinations of array and hash references, you can join +tables in any combination you desire. For example, to join Artist to +CD and Concert, and join CD to LinerNotes: + + # Relationships defined elsewhere: + # Artist->has_many('concerts' => 'Concert', 'artist'); + + my $rs = $schema->resultset('Artist')->search( + { }, + { + join => [ + { + cds => 'liner_notes' + }, + 'concerts' + ], + } + ); + + # Equivalent SQL: + # SELECT artist.* FROM artist + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT JOIN liner_notes ON cd.id = liner_notes.cd + # LEFT JOIN concert ON artist.id = concert.artist + =head2 Multi-step prefetch From 0.04999_05 onwards, C can be nested more than one relationship @@ -464,8 +526,8 @@ deep using the same syntax as a multi-step join: # Equivalent SQL: # SELECT tag.*, cd.*, artist.* FROM tag - # JOIN cd ON tag.cd = cd.cdid - # JOIN artist ON cd.artist = artist.artistid + # JOIN cd ON tag.cd = cd.id + # JOIN artist ON cd.artist = artist.id Now accessing our C and C relationships does not need additional SQL statements: @@ -671,7 +733,7 @@ B test.pl ### The statement below will print print "I can do admin stuff\n" if $admin->can('do_admin_stuff'); -=head2 Skip object creation for faster results +=head2 Skip row object creation for faster results DBIx::Class is not built for speed, it's built for convenience and ease of use, but sometimes you just need to get the data, and skip the @@ -684,9 +746,20 @@ To do this simply use L. $rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); my $hash_ref = $rs->find(1); - + Wasn't that easy? +=head2 Skip row object creation for faster results, but still inflate +column values to the corresponding objects + + my $rs = $schema->resultset('CD'); + + $rs->result_class(DBIx::Class::ResultClass::HashRefInflator->new ( + inflate_columns => 1 + )); + + my $hash_ref = $rs->find(1); + =head2 Get raw data for blindingly fast results If the L solution @@ -763,42 +836,58 @@ This will cause the following SQL statement to be run: Which will of course only work if your database supports this function. See L for more documentation. +=head2 Creating a result set from a set of rows + +Sometimes you have a (set of) row objects that you want to put into a +resultset without the need to hit the DB again. You can do that by using the +L method: + + my @uploadable_groups; + while (my $group = $groups->next) { + if ($group->can_upload($self)) { + push @uploadable_groups, $group; + } + } + my $new_rs = $self->result_source->resultset; + $new_rs->set_cache(\@uploadable_groups); + return $new_rs; + + =head1 USING RELATIONSHIPS =head2 Create a new row in a related table - my $book->create_related('author', { name => 'Fred'}); + my $author = $book->create_related('author', { name => 'Fred'}); =head2 Search in a related table Only searches for books named 'Titanic' by the author in $author. - my $author->search_related('books', { name => 'Titanic' }); + my $books_rs = $author->search_related('books', { name => 'Titanic' }); =head2 Delete data in a related table Deletes only the book named Titanic by the author in $author. - my $author->delete_related('books', { name => 'Titanic' }); + $author->delete_related('books', { name => 'Titanic' }); =head2 Ordering a relationship result set If you always want a relation to be ordered, you can specify this when you create the relationship. -To order C<< $book->pages >> by descending page_number. +To order C<< $book->pages >> by descending page_number, create the relation +as follows: - Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} ); + __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} ); =head2 Many-to-many relationships This is straightforward using L: - package My::DB; - # ... set up connection ... - package My::User; - use base 'My::DB'; + use base 'DBIx::Class'; + __PACKAGE__->load_components('Core'); __PACKAGE__->table('user'); __PACKAGE__->add_columns(qw/id name/); __PACKAGE__->set_primary_key('id'); @@ -806,7 +895,8 @@ This is straightforward using Lmany_to_many('addresses' => 'user_address', 'address'); package My::UserAddress; - use base 'My::DB'; + use base 'DBIx::Class'; + __PACKAGE__->load_components('Core'); __PACKAGE__->table('user_address'); __PACKAGE__->add_columns(qw/user address/); __PACKAGE__->set_primary_key(qw/user address/); @@ -814,7 +904,8 @@ This is straightforward using Lbelongs_to('address' => 'My::Address'); package My::Address; - use base 'My::DB'; + use base 'DBIx::Class'; + __PACKAGE__->load_components('Core'); __PACKAGE__->table('address'); __PACKAGE__->add_columns(qw/id street town area_code country/); __PACKAGE__->set_primary_key('id'); @@ -841,7 +932,7 @@ example of the recommended way to use it: $genus->add_to_species({ name => 'troglodyte' }); $genus->wings(2); $genus->update; - $schema->txn_do($coderef2); # Can have a nested transaction + $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit return $genus->species; }; @@ -874,7 +965,8 @@ L and associated sources by examining the database. The recommend way of achieving this is to use the L method: - perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])' + perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \ + -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])' This will create a tree of files rooted at C<./lib/My/Schema/> containing source definitions for all the tables found in the C database. @@ -920,7 +1012,7 @@ of the schema, plus scripts to convert from version 0.1 to 0.2. This requires that the files for 0.1 as created above are available in the given directory to diff against. -=head2 select from dual +=head2 Select from dual Dummy tables are needed by some databases to allow calling functions or expressions that aren't based on table content, for examples of how @@ -959,8 +1051,7 @@ will not work because there is no column called 'now' in the Dual table class while (my $dual = $rs->next) { print $dual->now."\n"; } - Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl -line 23. + # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23. You could of course use 'dummy' in C instead of 'now', or C to your Dual class for whatever you wanted to select from dual, but that's just @@ -1004,8 +1095,7 @@ C select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}], # which day of the week were you born on? - select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" -]}, "'day'"]}], + select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}], # select 16 rows from dual select => [ "'hello'" ], @@ -1123,7 +1213,7 @@ B Add the L schema component to your Schema class. This will add a new table to your database called -C which will keep track of which version is installed +C which will keep track of which version is installed and warn if the user trys to run a newer schema version than the database thinks it has.