SQL LIMIT clause

Yii 1.1 General Discussion
21

I don’t think you should touch the command builder either, as jacmoe said MSSQL its working and its not breaking, it should be something else, whats the line in commandbuilder that is giving you trouble?

22 

 public function applyLimit($sql,$limit,$offset)

        {

                if($limit>=0)

                      $sql.=' LIMIT '.(int)$limit;

                if($offset>0)

                        $sql.=' OFFSET '.(int)$offset;

                return $sql;

        }
23

There is a CMssqlCommandBuilder:




        public function applyLimit($sql, $limit, $offset)

	{

		$limit = $limit!==null ? intval($limit) : -1;

		$offset = $offset!==null ? intval($offset) : -1;

		if ($limit > 0 && $offset <= 0) //just limit

			$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);

		else if($limit > 0 && $offset > 0)

			$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);

		return $sql;

	}

I guess you have to write a command builder that suites your needs too.

24

Notice this:


CMssqlCommandBuilder extends CDbCommandBuilder

It extends CDbCommandBuilder.

Now try this:


    	'db' => array(

'class' => 'path.to.your.class',

// rest of db config

),

I think.

Haven’t tried.

25

You are right I should have said "extend CDbCommandBuilder and override methods to suit your needs" instead of "…to write a command builder that suites your needs…"

26

@Freak:

What’s your connectionstring?

The first element of that string is the driver.

You can also experiment with the driverMap property.

There also must be a place where the PDO driver and the schema is matched…

27

Ok… I see what needs to be done. CMssqlCommandBuilder.php demonstrates what needs to be done since mssql will not work with "LIMIT". Much more work to do. Thank you all you were a great help through this!

28

What driver are you using?

PDO ODBC?

If so, then it would be an odbc schema, wouldn’t it?

Reference:

http://www.yiiframework.com/forum/index.php?/topic/15065-no-odbc-database-schema-in-yii/page__p__75003#entry75003

29

Yes PDO_ODBC. I wrote an odbc schema but the missing part that I did not do was a CommandBuilder.

30

Samdark (and the rest of us) will give you lots of kudos, if you choose to share the schema - it would most probably go into the next version of Yii. :)

31

Well right now it’s a piece of junk. Much work to do or I would shame myself greatly! :lol:

32

Another thing that is really slowing me down is that yii does not support compound indexes. My database has hundreds of tables and each one has only compound indexes.

33

Just note that the MSSQL hack for limit and offset had have problems, in particular, paging becomes slower and slower for the later pages and the last page contains underflow from the previous page. See http://code.google.com/p/yii/issues/detail?id=1501&can=1&q=limit&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Stars%20Summary

34

Hi there,

Is there an update for this post => does a DB2 Schema exist for yii now?

Many Thanks

35

I’m also looking forward this DB2 schema…

36

I’m using all sorts of compound indexes with MySQL. Is this perhaps a driver related limit?