如何使用SQLite使DBIx:：Class按顺序忽略大小写？

Question

通常SQLite的排序规则区分大小写。所有大写字母都在小写字母之前。但是可以在ORDER BY子句中告诉SQLite忽略这一点，方法是：

... ORDER BY foo COLLATE NOCASE ASC

但是我们如何使用DBIx:：Class来实现这一点呢？

考虑下面的示例，它将一个SQLite数据库部署到内存中，表中包含“代码> Foo”和“一个目录<代码>栏<代码>。连接使用quote_name设置。它填充值zzbbaa，然后使用结果集上的all将它们取出来。我将在以下所有示例中使用此设置。您需要DBIx:：Class和DBD:：SQLite来运行它。

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

但如果直接使用底层DBI句柄进行查询，我也可以使用COLLATE NOCASE。

$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中进行转换。

在使用SQLite订购时，我如何告诉DBIx:：Class使用COLLATE NOCASE？

以下情况不起作用：

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

只有当quote_names未打开时，这才有效。

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

它将使用上述示例代码生成此查询和错误消息。

选择me. bar from"foo"me"ORDER BY"me". bar COLLATE NOCASE"ASC： DBIx::Class::Storage::DB::_prepare_sth（）： DBI异常：DBD::SQLite::db prepare_cached失败：没有这样的列：me.barCOLLATE NOCASE[for语句"SELECT"me"."bar"from"foo"me"ORDER BY"me"."bar COLLATE NOCASE"ASC"]

或者我可以通过使用DBIC将ORDER by子句中的upper或lower转换为。

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时也会抛出一个错误。

选择“我”。从“foo”到“me”的顺序是“lower（me”。“bar）”ASC:DBIx:：Class:：Storage:：DBI:：_prepare_sth（）：DBI异常：DBD:：SQLite:：db prepare_cached失败：没有这样的列：lower（me.bar）[对于语句“SELECT”me.]从“foo”到“me”，用“lower（me）”来表示“bar”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=