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

Question

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;

you should add your solution as answer.

Xerkus
– Xerkus

2012-09-27 00:32:10 +00:00
Commented Sep 27, 2012 at 0:32 — Xerkus
– Xerkus, Commented Sep 27, 2012 at 0:32

mokk · Accepted Answer · 2015-08-05 14:40:42Z

9

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

edited Aug 5, 2015 at 14:40

mokk

91418 silver badges35 bronze badges

answered Sep 27, 2012 at 6:43

RomanKonz

1,0271 gold badge8 silver badges16 bronze badges

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

Comments

Will B. · Accepted Answer · 2016-05-31 13:50:09Z

6

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:

edited May 31, 2016 at 13:50

answered Mar 14, 2013 at 13:23

Will B.

18.5k5 gold badges72 silver badges73 bronze badges

1 Comment

Will B. Over a year ago

@Pang Update to referenced version, note that ZF2 has changed significantly since the time of this posting.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related