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