Yii2 AR SQL Verbs; Y-U-SO-WEIRD?

Yii2 being an active record style DB abstraction AND needing to support a wide range of database technologies facilitated creating the standard insert / select / update / delete functionality inside the Active Record model layer. While most of implementation makes sense some of it is not intuitive. Herein is a TL;DR of the 4 major commands and the *All() version if applicable.

 

select / selectAll (in Yii2 this is termed ‘find’)

\Class::find([{array of criteria}])->one(); OR \Class::findOne([{array of criteria}]);

\Class::find([{array of criteria}])->all(); OR \Class::findAll([{array of criteria}]);

 

insert / insertAll (termed ‘save’)

$model = new \Class({column values as array});

$model->save();

No saveAll() implementation by default

 

update / updateAll

$model = \Class::find({column values as array});

$model->setAttribute(s)({string or array of SQL SET keyed values})

$model->save();

 

Class::updateAll([{array of criteria}], {string to Key = Value pairs for SQL SET})

 

delete / deleteAll

\Class::delete({string of criteria})

\Class::deleteAll({string of criteria})

 

Bonus: Yii2 also has the ability to create database commands and bypass the Active Record abstraction altogether: Yii::app()->db->createCommand({string  of SQL command}).

 

Now, lets look at MySQL / Maria / MsSQL / Postgra default immplimentation of the same actions:

select/all

Select {string of keyed values} FROM {string of source} WHERE {string of keyed values}

 

insert/All

Insert Into {string of source} VALUES {string of keyed values}

 

update/All

Update {string of source} Set {string of keyed values}

 

delete/All

Delete From {string of source} Where  {string of keyed values}

 

See a pattern there? string source, key/value data sets. And minus Select all start with the data source followed by the command, the the key/value criteria.  And even select makes sense when you treat the field names as a string substitution for `select {needle(s)} from {source|`.

 

It is not that AR is bad, nor that Yii2 had a lack of effort. Trying to support the feature set of multiple database technologies, active record, createQuery, best practices, security, and community requests is daunting, hands down. Maybe I’ll write a package to normalize the base 4 verbs for MySQl/MariaDB…

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *