CODEWITHSUNDEEP
phpzend-dbzend-framework2
RomanKonz
RomanKonz

Reputation: 1027

zf2 \Zend\Db\Sql\Sql using predicate in where condition

i really don't get the point how to use predicates in zend framework 2.

this is what i got:

$sql->select()
        ->columns(array('GroupedColum'
            ,'minValue' => new Expression('min(ValueColumn)')))
        ->from('ValueTable')
        ->group('GroupedColum')
        ->order('minValue')
        ->order('GroupedColum')
        ->limit(10);

this is working fine

now i want to apply somethin like that:

$predicate = new Zend\Db\Sql\Predicate\Predicate();
$sql->where($predicate->greaterThan('filterColumn','20);

this is what i tried, it throws no error, but it is not working :-(

This is what i expect as SQL:

select GroupedColum
    , min(ValueColumn) as minValue
from ValueTable
where filterColumn > 20
group by GroupedColum
order by minValue
    GroupedColum
limit 10;

Upvotes: 5

Views: 14532

Answers (2)

Will B.
Will B.

Reputation: 18416

A bit late but you can also accomplish this by doing

$sql->select()
->columns(array('GroupedColum'
    ,'minValue' => new Expression('min(ValueColumn)')))
->from('ValueTable')
->group('GroupedColum')
->order('minValue')
->order('GroupedColum')
->limit(10)
->where
    ->greaterThan('filterColumn', '20');

The __get magic method of \Zend\Db\Sql\Select has a case for where which returns the current where() predicate, which allows you to do more complex things such as

$sql->where
    ->greaterThan('filterColumn', '20')
    ->or
    ->greaterThan('filterColumn', '30');

vs

$predicate = new  \Zend\Db\Sql\Where();
$sql->where($predicate->greaterThan('filterColumn', '20'));
$sql->where($predicate->greaterThan('filterColumn', '30'), 'OR');

For a list of all predicates available in ZF 2 (2.1.0) from the Where predicate see:

Upvotes: 6

RomanKonz
RomanKonz

Reputation: 1027

I've solved the problem by viewing the source code of zf2

If you know how to do it, it is really easy, the code above was almost right!

Instead of 

$predicate = new Zend\Db\Sql\Predicate\Predicate();

you have to use

$predicate = new  \Zend\Db\Sql\Where();

it is an empty derived class, that is used in Zend\Db\Sql\Sql

this is the complete working example:

$sql->select()
    ->columns(array('GroupedColum'
        ,'minValue' => new Expression('min(ValueColumn)')))
    ->from('ValueTable')
    ->group('GroupedColum')
    ->order('minValue')
    ->order('GroupedColum')
    ->limit(10);
$predicate = new  \Zend\Db\Sql\Where();
$sql->where($predicate->greaterThan('filterColumn','20'));

Upvotes: 9

Related Questions