perl - DBIx::Class - 使用预取获取用作条件的所有关系？

Question

这是三个表:product , model , 和 product_model以 N:M 关系映射产品和模型。

product          product_model            model

id name          product_id model_id      id name
------------     -------------------      ----------
p1 Product 1     p1         m1            m1 Model 1
p2 Product 2     p2         m1            m2 Model 2
...              p2         m2

我想做什么:找到所有支持模型 2 的产品(例如 product 2)。然后，对于每个产品，显示该产品支持的模型 ID 列表( product 2 => [ m1 , m2 ])

这是我的第一次尝试。我还需要 N 个查询来搜索 model_id s 每个产品。

# 1 query for searching products
my @products = $schema->resultset('Product')->search(
   { 'product_models.model_id' => 'm2' },
   { 'join' => 'product_model' },
)
# N queries for searching product_models for each product
foreach my $product ( @products ) {
   my @model_ids = map { $_->model_id } $product->product_models;
   # @model_ids = ( 'm1', 'm2' ) for p2
}

我寻找一种只使用一个查询来获取结果的方法。更换 join与 prefetch没有用。

my @products = $schema->resultset('Product')->search(
   { 'product_models.model_id' => 'm2' },
   { 'prefetch' => 'product_model' },      # here
)

# no additional queries, but...
foreach my $product ( @products ) {
   my @model_ids = map { $_->model_id } $product->product_models;
   # now, @model_ids contains only ( `m2` )
}

接下来，我尝试了“预取同一张表两次”:

my @products = $schema->resultset('Product')->search(
    { 'product_models.model_id' => 'm2' },
    { 'prefetch' => [ 'product_models', 'product_models' ] },
);

foreach my $product ( @products ) {
    my @model_ids = map { $_->model_id } $product->product_models;
}

看来我成功了。只执行了一个查询，我从中获取了所有模型 ID。

但是我不太确定这是正确的(？)方式。这是正确的方法吗？

例如，如果我使用 join而不是 prefetch ing, Product 2在循环中出现两次。我明白这一点，因为连接表是这样的:

id name      p_m.p_id p_m.m_id p_m_2.p_id p_m_2.m_id
p2 Product 2 p2       m2       p2         m1
p2 Product 2 p2       m2       p2         m2   -- Product 2, one more time

为什么 Product 2当我使用 prefetch 时只出现一次?

结果查询几乎相同，除了 SELECT领域:

SELECT "me"."id", "me"."name",
 "product_models"."product_id", "product_models"."model_id",    -- only in prefetch
 "product_models_2"."product_id", "product_models_2"."model_id" --
  FROM "product" "me"
  LEFT JOIN "product_model" "product_models"
    ON "product_models"."product_id" = "me"."id"
  LEFT JOIN "product_model" "product_models_2"
    ON "product_models_2"."product_id" = "me"."id"
WHERE "product_models"."model_id" = 'm2'

Answer 1

如果您的架构中有正确的关系，则可以通过单个查询实现。但这很棘手。假设您的数据库如下所示:

CREATE TABLE product
 (`id` VARCHAR(2) PRIMARY KEY, `name` VARCHAR(9))
;
INSERT INTO product
 (`id`, `name`) VALUES
 ('p1', 'Product 1'),
 ('p2', 'Product 2')
;
CREATE TABLE product_model (
  `product_id` VARCHAR(2),
  `model_id` VARCHAR(2),
  PRIMARY KEY (product_id, model_id),
  FOREIGN KEY(product_id) REFERENCES product(id),
  FOREIGN KEY(model_id) REFERENCES model(id)
)
;
INSERT INTO product_model
 (`product_id`, `model_id`) VALUES
 ('p1', 'm1'),
 ('p2', 'm1'),
 ('p2', 'm2')
;
CREATE TABLE model
 (`id` VARCHAR(2) PRIMARY KEY, `name` VARCHAR(7))
;
INSERT INTO model
 (`id`, `name`) VALUES
 ('m1', 'Model 1'),
 ('m2', 'Model 2')
;

这基本上是您从问题中获得的数据库。我添加了主键和外键。反正你可能有这些。

我们现在可以从中创建一个架构。我写了一个简单的程序，它使用 DBIx::Class::Schema::Loader要做到这一点。它动态创建一个 SQLite 数据库。 (如果没有人把这个放在 CPAN 上，我会的)。

上面的 SQL 将进入 __DATA__部分。

use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/ make_schema_at /;

# create db
unlink 'foo.db';
open my $fh, '|-', 'sqlite3 foo.db' or die $!;
print $fh do { local $/; <DATA> };
close $fh;

$ENV{SCHEMA_LOADER_BACKCOMPAT} = 1;

# create schema
my $dsn = 'dbi:SQLite:foo.db';
make_schema_at(
    'DB',
    {
        #    debug => 1,
    },
    [ $dsn, 'sqlite', '', ],
);

$ENV{DBIC_TRACE} = 1;

# connect schema
my $schema = DB->connect($dsn);

# query goes here

__DATA__
# SQL from above

现在我们有了这个，我们可以专注于查询。起初这看起来很可怕，但我会尽力解释。

my $rs     = $schema->resultset('Product')->search(
    { 'product_models.model_id' => 'm2' },
    {
        'prefetch' => {
            product_models => {
                product_id => {
                    product_models => 'model_id'
                }
            }
        }
    },
);

while ( my $product = $rs->next ) {
    foreach my $product_model ( $product->product_models->all ) {
        my @models;
        foreach my $supported_model ( $product_model->product_id->product_models->all ) {
            push @models, $supported_model->model_id->id;
        }
        printf "%s: %s\n", $product->id, join ', ', @models;
    }
}

prefetch意味着加入这个关系，并保留数据以备后用。因此，要获得您产品的所有型号，我们必须编写

#          1                   2
{ prefetch => { product_models => 'product_id' } }

哪里 product_models是 N:M 表，而 product_id是与 Models 表的关系的名称。箭头 => 1 是来自 Product 的第一次加入至 ProductModel . 2 适用于 ProductModel回到每个具有 m2 型号的产品。有关说明，请参见 ER 模型的绘图。



现在我们想要拥有所有 ProductModel就是这个 Product已。那是箭头3。

#          1                   2               3
{ prefetch => { product_models => { product_id => 'product_models' } } }

最后，获得 Model对于 N:M 关系，我们必须使用 model_id带箭头 4 的关系。

{
    'prefetch' => {                            # 1
        product_models => {                    # 2
            product_id => {                    # 3
                product_models => 'model_id'   # 4
            }
        }
    }
},

查看 ER 模型图应该清楚这一点。请记住，这些连接中的每一个都是 LEFT OUTER默认情况下加入，因此它将始终获取所有行，而不会丢失任何内容。 DBIC 只会为您处理这些。

现在要访问所有这些，我们需要迭代。 DBIC 为我们提供了一些工具来做到这一点。

while ( my $product = $rs->next ) {

    #                                   1
    foreach my $product_model ( $product->product_models->all ) {
        my @models;

        #                                           2           3
        foreach my $supported_model ( $product_model->product_id->product_models->all ) {

            #                             4 
            push @models, $supported_model->model_id->id;
        }
        printf "%s: %s\n", $product->id, join ', ', @models;
    }
}

首先我们把所有的 ProductModel条目 (1)。对于每一个，我们取 Product (2).永远只有一个 Product在每一行中，因为这样我们有一个 1:N 关系，所以我们可以直接访问它。此 Product反过来有一个 ProductModel关系。那是 3。因为这是 N 边，所以我们需要取所有它们并进行迭代。然后我们推送 id每个 Model (4) 进入我们此产品的型号列表。之后，它只是打印。

这是查看它的另一种方式:



我们可以消除最后一个 model_id在 prefetch ，但是我们必须使用 get_column('model_id')获取ID。它将为我们节省一个连接。

现在，如果我们打开 DBIC_TRACE=1 ，我们得到这个 SQL 语句:

SELECT me.id, me.name, product_models.product_id, product_models.model_id, product_id.id, product_id.name, product_models_2.product_id, product_models_2.model_id, model_id.id, model_id.name
FROM product me
LEFT JOIN product_model product_models ON product_models.product_id = me.id
LEFT JOIN product product_id ON product_id.id = product_models.product_id
LEFT JOIN product_model product_models_2 ON product_models_2.product_id = product_id.id
LEFT JOIN model model_id ON model_id.id = product_models_2.model_id
WHERE (product_models.model_id = 'm2')
ORDER BY me.id

如果我们对我们的数据库运行它，我们有这些行:

p2|Product 2|p2|m2|p2|Product 2|p2|m1|m1|Model 1
p2|Product 2|p2|m2|p2|Product 2|p2|m2|m2|Model 2

当然，如果我们手动进行，那是非常无用的，但是 DBIC 的魔法确实对我们有帮助，因为所有奇怪的连接和组合都被完全抽象掉了，我们只需要一个查询即可获取所有数据。