sql - 如何在 DBIx::Class View 中构建动态 SQL 查询

Question

我想在 DBIx 中使用一个复杂的 SQL 语句。因此，我没有以通常的“抽象”方式构建它，而是想针对这种特殊情况使用 View (DBIx::Class::ResultSource::View)。

这是我对原始 DBI 代码的查询:

    SELECT a."key", CASE WHEN $language = ''
            THEN a."default" ELSE $language END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE a.regions LIKE '%,$region,%'
    AND b."displayPage" = ?
UNION
SELECT a."key", CASE WHEN $language = ''
            THEN a."default" ELSE $language END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE regions like '%,0,%'
    AND b."displayPage" = ?

所以在我看来它看起来像这样:

__PACKAGE__->result_source_instance->is_virtual(1);

__PACKAGE__->result_source_instance->view_definition(q[
SELECT a."key", CASE WHEN ? = ''
            THEN a."default" ELSE ? END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE a.regions LIKE '%,$region,%'
    AND b."displayPage" = ?
UNION
SELECT a."key", CASE WHEN ? = ''
            THEN a."default" ELSE ? END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE regions like '%,0,%'
    AND b."displayPage" = ?
]);

我尝试使用 bind 调用替换 $language，我用它来替换查询中的任何值:

    my @transl_hashrefs = $self->{schema}->resultset('View::Translation')
        ->search(
            {},
            {
                result_class => 'DBIx::Class::ResultClass::HashRefInflator',
                bind => [
                            $langmap,
                            $langmap,
                            $display_page,
                            $langmap,
                            $langmap,
                            $display_page,
                        ],
            } )
        ->all;

我应该提到，上述查询是在 Postgres 上调用的，因此工作 SQL 应该如下所示:

    SELECT a."key", CASE WHEN "lang1" = ''
            THEN a."default" ELSE "lang1" END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE a.regions LIKE '%,$region,%'
    AND b."displayPage" = 'home'
UNION
SELECT a."key", CASE WHEN "lang1" = ''
            THEN a."default" ELSE "lang1" END AS lan
FROM foo.regionlang as a
INNER JOIN foo."displayPageKeys"  as b ON a."key" = b."key"
WHERE regions like '%,0,%'
    AND b."displayPage" = 'home'

问题是我必须以某种方式在 SQL 中用双引号 ("") 获取 $language ("lang1") 的值。这不适用于 bind 方式，我猜这是为值而不是字段设计的。

还可以使用绑定(bind)传递变量，并在 View 中访问它们，如 $1、$2 等。但即便如此，我也不能将值放在双引号内。

对于最有可能、非常令人困惑的问题，我们深表歉意。我尽量把它说清楚。

非常感谢任何帮助!

干杯

Answer 1

我有一个类似的问题，其中普通 View 没有帮助(主要是因为如果您将 View 加入其他表，添加其他条件并限制接收的行，绑定(bind)可能会与参数混淆。

我最终采用的解决方案比您的需求复杂得多，但其根源是 Ovid 最近编写的一个名为 DBIx::Class::Report 的模块。它可能还没有在 CPAN 上，但它在 github 上，他在这里概述了它:http://blogs.perl.org/users/ovid/2015/03/dbixclassreport---generate-ad-hoc-dbic-classes-from-sql.html

有了这个，您可以使用任意 SQL 动态创建 View ，因此可以插入在绑定(bind)中不能正常工作的标识符，并且可以正常绑定(bind)其余标准以确保安全/性能。