return $rs->all(); # all records for page 1
-The C<page> attribute does not have to be specified in your search:
-
- my $rs = $schema->resultset('Artist')->search(
- undef,
- {
- rows => 10,
- }
- );
-
- return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
-
-In either of the above cases, you can get a L<Data::Page> object for the
-resultset (suitable for use in e.g. a template) using the C<pager> method:
+You can get a L<Data::Page> object for the resultset (suitable for use
+in e.g. a template) using the C<pager> method:
return $rs->pager();
are in any way unsure about the use of the attributes above (C< join
>, C< select >, C< as > and C< group_by >).
-=head2 Subqueries
+=head2 Subqueries (EXPERIMENTAL)
You can write subqueries relatively easily in DBIC.
name => [ 'Billy Joel', 'Brittany Spears' ],
});
- my $rs = $schema->resulset('CD')->search({
+ my $rs = $schema->resultset('CD')->search({
artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
});
B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
The following will B<not> work:
- my $rs = $schema->resulset('CD')->search({
+ my $rs = $schema->resultset('CD')->search({
artist_id => $inside_rs->get_column('id')->as_query,
});
+=head3 Support
+
+Subqueries are supported in the where clause (first hashref), and in the
+from, select, and +select attributes.
+
+=head3 Correlated subqueries
+
+ my $cdrs = $schema->resultset('CD');
+ my $rs = $cdrs->search({
+ year => {
+ '=' => $cdrs->search(
+ { artistid => { '=' => \'me.artistid' } },
+ { alias => 'inner' }
+ )->get_column('year')->max_rs->as_query,
+ },
+ });
+
+That creates the following SQL:
+
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
+ FROM cd me
+ WHERE year = (
+ SELECT MAX(inner.year)
+ FROM cd inner
+ WHERE artistid = me.artistid
+ )
+
+=head3 EXPERIMENTAL
+
+Please note that subqueries are considered an experimental feature.
+
=head2 Predefined searches
You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
my $hash_ref = $rs->find(1);
Wasn't that easy?
-
+
=head2 Get raw data for blindingly fast results
If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
while (my @vals = $cursor->next) {
print $vals[0]."\n";
}
+
+In case you're going to use this "trick" together with L<DBIx::Class::Schema/deploy> or
+L<DBIx::Class::Schema/create_ddl_dir> a table called "dual" will be created in your
+current schema. This would overlap "sys.dual" and you could not fetch "sysdate" or
+"sequence.nextval" anymore from dual. To avoid this problem, just tell
+L<SQL::Translator> to not create table dual:
+
+ my $sqlt_args = {
+ add_drop_table => 1,
+ parser_args => { sources => [ grep $_ ne 'Dual', schema->sources ] },
+ };
+ $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args );
Or use L<DBIx::Class::ResultClass::HashRefInflator>
=head2 Working with PostgreSQL array types
-If your SQL::Abstract version (>= 1.50) supports it, you can assign to
-PostgreSQL array values by passing array references in the C<\%columns>
-(C<\%vals>) hashref of the L<DBIx::Class::ResultSet/create> and
-L<DBIx::Class::Row/update> family of methods:
+You can also assign values to PostgreSQL array columns by passing array
+references in the C<\%columns> (C<\%vals>) hashref of the
+L<DBIx::Class::ResultSet/create> and L<DBIx::Class::Row/update> family of
+methods:
$resultset->create({
numbers => [1, 2, 3]
$resultset->search(
{
- numbers => \[ '= ?', [1, 2, 3] ]
+ numbers => \[ '= ?', [numbers => [1, 2, 3]] ]
}
);
See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
-placeholders and bind values (subqueries)> for more explanation.
+placeholders and bind values (subqueries)> for more explanation. Note that
+L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
+the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
+arrayrefs together with the column name, like this: C<< [column_name => value]
+>>.
=head1 BOOTSTRAPPING/MIGRATING
statement and dig down to see if certain parameters cause aberrant behavior.
You might want to check out L<DBIx::Class::QueryLog> as well.
+=head1 STARTUP SPEED
+
+L<DBIx::Class|DBIx::Class> programs can have a significant startup delay
+as the ORM loads all the relevant classes. This section examines
+techniques for reducing the startup delay.
+
+These tips are are listed in order of decreasing effectiveness - so the
+first tip, if applicable, should have the greatest effect on your
+application.
+
+=head2 Statically Define Your Schema
+
+If you are using
+L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to build the
+classes dynamically based on the database schema then there will be a
+significant startup delay.
+
+For production use a statically defined schema (which can be generated
+using L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to dump
+the database schema once - see
+L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> and
+L<dump_directory|DBIx::Class::Schema::Loader/dump_directory> for more
+details on creating static schemas from a database).
+
+=head2 Move Common Startup into a Base Class
+
+Typically L<DBIx::Class> result classes start off with
+
+ use base qw/DBIx::Class/;
+ __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
+
+If this preamble is moved into a common base class:-
+
+ package MyDBICbase;
+
+ use base qw/DBIx::Class/;
+ __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
+ 1;
+
+and each result class then uses this as a base:-
+
+ use base qw/MyDBICbase/;
+
+then the load_components is only performed once, which can result in a
+considerable startup speedup for schemas with many classes.
+
+=head2 Explicitly List Schema Result Classes
+
+The schema class will normally contain
+
+ __PACKAGE__->load_classes();
+
+to load the result classes. This will use L<Module::Find|Module::Find>
+to find and load the appropriate modules. Explicitly defining the
+classes you wish to load will remove the overhead of
+L<Module::Find|Module::Find> and the related directory operations:-
+
+ __PACKAGE__->load_classes(qw/ CD Artist Track /);
+
+If you are instead using the L<load_namespaces|DBIx::Class::Schema/load_namespaces>
+syntax to load the appropriate classes there is not a direct alternative
+avoiding L<Module::Find|Module::Find>.
+
+=head1 MEMORY USAGE
+
+=head2 Cached statements
+
+L<DBIx::Class> normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>.
+This is normally a good idea, but if too many statements are cached, the database may use too much
+memory and may eventually run out and fail entirely. If you suspect this may be the case, you may want
+to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash:
+
+ # print all currently cached prepared statements
+ print for keys %{$schema->storage->dbh->{CachedKids}};
+ # get a count of currently cached prepared statements
+ my $count = scalar keys %{$schema->storage->dbh->{CachedKids}};
+
+If it's appropriate, you can simply clear these statements, automatically deallocating them in the
+database:
+
+ my $kids = $schema->storage->dbh->{CachedKids};
+ delete @{$kids}{keys %$kids} if scalar keys %$kids > 100;
+
+But what you probably want is to expire unused statements and not those that are used frequently.
+You can accomplish this with L<Tie::Cache> or L<Tie::Cache::LRU>:
+
+ use Tie::Cache;
+ use DB::Main;
+ my $schema = DB::Main->connect($dbi_dsn, $user, $pass, {
+ on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 },
+ });
=cut