在 DBIx-Class 中使用预取时如何仅选择特定列? [英] How can I select only specific columns when using prefetch in DBIx-Class?

问题描述

我正在为相当基本的 DBIx 类预取用法而苦苦挣扎.我想限制使用预取时从联接表返回的列.

I'm struggling with fairly fundamental DBIx-Class prefetch usage. I want to limit the columns that are returned from joined tables when prefetch is used.

这个:

my $rs = $schema->resultset('CD')->search(
  {}, # No searching restrictions through WHERE clause
  {
    prefetch => [qw/ artist /],
    columns  => [qw/ title artist.name /],
  }
);

生成此 SQL:

SELECT cd.title, artist.*
FROM cd
JOIN artist ON cd.artist = artist.id

但我不想拖下所有的艺术家列，只是 cd.title 和 Artist.name 列(在这个例子中，我的实际用例更复杂).列功能似乎只适用于主表，而不适用于连接表.

But I don't want to haul down all of the artist columns, just the cd.title and artist.name columns (in this example, my real use case is more complex). The columns feature seems to only work on the primary table rather than than joined tables as well.

我想要这个 SQL:

SELECT cd.title, artist.name
FROM cd
JOIN artist ON cd.artist = artist.id

我刚刚使用 Catalyst/DBIx-Class 获得了我的海腿，所以我可能在这里忽略了一些非常明显的东西！

I'm just getting my sea-legs with Catalyst/DBIx-Class so I'm probably over-looking something blindingly obvious here!

推荐答案

是的，你说得对.只能选择主表中的列，不能获取连接表中的特定列.你需要的是加入.使用 join 和 '+select','+as' 属性，可以从两个表中选择特殊列.

Yes, you are right. You can only select columns in the primary table, and can not get specific columns in the joined tables. What you need is join. use join and '+select','+as' property, you can select special columns from two tables.

预取也用于从预取表中选择所有列.当您确实需要这些列时，使用预取会更有效，例如所以你可以做 $cd->artist->name 而不需要它做额外的查询.但是，如果您不需要这些列，那么加载这些数据就会造成不必要的性能损失.

Prefetch is used to select all columns from the prefetch tables as well. It is more efficient to use prefetch when you actually need those columns, e.g. so you can do $cd->artist->name without needing it to do the additional query. But if you don't need those columns then you have an unnecessary performance hit for loading up that data.

这篇关于在 DBIx-Class 中使用预取时如何仅选择特定列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！