Typically SQLite's collation sorts case-sensitive. All capital letters come before small letters. But it's possible to tell SQLite in the ORDER BY clause to ignore that, by doing this:
... ORDER BY foo COLLATE NOCASE ASC
But how do we do this with DBIx::Class?
Consider the following example, which deploys an SQLite database in memory with a table foo and one comlumn bar. The connection uses the quote_names setting. It fills in the values z Z b B a A and then gets them back out using all on the ResultSet. I'll be using this setup in all my following examples. You need DBIx::Class and DBD::SQLite to run this.
use strict;
use warnings;
package Foo::Schema::Result::Foo;
use base 'DBIx::Class::Core';
__PACKAGE__->table("foo");
__PACKAGE__->add_columns( "bar", { data_type => "text" }, );
package Foo::Schema;
use base 'DBIx::Class::Schema';
__PACKAGE__->register_class( 'Foo' => 'Foo::Schema::Result::Foo' );
package main;
my $schema = Foo::Schema->connect(
{
dsn => 'dbi:SQLite:dbname=:memory:',
quote_names => 1,
}
);
$schema->deploy;
$schema->resultset('Foo')->create( { bar => $_ } ) for qw(z Z b B a A);
my @all = $schema->resultset('Foo')->search(
{},
{
order_by => { -asc => 'me.bar' },
},
)->all;
# example code starts here
print join q{ }, map { $_->bar } @all;
The output of this is sorted case-sensitive.
A B Z a b z
Now of course I could sort it with Perl and make it case-insensitive, like this.
print join q{ }, sort { lc $a cmp lc $b } map { $_->bar } @all;
Now I get
A a B b Z z
But I can also use the COLLATE NOCASE if I query using the underlying DBI handle directly.
$schema->storage->dbh_do(
sub {
my ( $storage, $dbh, @args ) = @_;
my $res = $dbh->selectall_arrayref(
"SELECT * FROM foo ORDER BY bar COLLATE NOCASE ASC"
);
print "$_->[0] " for @$res;
}
This gives us
a A b B z Z
I want DBIC to use the COLLATE NOCASE, but without running any SQL. I do not want to do any expensive string conversions in the ORDER BY, or do them later in Perl.
How do I tell DBIx::Class to use the COLLATE NOCASE when ordering with SQLite?
The following does not work:
order_by => { '-collate nocase asc' => 'me.bar' },
And this only works if quote_names is not turned on.
order_by => { -asc => 'me.bar COLLATE NOCASE' },
It will produce this query and error message with the above example code.
SELECT "me"."bar" FROM "foo" "me" ORDER BY "me"."bar COLLATE NOCASE" ASC: DBIx::Class::Storage::DBI::_prepare_sth(): DBI Exception: DBD::SQLite::db prepare_cached failed: no such column: me.bar COLLATE NOCASE [for Statement "SELECT "me"."bar" FROM "foo" "me" ORDER BY "me"."bar COLLATE NOCASE" ASC"]
Or I could do it by converting to upper or lower in the ORDER BY clause using DBIC.
my @all = $schema->resultset('Foo')->search(
{},
{
order_by => { -asc => 'lower(me.bar)' },
},
)->all;
print join q{ }, map { $_->bar } @all;
This gives
a A b B z Z
without quote_names which is similar, but the other way around. (That's not my concern here), but also throws an error when quote_names is turned on.
SELECT "me"."bar" FROM "foo" "me" ORDER BY "lower(me"."bar)" ASC: DBIx::Class::Storage::DBI::_prepare_sth(): DBI Exception: DBD::SQLite::db prepare_cached failed: no such column: lower(me.bar) [for Statement "SELECT "me"."bar" FROM "foo" "me" ORDER BY "lower(me"."bar)" ASC"]
