在两个过滤器之间推动Symfony2“OR”

Question

I'm trying get from my database some datas which must start or end in an interval :

The basic SQL Query looks like that :

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

The propel query is this one :

$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();

When I'm executing my script I've this error :

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)

I don't really understand why I'm getting an error and why my query has an "AND" statement instead of an "OR".

Answer 1

I noticed that Propel can't do a simple "OR" (_or()) between 2 filters wich contains date intervals.

And It also has problem with Criteria so I resolved my problem by replacing criteria with string

$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');