This results in something like the following C<WHERE> clause:
- WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
+ WHERE artist LIKE ? AND title LIKE ?
+
+And the following bind values for the placeholders: C<'%Lamb%'>, C<'%Fear of
+Fours%'>.
Other queries might require slightly more complex logic:
be optimized for your database in a special way, but you still want to
get the results as a L<DBIx::Class::ResultSet>.
-The recommended way to accomplish this is by defining a separate
-L<ResultSource::View|DBIx::Class::ResultSource::View> for your query.
+This is accomplished by defining a
+L<ResultSource::View|DBIx::Class::ResultSource::View> for your query,
+almost like you would define a regular ResultSource.
package My::Schema::Result::UserFriendsComplex;
use strict;
# ->table, ->add_columns, etc.
+ # do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);
+
__PACKAGE__->result_source_instance->view_definition(q[
SELECT u.* FROM user u
INNER JOIN user_friends f ON u.id = f.user_id
Note that you cannot have bind parameters unless is_virtual is set to true.
+=over
+
+=item * NOTE
+
+If you're using the old deprecated C<< $rsrc_instance->name(\'( SELECT ...') >>
+method for custom SQL execution, you are highly encouraged to update your code
+to use a virtual view as above. If you do not want to change your code, and just
+want to suppress the deprecation warning when you call
+L<DBIx::Class::Schema/deploy>, add this line to your source definition, so that
+C<deploy> will exclude this "table":
+
+ sub sqlt_deploy_hook { $_[1]->schema->drop_table ($_[1]) }
+
+=back
+
=head2 Using specific columns
When you only want specific columns from a table, you can use
# SELECT name name, LENGTH( name )
# FROM artist
-Note that the C< as > attribute has absolutely nothing to with the sql
-syntax C< SELECT foo AS bar > (see the documentation in
-L<DBIx::Class::ResultSet/ATTRIBUTES>). If your alias exists as a
-column in your base class (i.e. it was added with C<add_columns>), you
-just access it as normal. Our C<Artist> class has a C<name> column, so
-we just use the C<name> accessor:
+Note that the C<as> attribute B<has absolutely nothing to do> with the sql
+syntax C< SELECT foo AS bar > (see the documentation in
+L<DBIx::Class::ResultSet/ATTRIBUTES>). You can control the C<AS> part of the
+generated SQL via the C<-as> field attribute as follows:
+
+ my $rs = $schema->resultset('Artist')->search(
+ {},
+ {
+ join => 'cds',
+ distinct => 1,
+ '+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
+ '+as' => [qw/num_cds/],
+ order_by => { -desc => 'amount_of_cds' },
+ }
+ );
+
+ # Equivalent SQL
+ # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
+ # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
+ # GROUP BY me.artistid, me.name, me.rank, me.charfield
+ # ORDER BY amount_of_cds DESC
+
+
+If your alias exists as a column in your base class (i.e. it was added with
+L<add_columns|DBIx::Class::ResultSource/add_columns>), you just access it as
+normal. Our C<Artist> class has a C<name> column, so we just use the C<name>
+accessor:
my $artist = $rs->first();
my $name = $artist->name();
# Or use DBIx::Class::AccessorGroup:
__PACKAGE__->mk_group_accessors('column' => 'name_length');
+See also L</Using SQL functions on the left hand side of a comparison>.
+
=head2 SELECT DISTINCT with multiple columns
my $rs = $schema->resultset('Artist')->search(
The following will B<not> work:
my $rs = $schema->resultset('CD')->search({
- artist_id => $inside_rs->get_column('id')->as_query,
+ artist_id => $inside_rs->get_column('id')->as_query, # does NOT work
});
=head3 Support
=head2 Using SQL functions on the left hand side of a comparison
-Using SQL functions on the left hand side of a comparison is generally
-not a good idea since it requires a scan of the entire table. However,
+Using SQL functions on the left hand side of a comparison is generally not a
+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.
If you do not have quoting on, simply include the function in your search
$rs->search({ 'YEAR(date_of_birth)' => 1979 });
-With quoting on, or for a more portable solution, use the C<where>
-attribute:
-
- $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' });
-
-=begin hidden
+With quoting on, or for a more portable solution, use literal SQL values with
+placeholders:
-(When the bind args ordering bug is fixed, this technique will be better
-and can replace the one above.)
+ $rs->search(\[ 'YEAR(date_of_birth)', [ dummy => 1979 ] ]);
-With quoting on, or for a more portable solution, use the C<where> and
-C<bind> attributes:
+ # Equivalent SQL:
+ # SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
- $rs->search({}, {
- where => \'YEAR(date_of_birth) = ?',
- bind => [ 1979 ]
+ $rs->search({
+ name => 'Bob',
+ -nest => \[ 'YEAR(date_of_birth)', [ dummy => 1979 ] ],
});
-=end hidden
+ # Equivalent SQL:
+ # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
+
+See also L<SQL::Abstract/Literal SQL with placeholders and bind values
+(subqueries)>.
=head1 JOINS AND PREFETCHING
Alternatively, you can send the conversion sql scripts to your
customers as above.
-=head2 Setting quoting for the generated SQL.
+=head2 Setting quoting for the generated SQL
If the database contains column names with spaces and/or reserved words, they
need to be quoted in the SQL queries. This is done using:
- __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
- __PACKAGE__->storage->sql_maker->name_sep('.');
+ $schema->storage->sql_maker->quote_char([ qw/[ ]/] );
+ $schema->storage->sql_maker->name_sep('.');
The first sets the quote characters. Either a pair of matching
brackets, or a C<"> or C<'>:
- __PACKAGE__->storage->sql_maker->quote_char('"');
+ $schema->storage->sql_maker->quote_char('"');
Check the documentation of your database for the correct quote
characters to use. C<name_sep> needs to be set to allow the SQL
}
)
+In some cases, quoting will be required for all users of a schema. To enforce
+this, you can also overload the C<connection> method for your schema class:
+
+ sub connection {
+ my $self = shift;
+ my $rv = $self->next::method( @_ );
+ $rv->storage->sql_maker->quote_char([ qw/[ ]/ ]);
+ $rv->storage->sql_maker->name_sep('.');
+ return $rv;
+ }
+
=head2 Setting limit dialect for SQL::Abstract::Limit
In some cases, SQL::Abstract::Limit cannot determine the dialect of
projects / dbsrgits/DBIx-Class.git / blobdiff