my $rs = $cdrs->search({
year => {
'=' => $cdrs->search(
- { artist_id => { '=' => \'me.artist_id' } },
+ { artist_id => { '=' => { -ident => 'me.artist_id' } } },
{ alias => 'inner' }
)->get_column('year')->max_rs->as_query,
},
good idea since it requires a scan of the entire table. (Unless your RDBMS
supports indexes on expressions - including return values of functions - and
you create an index on the return value of the function in question.) However,
-it can be accomplished with C<DBIx::Class> when necessary.
-
-Your approach for doing so will depend on whether you have turned
-quoting on via the C<quote_char> and C<name_sep> attributes. If you
-explicitly defined C<quote_char> and C<name_sep> in your
-C<connect_info> (see L<DBIx::Class::Storage::DBI/"connect_info">) then
-you are using quoting, otherwise not.
-
-If you do not have quoting on, simply include the function in your search
-specification as you would any column:
-
- $rs->search({ 'YEAR(date_of_birth)' => 1979 });
-
-With quoting on, or for a more portable solution, use literal SQL values with
-placeholders:
+it can be accomplished with C<DBIx::Class> when necessary by resorting to
+literal SQL:
$rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
# Equivalent SQL:
# SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
- $rs->search({
+ $rs->search({ -and => [
name => 'Bob',
- -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
- });
+ \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
+ ]});
# Equivalent SQL:
# SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part
should be either the same as the name of the column (do this if the type of the
-return value of the function is the same as the type of the column) or
-otherwise it's essentially a dummy string currently (use C<plain_value> as a
-habit). It is used by L<DBIx::Class> to handle special column types.
+return value of the function is the same as the type of the column) or in the
+case of a function it's currently treated as a dummy string (it is a good idea
+to use C<plain_value> or something similar to convey intent). The value is
+currently only significant when handling special column types (BLOBs, arrays,
+etc.), but this may change in the future.
See also L<SQL::Abstract/Literal SQL with placeholders and bind values
(subqueries)>.
Check the documentation of your database for the correct quote
characters to use. C<name_sep> needs to be set to allow the SQL
-generator to put the quotes the correct place.
+generator to put the quotes the correct place, and defaults to
+C<.> if not supplied.
In most cases you should set these as part of the arguments passed to
L<DBIx::Class::Schema/connect>:
return $rv;
}
-=head2 Setting limit dialect for SQL::Abstract::Limit
-
-In some cases, SQL::Abstract::Limit cannot determine the dialect of
-the remote SQL server by looking at the database handle. This is a
-common problem when using the DBD::JDBC, since the DBD-driver only
-know that in has a Java-driver available, not which JDBC driver the
-Java component has loaded. This specifically sets the limit_dialect
-to Microsoft SQL-server (See more names in SQL::Abstract::Limit
--documentation.
-
- __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
-
-The JDBC bridge is one way of getting access to a MSSQL server from a platform
-that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
-
-The limit dialect can also be set at connect time by specifying a
-C<limit_dialect> key in the final hash as shown above.
-
=head2 Working with PostgreSQL array types
You can also assign values to PostgreSQL array columns by passing array
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]
->>.
+arrayrefs together with the column name, like this:
+C<< [column_name => value] >>.
+
+=head2 Formatting DateTime objects in queries
+
+To ensure C<WHERE> conditions containing L<DateTime> arguments are properly
+formatted to be understood by your RDBMS, you must use the C<DateTime>
+formatter returned by L<DBIx::Class::Storage::DBI/datetime_parser> to format
+any L<DateTime> objects you pass to L<search|DBIx::Class::ResultSet/search>
+conditions. Any L<Storage|DBIx::Class::Storage> object attached to your
+L<Schema|DBIx::Class::Schema> provides a correct C<DateTime> formatter, so
+all you have to do is:
+
+ my $dtf = $schema->storage->datetime_parser;
+ my $rs = $schema->resultset('users')->search(
+ {
+ signup_date => {
+ -between => [
+ $dtf->format_datetime($dt_start),
+ $dtf->format_datetime($dt_end),
+ ],
+ }
+ },
+ );
+
+Without doing this the query will contain the simple stringification of the
+C<DateTime> object, which almost never matches the RDBMS expectations.
+
+This kludge is necessary only for conditions passed to
+L<DBIx::Class::ResultSet/search>, whereas
+L<create|DBIx::Class::ResultSet/create>,
+L<find|DBIx::Class::ResultSet/find>,
+L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are all
+L<DBIx::Class::InflateColumn>-aware and will do the right thing when supplied
+an inflated C<DateTime> object.
=head2 Using Unicode
For example, say that you have three columns, C<id>, C<number>, and
C<squared>. You would like to make changes to C<number> and have
C<squared> be automagically set to the value of C<number> squared.
-You can accomplish this by overriding C<store_column>:
+You can accomplish this by wrapping the C<number> accessor with
+L<Class::Method::Modifiers>:
+
+ around number => sub {
+ my ($orig, $self) = (shift, shift);
- sub store_column {
- my ( $self, $name, $value ) = @_;
- if ($name eq 'number') {
- $self->squared($value * $value);
+ if (@_) {
+ my $value = $_[0];
+ $self->squared( $value * $value );
}
- $self->next::method($name, $value);
+
+ $self->next::method(@_);
}
Note that the hard work is done by the call to C<next::method>, which
redispatches your call to store_column in the superclass(es).
+Generally, if this is a calculation your database can easily do, try
+and avoid storing the calculated value, it is safer to calculate when
+needed, than rely on the data being in sync.
+
=head2 Automatically creating related objects
You might have a class C<Artist> which has many C<CD>s. Further, you
projects / dbsrgits/DBIx-Class.git / blobdiff