有条件加入学说？

发布于 2024-11-19 18:33:04 字数 1273 浏览 1 评论 0原文

我有两张表，位置和预测。我想查询数据库以查找与每天的预测参数匹配的所有位置。

例如，伦敦已预测 2011-07-13、2011-07-14、2011-07-15 和 2011-07-16 的条目。如果 2011 年 7 月 14 日和 2011 年 7 月 15 日温度不高于 40 且不低于 13，我希望返回伦敦。

我已经设法在 MySQL 中解决这个问题（我认为）： <代码>

SELECT f1.day, f1.temperature_high, f1.temperature_low, f2.day, f2.temperature_high, f2.temperature_low, l.name 
FROM location l
INNER JOIN forecast f1 ON 
  f1.location_id = l.id AND 
  f1.day = '2011-07-14' AND 
  f1.temperature_high <= '40' AND 
  f1.temperature_low >= '13'
INNER JOIN forecast f2 ON 
  f2.location_id = l.id AND 
  f2.day = '2011-07-15' AND 
  f2.temperature_high <= '40' AND 
  f2.temperature_low >= '13';

我试图将其转化为 Doctrine，但出现内存耗尽错误。我的学说查询： <代码>

$this->results = Doctrine_Query::create()->select('l.name')->from('Location l');

 foreach ($values['day'] as $i => $day) {
  $this->结果->innerJoin('l.Forecasts f'.$i.' ON 
    f'.$i.'.condition_id IN (' . implode(',', $values['condition']) . ') AND 
    f'.$i.'.温度高 <= ' .$values['温度最大'] 。 ' 和 
    f'.$i.'.温度低 >= ' 。 $值['温度_最小值']);
}
$this->结果->execute();

什么是正确的&什么是正确的？运行此查询的最有效方法？我很确定我正在做一些根本性错误的事情。

提前非常感谢
皮特

原文

I've two tables, location and forecast. I'd like to query the database to find all locations that match forecast parameters for each day.

e.g. London has forecast entries for the days 2011-07-13, 2011-07-14, 2011-07-15, and 2011-07-16. I'd like London to be returned if on 2011-07-14 AND 2011-07-15 the temperature is not higher than 40 and not lower than 13.

I've managed to work this out in MySQL (I think):

SELECT f1.day, f1.temperature_high, f1.temperature_low, f2.day, f2.temperature_high, f2.temperature_low, l.name 
FROM location l
INNER JOIN forecast f1 ON 
  f1.location_id = l.id AND 
  f1.day = '2011-07-14' AND 
  f1.temperature_high <= '40' AND 
  f1.temperature_low >= '13'
INNER JOIN forecast f2 ON 
  f2.location_id = l.id AND 
  f2.day = '2011-07-15' AND 
  f2.temperature_high <= '40' AND 
  f2.temperature_low >= '13';

I attempted to translate this into Doctrine, but I'm getting memory exhausted errors. My Doctrine query:

$this->results = Doctrine_Query::create()->select('l.name')->from('Location l');

foreach ($values['day'] as $i => $day) {
  $this->results->innerJoin('l.Forecasts f'.$i.' ON 
    f'.$i.'.condition_id IN (' . implode(',', $values['condition']) . ') AND 
    f'.$i.'.temperature_high <= ' .$values['temperature_max'] . ' AND 
    f'.$i.'.temperature_low >= ' . $values['temperature_min']);
}
$this->results->execute();

What is the correct & most efficient way of running this query? I'm pretty sure I'm doing something fundamentally wrong.

Many thanks in advance
Pete

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三寸金莲 2024-11-26 18:33:05

我认为使用 $query->addWhere('EXISTS (...)') 与每个条件的内部 select 语句会更容易。

$this->results = Doctrine_Query::create()->select('name')->from('location');

foreach ($values['day'] as $i => $day) {
    $this->results->addWhere('EXISTS (
        SELECT NULL
        FROM forecast
        WHERE location_id = location.id
        AND condition_id IN (' . implode(',', $values['condition']) . ')
        AND temperature_high <= ' . $values['temperature_max'] . '
        AND temperature_low >= ' . $values['temperature_min'] . '
    )');
}
$this->results->execute();

I think it would be easier to use $query->addWhere('EXISTS (...)') with an inner select statement for each condition.

$this->results = Doctrine_Query::create()->select('name')->from('location');

foreach ($values['day'] as $i => $day) {
    $this->results->addWhere('EXISTS (
        SELECT NULL
        FROM forecast
        WHERE location_id = location.id
        AND condition_id IN (' . implode(',', $values['condition']) . ')
        AND temperature_high <= ' . $values['temperature_max'] . '
        AND temperature_low >= ' . $values['temperature_min'] . '
    )');
}
$this->results->execute();

回复收藏 0 原文

~没有更多了~