How do I make DBIx::Class ignore case in ORDER BY with SQLite?

Question

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"]

Answer 1

If you're comfortable using a very small amount of SQL, you can pass a scalar reference to denote literal SQL, and DBIC won't mess with it:

order_by => \'me.bar COLLATE NOCASE ASC'

Or, with just the bare minimum amount of literal SQL:

order_by => { -asc => \'me.bar COLLATE NOCASE' }

Note that this syntax is technically discouraged, but I don't know of any other way to achieve what you're after:

The old scalarref syntax (i.e. order_by => \'year DESC') is still supported, although you are strongly encouraged to use the hashref syntax as outlined above.