Zend Framework 2 error : Statement could not be executed when implement year function in table gateway

Question

I have implemented tablegateway statements in my zf2 project but now I have problem when I used mysql year function couse I snipped it into greaterThanOrEqualTo function. this is my code

code:

$where = new Where();
$classYearMin = $classYear - 1;
$where->greaterThanOrEqualTo('year(date_a)', $classYear)
->and
->equalTo('id_type',1)
->and
->notEqualTo('semester_type','concat("B_","'.$classYearMin.'","/","'.$classYear.'")');
$sql= $this->tableGateway->select(function (Select $select) use ($where) {
    $select
    ->columns(array('semester_type'))
    ->order('date_a')
    ->where($where);
});

and my mysql output syntax :

Syntax:

select semester_type 
from skedul 
where year(date_a) >= 2010 
  and id_type = 1 
  and semester_type != concat('B_',2010-1,'/',2010) 
order by date_a;

and error output

Statement could not be executed (42S22 - 1054 - Unknown column 'year(date_a`)' in 'where clause')

Can anyone help me? Thanks in advance and sorry for my bad English

Answer 1

by default zend will assume that you are giving a column in greaterOrEqualTo. If you want to change that you need to use a Zend db expression.

Change

$where->greaterThanOrEqualTo('year(date_a)', $classYear)

With

$where->greaterThanOrEqualTo(new \Zend\Db\Sql\Expression('year(date_a)'), $classYear)

By the way, you should print the real sql generated with ZF to find what does not work.

Hope this will solve the issue