How to make DBIx :: Class ignore case in ORDER BY using SQLite?

Question

Typically, SQLite collation sorts case-sensitive. All uppercase letters come in small letters. But you can tell SQLite in the sentence ORDER BYto ignore this by doing this :

... ORDER BY foo COLLATE NOCASE ASC

But how do we do it with DBIx :: Class?

Consider the following example, which deploys a SQLite database in memory with a table fooand one comment. bar. The connection uses a parameterquote_names . It fills in the values z Z b B a Aand then returns them using allin the ResultSet. I will use this setting in all of 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;

.

A B Z a b z

, , Perl , .

print join q{ }, sort { lc $a cmp lc $b } map { $_->bar } @all;

A a B b Z z

COLLATE NOCASE, DBI.

$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;
    }

a A b B z Z  

, DBIC COLLATE NOCASE, SQL. ORDER BY Perl.

DBIx:: Class COLLATE NOCASE SQLite?

---

:

order_by => { '-collate nocase asc' => 'me.bar' },

, quote_names .

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

.

SELECT "me" . "bar" FROM "foo" "me" ORDER BY "me" . "bar COLLATE NOCASE" ASC: DBIx:: Class:: Storage:: DBI:: _ prepare_sth(): DBI: DBD:: SQLite:: db prepare_cached : : me.bar COLLATE NOCASE [for Statement "SELECT" me "." Bar "FROM" foo "me" ORDER BY "me" . "bar COLLATE NOCASE" ASC "]

, upper lower ORDER BY DBIC.

my @all = $schema->resultset('Foo')->search(
    {},
    {
        order_by => { -asc => 'lower(me.bar)' },
    },
)->all;

print join q{ }, map { $_->bar } @all;

A a B b Z z

quote_names, , . ( ), quote_names.

SELECT "me" . "bar" FROM "foo" "me" ORDER BY "lower (me". "bar)" ASC: DBIx:: Class:: Storage:: DBI:: _ prepare_sth(): DBI: DBD:: SQLite:: db prepare_cached : : lower (me.bar) [for Statement "SELECT" me "." bar "FROM" foo "me" ORDER BY "lower (me". "bar)" ASC "]

Answer 1

SQL, , SQL, DBIC :

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

, SQL:

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

, , , :

scalarref (.. order_by = > \'year DESC') - , hashref , .