QueryBuilder ignores LIMIT with Oracle

[cleithoff]

When you use Phalcon\Mvc\Model\Query\Builder with Oracle the method limit does nothing.

Example from inside controller action:

$query = $this->modelsManager->createBuilder()->from('mytable');
$query->limit(100,200);
$result = $query->getQuery()->execute();

The $result has all records from that table. So limit is ignored. When I looked into the sql log I see following query:

SELECT * FROM (SELECT Z1., ROWNUM PHALCON_RN FROM (SELECT mytable. FROM mytable) Z1)

As you can see the WHERE conditions are missing.

I tested these under Phalcon 2.1.0 RC1 and 2.0.11 with windows dlls from your website.

With MySql+Ubuntu it is working! So maybe this issue depends on Phalcon\Db\Dialect\Oracle. The builder creates PHQL with parameter bindings and after that when Phalcon\Db\Dialect\Oracle is used to create the real query no real limit and offset number are found (maybe it is a string :APL0: or empty/0) and this caused that no where clauses are created.

Workaround: Use get phql from builder and create query from phql. Before you will replace LIMIT and OFFSET bindings with your parameters.

$query = $this->modelsManager->createBuilder()->from('mytable');
$query->limit(100,200);

$phql = $query->getPhql();
$phql = str_replace(':APL0:', $limit, $phql);
$phql = str_replace(':APL1:', $offset, $phql);
$query = $this->modelsManager->createQuery($phql);

$result = $query->execute();

[lubberscorrado]

Thanks for the Workaround, i just came across the exact same issue and was pulling my hair off
Hopefully this can be fixed directly in phalcon soon 😀

[sergeyklay]

Phalcon does not support Oracle. Refer to #12008