Status update: ActiveRecord

I just finished the new ActiveRecord design and implementation. Below is a summary of the main features of this new design. Your feedback are greatly appreciated.

[size="4"]Class Declaration[/size]

Please refer to the attached ER diagram for the sample data. We can declare the corresponding AR classes as follows. You may notice they differ from 1.x syntax in several places:

  • No more model() method.

  • tableName(), relations(), scopes() are now static methods. Several other methods are also turned into static.

  • The relation declaration syntax is changed. We now only differentiate two kinds of relations: has one and has many. Foreign key constraints are specified using ‘link’ option. And the ‘via’ option is equivalent to the ‘through’ option in 1.x.

  • Scopes must be declared in scopes() using anonymous functions.

  • The token "@." and "?." can be used in queries and scopes to represent the table alias prefix to columns. The former represents the self table, the latter the foreign table.




class Customer extends ActiveRecord

{

	const STATUS_ACTIVE = 1;

	const STATUS_INACTIVE = 2;


	public static function tableName()

	{

		return 'tbl_customer';

	}


	public static function relations()

	{

		return array(

			'orders:Order[]' => array(

				'link' => array('customer_id' => 'id'),

			),

		);

	}


	public static function scopes()

	{

		return array(

			'active' => function($q) {

				return $q->andWhere('@.`status` = ' . self::STATUS_ACTIVE);

			},

		);

	}

}


class Item extends ActiveRecord

{

	public static function tableName()

	{

		return 'tbl_item';

	}

}


class OrderItem extends ActiveRecord

{

	public static function tableName()

	{

		return 'tbl_order_item';

	}


	public static function relations()

	{

		return array(

			'order:Order' => array(

				'link' => array('order_id' => 'id'),

			),

			'item:Item' => array(

				'link' => array('item_id' => 'id'),

			),

		);

	}

}


class Order extends ActiveRecord

{

	public static function tableName()

	{

		return 'tbl_order';

	}


	public static function relations()

	{

		return array(

			'customer:Customer' => array(

				'link' => array('id' => 'customer_id'),

			),

			'orderItems:OrderItem' => array(

				'link' => array('order_id' => 'id'),

			),

			// via another relation

			'items:Item[]' => array(

				'via' => 'orderItems',

				'link' => array(

					'id' => 'item_id',

				),

				'order' => '@.id',

			),

			// via a join table

			'books:Item[]' => array(

				'joinType' => 'INNER JOIN',

				'via' => array(

					'table' => 'tbl_order_item',

					'link' => array(

						'order_id' => 'id',

					),

				),

				'link' => array(

					'id' => 'item_id',

				),

				'on' => '@.category_id = 1',

			),

		);

	}

}



[size="4"]Query Interface[/size]

Only three methods are directly provided in AR: find(), findBySql() and count(). They all return a new instance of ActiveQuery which provides typical query building methods, such as select(), from(), etc.

Below are some examples:




// equivalent to Customer::model()->find() in 1.x

$customer = Customer::find()->one(); 


// equivalent to Customer::model()->findAll() in 1.x

$customers = Customer::find()->all(); 


// same as above except that each customer data is returned as an array

$customers = Customer::find()->asArray()->all();


// equivalent to Customer::model()->findBySql(...) in 1.x

$customer = Customer::findBySql('SELECT * FROM tbl_customer')->one();


// iterator support

foreach (Customer::find() as $customer) 


// array access support

// $customers is an ActiveQuery object

$customers = Customer::find(); $customer = $customers[0]; 


// equivalent to Customer::model()->findByPk(2) in 1.x

$customer = Customer::find(2)->one();


// equivalent to Customer::model()->findAllByAttributes(array('name'=>'customer1')) in 1.x

$customers = Customer::find()->where(array('name'=>'customer1'))->all(); 


// chained query methods

$customers = Customer::find()

    ->where('name like :name', array(':name' => '%customer%'))

    ->order('id')

    ->all();  


// or equivalently:

$customers = Customer::find(array(

	'where' => 'name like :name',

	'params' => array(':name' => '%customer%'),

	'order' => 'id',

))->all(); 


// equivalent to Customer::model()->count() in 1.x

$count = Customer::count()->value();


// eager relational query

$customers = Customer::find()->with('orders')->all(); 


// lazy relation query

$orders = $customer->orders;


// scope usage

$customers = Customer::find()->active()->all(); 



Oh, this is pure gold! Also: The new syntax for relations is much easier to understand than the old one. Good job on that!

However: What’s the reason behind that ->count()->value() construct? Feels a bit bogus. And does find()->asArray() have a way to get the primary key (or any arbitrary field) as array key?

And what’s going to happend with STAT-relations?

I’m extremely fine with everything else. Really looking forward to that :lol:

Edit: How are default scopes being handled?

Looks great!

Would the following code also populate the relations in the returned array?




$customers = Customer::find()->with("orders")->asArray()->all();



Does this mean that the current 1.X method for declaring parameterised named scopes won’t work? And if so, how would I e.g. add a scope to a model via a behavior? Is there still a central DbCriteria object per model that can be accessed?

Also, did you consider/implement a way for adding and saving related records?, e.g.




$customer = new Customer;

$customer->name = "A Customer";

$customer->orders->add(array(

    "order_time" => new DbExpression("NOW()"),

    "total_price" => 10,

    "items" => array(

        "quantity" => 10,

        "subtotal" => 10

    )

);

$customer->save(); // save the customer, the orders and the order items



Nice work Q,

Once I got used to the concept behind it, these syntax are more natural to sql. And I suspect new yii comers should find it easier to adapt (assuming they are comfortable with SQL) .

Thanks for the iterator support


foreach (Customer::find() as $customer) 

I especially like the query interface syntax. It feels easier to read by just looking one can easily know what to expect, when looking


CLASSNAME::findMethod()->sqlLikeNameScheme()->rowsReturn()

So my thoughts are are follows:

Declarations: Requires more effort to declare. Good thing is the effort is in one place. (The class)

Query Interface: Work put into declarations is reaped when calling the methods.

[b]

[/b]

Is this available immediately? I take it as there are no need to declare STAT-relation[size="2"][color="#1c2837"]?[/color][/size]

[size="2"] [/size]

Great work.

What’s the reason behind that ->count()->value() construct?

This is because count() returns ActiveQuery and we want to support things like ->count()->with(…)->where(…).

Does find()->asArray() have a way to get the primary key (or any arbitrary field) as array key?

Not sure if I understand this question. asArray() instructs ActiveQuery to return each AR object as an array (column name => column value).

And what’s going to happend with STAT-relations?

STAT relation is no longer supported.

How are default scopes being handled?

It’s declared via ActiveRecord::defaultScope(). Similar to 1.x (but in static method).

Does this mean that the current 1.X method for declaring parameterised named scopes won’t work?

Anonymous functions used in scopes() can take additional parameters to support parameterized scopes.

And if so, how would I e.g. add a scope to a model via a behavior?

You have to override ActiveRecord::createActiveQuery() and explicit attach behaviors to the newly created ActiveQuery object. It’s a bit troublesome, but not much more than in 1.x, I think.

Is there still a central DbCriteria object per model that can be accessed?

No. find() returns a new ActiveQuery object, which is similar to CDbCriteria. This object is accessible in scopes.

Also, did you consider/implement a way for adding and saving related records?

The requirements are still not very clear to me yet. But I will certainly consider it.

Is this available immediately?

Not yet. But we are one step closer to alpha release. The remaining main work is MVC, which is much easier than DB/DAO/AR stuff.

Ah, okay. ->count() itself looks pretty much complete. But together with method-chaining, this makes total sense. Maybe the phpdoc should contain a hint on this?

That’s not what I meant :rolleyes: Is there any way to let $customers[2] be the customer with the id #2 (presuming $customers=Customer::find()->asArray()-all();)?

:o :(

There’s also an option “index” which allows you to use specific column values as array keys.

Ah, excellent :D

Great!

it look like ActiveRecord class now can almost behave like QueryBuilder.

nice work.

i guess




$customers = Customer::find()->one();



returns an Object

what about




$customers = Customer::find()->all();



will it return

array of Objects or arrays?

Your code will return array of objects. This one:




$customers = Customer::find()->asArray()->all();



will return array of arrays.

Hi,

Great work!

Few questions.

What about db indexes? Does ActiveQuery allow to specify index for primary table? The same for relations.

What about not regular columns? E.g. if I perform query below, does upperCasedName field would be accessible without strong declaration into model?




$customers = Customer::find(array(

        'select' => array('upper(name)' => 'upperCasedName', '*'),

))->all();



What about events? The same flow? Or now we can attach handler statically to all models. E.g.


 

Customer::attachEventHandler('afterSave', function($e) {

   // applied for all customer instances (that already created or just would be created) 

});

$customer->attachEventHandler('afterSave', ...); // for this instance only



looks nice at a glance view

but did you think about possibility to rely on conventions over configurations in such things as table name or relations like it’s done in activerecord ruby gem? ( http://rubydoc.info/gems/activerecord/3.2.2/frames )

Haven’t seen the CDbExpression related stuff. That one is important.

Related model saving. Hate to look for a good extension that handles that and all related stuff.

STAT relations. Well, they shouldn’t been added as relations in the first place. BUT! The idea itself is some what brilliant and I use it though my projects from time to time - makes code cleaner and easier.

I love the changes! :)

Except for one thing: STAT is removed. I think that was an awfully handy feature.

So clean, so beautiful!

Will there be a fixed order of the attributes in the "link" key for the relation?

From what I could understand from the code above, the position of the local and the foreign key names wasn’t constant.

To make the name more portable, what about renaming the AR method "findBySql" to "findByQuery", "findByCommand" or similar?

Also, the method "tableName" in the AR is a reference to table-based storages. Could it be renamed to a more generic name?

mentel - I think these issues could be resolved by having 2 active record classes:

abstract class BaseActiveRecord which contains the methods common to all data stores

ActiveRecord which contains the methods for data stores using PDO, this would (probably) be the only official AR implementation.

I forgot to describe another important new feature: AR now can detect if an attribute is dirty or not, and by default it will only save dirty attributes to DB.

Regarding the removal of STAT, as Psih said, it doesn’t belong to AR. However, considering the fact it does bring some convenience, we may support it in some different form (such as via a helper class).

@ololo: Because we now only differentiate has_one and has_many, it is necessary to specify the ‘link’ option. Since we have Gii, we expect much of these code will be generated automatically.

qiang

I have a concern about supporting only PDO witch i had communicated to SamDark. I’m raising this because I had bumped numerous times into PDO lack of db specific functionality. I don’t say I need async queries or multiple select, but such a trivial thing like “ping” made me write an ugly code that does “SELECT NOW()” so that my application does not crash because of db timeout or when something happens with the connection. And PDO kin’a horribly lags behind in its development and i have seen some core dev concerns and opinions to drop it on the internals list (people just dont wana develop PDO, and db specific drivers get love from the db vendors all the time, they do not handle pdo for obvious reasons).

And its good to build a project when you have the ability to switch to a native driver and get your db specific functionality to work if you need it.