sql - 如何在 DBIx::Class 中使用交叉表功能

Question

我正在尝试在 Catalyst 应用程序中使用 DBIx::Class 来创建将在交叉表列表中产生结果的 SQL 语句。这是我尝试生成的 SQL 示例。

select * from crosstab('
    select event_member_id, round, gross from results where event_id = 21 and round = 6 or round = 7 order by 1, 2')
    AS results(player integer, Saturday integer, Sunday integer)
;

我试图创建一个名为 cross_tab 的自定义方法，如下所示:

    sub cross_tab {
    my $self = shift;
    my $attributes = shift || {};

    $attributes->{'select'} = "* from crosstab('
        select event_member_id, round, gross from results where
        event_id = 21 and
        round = 6 or round = 7
        order by 1, 2')"
    ;`enter code here`

    $attributes->{'as'} = [
        'results(player integer, Saturday integer, Sunday integer)',
    ];

    return $self->search({}, $attributes);

}

这将创建如下所示的 SQL:

SELECT me.* from crosstab('
    select event_member_id, round, gross from results where 
    event_id = 21 and 
    round = 6 or round = 7 
    order by 1, 2') 
FROM results me;

这相当接近，但我不想在开头附加“我”，也不想添加 FROM 语句“FROM results me”。我假设这是通过搜索方法添加的。

除了“搜索”之外，是否有更好的 DBIx::Class 方法可供使用，或者是否有办法告诉 DBIx::Class 不要添加附加子句？

Answer 1

我不确定您为什么不想要其中的“我”。 DBIC 一直在添加它，它不会妨碍您处理结果集中的数据，因为它会被过滤掉。

要完成您想要完成的事情，最简单的方法可能是创建一个 virtual view .然后，您可以在该 View 中定义确切的查询，并直接在结果源上进行选择，就好像它是您架构中的任何其他表一样。

您也可以通过反向引用字符串语法(\'sql literal')使用子查询和一些自定义 SQL 获得类似的结果，尽管总的来说会更难。

应该这样做:

package MyApp::Schema::Result::VirtualCrosstab;

use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

__PACKAGE__->table('virtual_crosstab');
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(qq[
    select * from crosstab(
        select event_member_id, round, gross
        from results
        where event_id = 21 and round = 6 or round = 7 order by 1, 2
    )
    AS results(player integer, Saturday integer, Sunday integer)
;
]);
__PACKAGE__->add_columns(
  'player' => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  'saturday' => {
    data_type => 'integer',
  },
  'sunday' => {
    data_type => 'integer',
  },
);

1;