如何在同一列上使用 2 个条件过滤 Perl DBIx 记录集?
我正在接触 DBIx::Class — 到目前为止我很喜欢它。
我遇到的一个问题是我想查询记录,过滤掉不在特定日期范围内的记录。
我花了一段时间才弄清楚如何进行 <=
类型的匹配而不是相等匹配:
my $start_criteria = ">= $start_date";
my $end_criteria = "<= $end_date";
my $result = $schema->resultset('MyTable')->search(
{
'status_date' => \$start_criteria,
'status_date' => \$end_criteria,
});
明显的问题是,由于过滤器位于散列中,因此我要覆盖“status_date”的值,并且仅搜索 status_date <= $end_date 的位置。执行的 SQL 是:
SELECT me.* from MyTable me where status_date <= '9999-12-31'
我搜索了 CPAN、Google 和 SO,但无法弄清楚如何将 2 个条件应用于同一列。我能找到的所有文档都显示了如何过滤超过 1 列,但不在同一列上过滤 2 个条件。
我确信我错过了一些明显的东西。这里有人可以向我指出吗?
I'm getting my feet wet in DBIx::Class — loving it so far.
One problem I am running into is that I want to query records, filtering out records that aren't in a certain date range.
It took me a while to find out how to do a <=
type of match instead of an equality match:
my $start_criteria = ">= $start_date";
my $end_criteria = "<= $end_date";
my $result = $schema->resultset('MyTable')->search(
{
'status_date' => \$start_criteria,
'status_date' => \$end_criteria,
});
The obvious problem with this is that since the filters are in a hash, I am overwriting the value for "status_date", and am only searching where the status_date <= $end_date. The SQL that gets executed is:
SELECT me.* from MyTable me where status_date <= '9999-12-31'
I've searched CPAN, Google and SO and haven't been able to figure out how to apply 2 conditions to the same column. All documentation I've been able to find shows how to filter on more than 1 column, but not 2 conditions on the same column.
I'm sure I'm missing something obvious. Can someone here point it out to me?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
IIRC,您应该能够传递多个搜索条件的数组引用(每个搜索条件都有自己的哈希引用)。例如:
编辑:哎呀,神经病。它执行 OR,而不是 AND。
看起来正确的方法是为单个
status_date
提供哈希引用:这些内容记录在 SQL::Abstract,DBIC 在底层使用它。
IIRC, you should be able to pass an array reference of multiple search conditions (each in its own hashref.) For example:
Edit: Oops, nervermind. That does an OR, as opposed to an AND.
It looks like the right way to do it is to supply a hashref for a single
status_date
:This stuff is documented in SQL::Abstract, which DBIC uses under the hood.
SQL 中有 BETWEEN ,DBIx::Class 也支持它:
There is
BETWEEN
in SQL and in DBIx::Class it's supported: