perl - DBIx::Class 连接生成大量的 select 语句

Question

我注意到当我在 DBIx::Class 中进行选择时，我得到了大量的 SELECT 语句。例如，我有一个如下所示的查询:

 my $testSetRs = $db->resultset('Testset')->search(
    {%searchCriteria},
    {
        prefetch  => [
            {
                'ax_group_testsets' => {'ax_group' => 'ax_user_groups'},
            },
            {'ax_user_testsets'  => 'ax_user'},
        ],
        '+columns' => [
            {
                'me.ax_group_testsets.ax_group_id'     => 'ax_group_testsets.ax_group_id',
                'me.ax_group_testsets.ax_group.public' => 'ax_group.public',
                'me.ax_group_testsets.ax_group.name'   => 'ax_group.name',
            },
        ],
        collapse => 1,
        group_by => ['me.ts_id', 'me.ts_name'],
        cache    => 1,
    }
);

生成:

SELECT me.ts_id, me.ts_name 
FROM testset me 
LEFT JOIN ax_group_testset ax_group_testsets ON ax_group_testsets.ts_id = me.ts_id 
LEFT JOIN ax_group ax_group ON ax_group.ax_group_id = ax_group_testsets.ax_group_id 
LEFT JOIN ax_user_group ax_user_groups ON ax_user_groups.ax_group_id = ax_group.ax_group_id 
LEFT JOIN ax_user_testset ax_user_testsets ON ax_user_testsets.ts_id = me.ts_id 
LEFT JOIN ax_user ax_user ON ax_user.ax_user_id = ax_user_testsets.ax_user_id 
WHERE ( ( ax_user.ax_user_id = ? OR ax_user_groups.ax_user_id = ? ) ) 
GROUP BY me.ts_id, me.ts_name: '349', '349'

SELECT me.ax_group_id, ax_group.public, ax_group.name FROM ax_group_testset me  JOIN ax_group ax_group ON ax_group.ax_group_id = me.ax_group_id WHERE ( me.ts_id = ? ): '4476'

SELECT me.ax_group_id, ax_group.public, ax_group.name FROM ax_group_testset me  JOIN ax_group ax_group ON ax_group.ax_group_id = me.ax_group_id WHERE ( me.ts_id = ? ): '4566'

SELECT me.ax_group_id, ax_group.public, ax_group.name FROM ax_group_testset me  JOIN ax_group ax_group ON ax_group.ax_group_id = me.ax_group_id WHERE ( me.ts_id = ? ): '4701'

等等

第一个SELECT，我要。它之后的那些困扰着我。每个组(40 人以上)都有一个。这发生在我所有的连接上，并且可以通过正确的查询跳到数百个。为什么会生成这么多查询？我该怎么办？

我正在运行 Perl ActiveState 5.16 和 DBIx .08250。

Answer 1

您的答案在于预取属性，如 DBIx::Class::Manual::Joining 中所述和 DBIx::Class::ResultSet文档。

添加

  prefetch   => {'ax_group_testsets' => {'ax_group' => 'ax_user_groups'} },

并且，假设您想要所有三个相关的记录列:

  '+columns' => [ 
          { 
             'me.ax_group_testsets.ax_group_id'     => 'ax_group_testsets.ax_group_id',
             'me.ax_group_testsets.ax_group.public' => 'ax_group.public',
             'me.ax_group_testsets.ax_group.name'   => 'ax_group.name',
          },
      ],
   collapse => 1,

到查询属性散列。