我正在尝试从我的数据库中获取一些必须以间隔开始或结束的数据:
基本的SQL查询如下所示:
SELECT broadcast.id, broadcast.id_channel, broadcast.start_at, broadcast.real_start_at, broadcast.real_end_at, broadcast.title, broadcast.sended, FROM `broadcast`
WHERE (
(broadcast.real_start_at >= :p1 AND broadcast.real_start_at <= :p2)
OR
(broadcast.real_end_at >= :p3 AND broadcast.real_end_at <= :p4))
AND broadcast.sended=:p5 AND broadcast.id_plurimedia=:p6
LIMIT 50
推进查询是这个:
$broadcastsQuery = BroadcastQuery::create()
->limit(20);
->condition('cond1', 'broadcast.real_start_at >= ?', $this->date_start, 'Criteria::GREATER_EQUAL)
->condition('cond2', 'broadcast.real_start_at <= ?', $this->date_end, 'Criteria::LESS_EQUAL)
->combine(array('cond1', 'cond2'), 'Criteria::LOGICAL_AND, 'cond3')
->condition('cond4', 'broadcast.real_end_at >= ?', $this->date_start, 'Criteria::GREATER_EQUAL)
->condition('cond5', 'broadcast.real_end_at <= ?', $this->date_end, 'Criteria::LESS_EQUAL)
->combine(array('cond4', 'cond5'), 'Criteria::LOGICAL_AND, 'cond6')
->where(array('cond3', 'cond6'), null, 'OR');
->filterBySended(true)
->find();
当我执行脚本时,出现此错误:
Unable to execute SELECT statement [SELECT broadcast.id, broadcast.id_channel, broadcast.id_plurimedia, broadcast.id_aedra, broadcast.start_at, broadcast.real_start_at, broadcast.real_end_at, broadcast.title, broadcast.sended, broadcast.date_creation, broadcast.date_edition FROM `broadcast` WHERE ((broadcast.real_start_at >= :p1 AND broadcast.real_start_at <= :p2) AND (broadcast.real_end_at >= :p3 AND broadcast.real_end_at <= :p4)) AND broadcast.sended=:p5 LIMIT 50] [wrapped: Warning: PDOStatement::bindValue() expects parameter 3 to be long, string given in /var/www/projectKI/vendor/propel/propel1/runtime/lib/connection/DebugPDOStatement.php line 114] (500 Internal Server Error)
我真的不明白为什么我会收到错误以及为什么我的查询有"AND"语句而不是"OR"。
我注意到 Propel 无法在包含日期间隔的 2 个过滤器之间执行简单的"OR"(_or())。
它也有标准问题,所以我通过用字符串替换标准来解决我的问题
$broadcastQuery->condition('c1', 'broadcast.real_start_at > ?', $this->date_start)
->condition('c2', 'broadcast.real_start_at < ?', $this->date_end)
->combine(array('c1', 'c2'), 'and', 'c3')
->condition('c1', 'broadcast.real_end_at > ?', $this->date_start)
->condition('c2', 'broadcast.real_end_at < ?', $this->date_end)
->combine(array('c1', 'c2'), 'and', 'c4')
->combine(array('c3', 'c4'), 'or');