[Denis Roussel]

I know that DB2 support for Yii has already been discussed here.

But I nearly made it working on our environment.

I try to implement some needed schema functionnalities for ActiveRecord needs.

The one I'm on is the LIMIT sql function.

Unfortunately, DB2 has no support or integrated function to manage limit offsets. So, I have to modify the Find SQL query in order to make GridView working, for instance.

I need to add a column to the query and modify the query condition too...

E.G. :

SELECT * from (SELECT p.*,row_number() over() as rn from products as p) as col where rn between 1  and 20

I don't know the whole Yii Framework and I want to reuse some existing functions avoiding to write so much code.

The first solution in my mind was to rewrite (by class extension) the applyLimit() CDbCommandBuilder class function. But it could be hard to modify the whole sql query text.

The second could be writing modifications in createFindCommand() function directly.

If someone can give me the way to the light...


Many thanks!

[Denis Roussel]

 Denis Roussel, on 26 October 2012 - 07:25 AM, said:

I know that DB2 support for Yii has already been discussed here.

But I nearly made it working on our environment.

I try to implement some needed schema functionnalities for ActiveRecord needs.

The one I'm on is the LIMIT sql function.

Unfortunately, DB2 has no support or integrated function to manage limit offsets. So, I have to modify the Find SQL query in order to make GridView working, for instance.

I need to add a column to the query and modify the query condition too...

E.G. :

SELECT * from (SELECT p.*,row_number() over() as rn from products as p) as col where rn between 1  and 20

I don't know the whole Yii Framework and I want to reuse some existing functions avoiding to write so much code.

The first solution in my mind was to rewrite (by class extension) the applyLimit() CDbCommandBuilder class function. But it could be hard to modify the whole sql query text.

The second could be writing modifications in createFindCommand() function directly.

If someone can give me the way to the light...


Many thanks!

I found the solution in exploring the Zend Framework as they have the limit functionnality implemented yet.

See ZendCore.


But there is one more limitation as the solution generate a new field (and a new query) which contains the row number and add a WHERE condition on that field.

The SELECT part must include the ORDER BY part to correctly apply the number to the row : mandatory when there is an ORDER BY DESC...

The query building becomes quite difficult when you involve relations as that's not the CommandBuilder (createFindCommand) which calls the applyLimit.

Is there a solution to build the query easily without extending so much classes?

There is an example of an entire query :

$order is the $criteria->order taken from the createFindCommand (for simple queries)

SELECT z2.*
              FROM (
                  SELECT ROW_NUMBER() OVER(".$order.") AS \"DB_ROWNUM\", z1.*
                  FROM (
                      " . $sql . "
                  ) z1
              ) z2
              WHERE z2.db_rownum BETWEEN " . (int)($offset+1) . " AND " . (int)($limit+(int)$offset);