How to create a query with multiple where-clauses in Zend 2 Framework?

Question

I am trying to put this where-clause in my query with Zend 2 (Zend\Db\Sql):

WHERE `comunity_id` = '$communityId' 
  AND (   
   (`date_to` IS NULL AND `date_from` = CURDATE()) 
    OR 
   (`date_to` IS NOT NULL AND `date_to` >= CURDATE()) 

  )

using this method:

public function findByCommunityId($communityId, $limit = 10)
{
    $select = $this->getSelect();

    $where = new Where();

    $select->where(
        array(
        $where->equalTo('community_id',$communityId), PredicateSet::COMBINED_BY_AND,
        new PredicateSet(
            array(
                new IsNull('date_to'),
                $where->equalTo('date_from', new Expression('CURDATE()'))
            ), PredicateSet::COMBINED_BY_AND
        ),
        PredicateSet::COMBINED_BY_OR,               
        new PredicateSet(
            array(
                    new IsNotNull('date_to'),
                    $where->greaterThanOrEqualTo('date_to', new Expression('CURDATE()'))
            ),
            PredicateSet::COMBINED_BY_AND
        )           
        )

    );


    $select->order(array (
            'date_from' => 'ASC' 
    ));
    $select->limit($limit);

    \Zend\Debug\Debug::dump($select->getSqlString($this->getDbAdapter()->getPlatform()));die;

    return $this->select($select);
}

Which results in:

SELECT `com_community_events`.* 
FROM `com_community_events` 
WHERE (`community_id` = '12' AND `date_from` = CURDATE() AND `date_to` >= CURDATE()) 
AND AND AND (`date_to` IS NULL AND (`community_id` = '12' AND `date_from` = CURDATE()     AND `date_to` >= CURDATE())) AND OR AND (`date_to` IS NOT NULL AND (`community_id` = '12' AND `date_from` = CURDATE() AND `date_to` >= CURDATE())) 
ORDER BY `date_from` ASC 
LIMIT '10'

My question is why Zend puts multiple AND/OR keyword next to each other and why the first where clause about the community_id is included in the other part of the where clause?

Kunal Dethe · Accepted Answer · 2014-07-12 10:11:05Z

1

Try this -

Note - Off course I haven't tried this particular code but this is the way I do it usually. Just in case it doesn't work as it is, at least it will give some on the usage of nest() and unnest().

$where = new Where();
$where->nest()
    ->equalTo('community_id', $communityId)
    ->and
        ->nest()
            ->isNull('date_to')
            ->and
            ->equalTo('date_from', new Expression('CURDATE()'))
        ->unnest()
    ->or
        ->nest()
            ->isNull('date_to')
            ->and
            ->greaterThanOrEqualTo('date_from', new Expression('CURDATE()'))
        ->unnest()
    ->unnest();

Good luck.

answered Jul 12, 2014 at 10:11

Kunal Dethe

1,2741 gold badge20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a query with multiple where-clauses in Zend 2 Framework?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related