Yii default ORDER BY

I ran into an issue with a minor difference between Oracle and MySQL. Apparently MySQL is better about returning rows in the order they were inserted than Oracle. Now if you want to let me know it’s wrong to assume results are returned in any specific order, I know! Neither gives any guarantee on the order of results without an ORDER BY, but Oracle is semi-random.

It took me a little while to find the right way to add default items to queries. Yii uses CActiveRecords for model queries. I already had an overridden CActiveRecord class from Yii handling “getLastInsertId” with Oracle. So I knew I would be able to use that somehow.

I finally discovered scopes. It allows me to define a scope:

public function scopes()
    return array(
            'order'=>'SORT_ORDER ASC',
            'order'=>'ID ASC',

and then use it as such:


But still I didn’t find anything on default scopes. On a whim I did a grep -i “defaultscope” on the code and discovered it exists in the Yii framework. So I was able to piece together the following:

public function defaultScope()
    return array(
 	'order'=>'ID ASC'

and bam. Add that to my QActiveRecord, and it’s golden.

Abstracting schemas for MySQL and Oracle with Yii Migrations

Yii migrations are an interesting way to keep track of your database changes.  I was originally looking for a tool that would abstract the schema SQL — with something like the Yii ActiveRecord — so I wouldn’t have to write duplicate code for Oracle and MySQL.  I mean, that’s kind of the point of going with the PDO abstraction.

So more than a few forum question/answers led me to Yii migration:


I seriously have that page open 3 times in my tabs and I have read it top to bottom probably 20 times.  I keep going back to it over and over hoping it will have more information.  It’s just a little short on answers to all of my questions.

As I said, all I wanted was a way to abstract the schema, so the original premise of “migrations” was a little different than what I was looking for, but it being the only non-custom option, I went forward with it.  The point of migrations is to make sure the state of your database matches the revision of your code.  So it’s sort of like its own hokey versioning system.  As such, it doesn’t function as simply as I might like it.  It’s not easy to focus on one migration if it’s not the last migration.  Because the point of migrations is that you’re moving forward and editing a past migration would be like editing revision X without it effecting all of the future revisions.  So it makes sense for what it is, it’s just awkward.  For one the file structure HAS to include the timestamp.  And it has to include that timestamp at the front of the filename, so autocompleting these migration filenames is a pain in the ass.

One important thing they don’t talk about in the documentation is that the list of what’s been migrated is added automatically to your database via the “tbl_migrations” — and please remember the “s as it will insert it as lowercase, which could be annoying for Oracle users, though Yii seems to handle it “appropriately”.

The only major hurdle here is dealing with autoincrement. The way this was dealt with was I created an Autoincrement class that I plopped in the migrations directory:

This meant that I could just create the tables like so:

class m120402_194059_Quizes extends CDbMigration
	public function up()

		$this->createTable('QUIZES', array(
			'ID' => 'pk',
			'COLLECTION_ID' => 'integer NOT NULL',
			'TITLE' => 'string NOT NULL',
			'DESCRIPTION' => 'string',
			'VISIBILITY' => "integer NOT NULL",
			'STATE' => 'string',
			'SHOW_FEEDBACK' => 'integer',
			'START_DATE' => 'datetime',
			'END_DATE' => 'datetime',
			'DATE_MODIFIED' => 'datetime',
			'DELETED' => "integer NOT NULL",

		Autoincrement::up('QUIZES', Yii::app()->db->driverName);


	public function down()

		Autoincrement::down('QUIZES', Yii::app()->db->driverName);



Yii fixtures with Foreign Keys

CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 2292 OCIStmtExecute: ORA-02292: integrity constraint (QUIZMO_DEV.FK_ANSWERS_QUESTIONS) violated – child record found
(/web/quizmo/PDO_OCI-1.0/oci_statement.c:142). The SQL statement executed was: DELETE FROM “QUESTIONS”

Totally annoying. Now I’m not one who has a ton of experience with foreign keys as most places seem to have relations with tables but never explicitly link them as such.

So a FK just doesn’t let you add a record when the record it’s linking to doesn’t exist and won’t let you delete a record when there is some other record linking to it.

The problem is I don’t think Yii thought about these when they did fixtures. They built in a way to deal with it, but it’s a little annoying.

Let’s ignore the fact that all of my tables are in caps because I want this to work in Oracle without having to throw single quotes around everything all the time. Let’s take a look at the fixtures of a section of my tables in a project I’m working on right now.


First I need an init.php in the fixture directory. This is called from /yii/framework/test/CDbFixtureManager.php

	public function prepare()
		$initFile=$this->basePath . DIRECTORY_SEPARATOR . $this->initScript;


			foreach($this->getFixtures() as $tableName=>$fixturePath)

So the init file just needs to do this:

foreach($this->getFixtures() as $tableName=>$fixturePath)

The issue is the resets and loads need to be done in the right order. So the way I went about this is I put in an array of the tables, using that and a reversed version of the array.

$reset_order = array(

$load_order = array_reverse($reset_order);

foreach($this->getFixtures() as $tableName=>$fixturePath){
	if(!in_array($tableName, $reset_order)){
		throw new CException("Table '$tableName' is not in the reset_order.");
	if(!in_array($tableName, $load_order)){
		throw new CException("Table '$tableName' is not in the load_order.");

foreach($reset_order as $tableName){
	//echo("resetting $tableName\n");
	// this runs the TABLE.init.php if it exists
	// otherwise it just does a $this->truncateTable($tableName);
foreach($load_order as $tableName){
	//echo("loading $tableName\n");

That’s not all though, because EVERY table needs a TABLE.init.php file that will be run when “resetTable()” is run in the above script.


Without these scripts, each table will just DELETE FROM MYTABLE when resetTable is called. The problem with this is if you DELETE FROM COLLECTIONS without deleting the QUIZZES first, you get an integrity constraint violation. So a table init file needs to truncate all tables that are “children” of that table in the order of smallest to largest. COLLECTIONS.init.php looks like this:


Yii Migrations: Dealing with auto_increment for Oracle and MySQL

Oracle deals with auto_increment by using sequences and triggers:

create sequence test_seq
start with 1
increment by 1

create trigger test_trigger
before insert on test
for each row
select test_seq.nextval into :new.id from dual;

And MySQL is just an auto_increment keyword on the end of a column declaration:

CREATE TABLE animals (
name CHAR(30) NOT NULL,
So I created a simple Autoincrement class:


Which I included in the migration file for a schema.  This class will handle checking the driver and executing the appropriate sql.
Autoincrement::up('USERS', Yii::app()->db->driverName);
Autoincrement::down('USERS', Yii::app()->db->driverName);

What needs to be remembered here is to add a “sequenceName” to the model class, that is of the form “TABLENAME_SEQ”.