CODEWITHSUNDEEP
databasezend-framework2
Henk Kroon
Henk Kroon

Reputation: 71

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

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?

Upvotes: 0

Views: 405

Answers (1)

Kunal Dethe
Kunal Dethe

Reputation: 1274

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.

Upvotes: 1

Related Questions