Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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;
like image 535
RomanKonz Avatar asked Sep 26 '12 21:09

RomanKonz


2 Answers

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'));
like image 116
RomanKonz Avatar answered Nov 08 '22 11:11

RomanKonz


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:

  • https://github.com/zendframework/zendframework/blob/release-2.1.0/library/Zend/Db/Sql/Predicate/Predicate.php
  • http://framework.zend.com/apidoc/2.1/classes/Zend.Db.Sql.Predicate.Predicate.html
like image 44
Will B. Avatar answered Nov 08 '22 13:11

Will B.