Yii Framework Forum: Reimplement New Schema (On Db2) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Reimplement New Schema (On Db2) Rate Topic: -----

#1 User is offline   Denis Roussel 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 28-October 11

Posted 26 October 2012 - 07:25 AM

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!
0

#2 User is offline   Denis Roussel 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 28-October 11

Posted 20 November 2012 - 04:31 AM

View PostDenis 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);

0

#3 User is offline   Edgard Messias 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 05-December 12
  • Location:Brazil

Posted 10 September 2013 - 08:33 PM

Try my extension: YiiDB2
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users