Allow decoupling between ActiveRecord attribute names and database column names

[stephen-164]

The house style I develop under mandates db column prefixes (a table users would have columns uid, uname, uemail; the table orders would have columns oid,ouser,odate, etc). It's not nice to have to expose these as the properties of the relevant active records; rather we have $user->id and $order->id as usual. To achieve that in Yii 1 I've previously implemented a small framework hack which allows a map between DB column names and active record attributes: the implementation allows CActiveRecord::attributeNames() to return a keyed array where the keys are column names - this is backward-compatible with the normal implementation. The Yii2 implementation would apply the same behaviour to ActiveRecord::attributes(), I guess.

It would be nice if this could be a properly-supported feature in Yii, and this seems a good time to implement it. Is this a legitimate feature? If so I can prepare a PR for it.

[rawtaz]

How does this in-house policy suggest that you deal with having multiple table names beginning with the same initial character (take for example the table names organizations and orders)? How should the column names be prefixed then?

[stephen-164]

That there not be any such clashes within the same database (so we'd probably have orders.odid and organizations.ogid or maybe .orgid); that's the whole point.

That's not directly relevant to the framework, though: I actually implement the prefixing through an abstract ActiveRecord base class where each model specifies the prefix and that's added to the columns automatically. My request is just that the framework support a flexible way in which this mapping can occur; it's up to each application to decide how (if at all) it will.

Having flexible mapping is also useful for schema migrations - when traits came in, for example, we had a big refactor to collect all the code handling prices into one interface-and-implementing-trait; it was really useful to be able to change payment.pyamount, option.oprice, discount.divalue, etc etc, to all map to $model->price while deferring the schema updates for a later maintenance window.

[qiangxue]

What would you do with column names appearing in query conditions?

[stephen-164]

I'd like there to be a consistent behaviour, that's a flaw in my current implementation (which doesn't handle CActiveRecord::findByAttributes() etc). I would expect ActiveRecord::find() in 'array mode' (where $q is a condition array) to use attribute names as the keys. Conversely I don't think it's a problem for ActiveRecord::findBySql() to require column names - it is taking SQL after all! I would think that providing ActiveRecord::getColumnName($attribute) and ActiveRecord::getAttributeName($column) functions would be sufficient to allow applications to generate correct SQL if required.

[iJackUA]

I would agree with @stephen-164 that this "inconsistency" in camel case params and methods, but underscored AR attributes looks not very good sometimes (but of course we can leave with it). I very seriously was thinking about a mapper in project with MongoDB - as attribute names are stored inside each document there and I was thinking that it would be cool to have a mapper that will allow to store very short attribute names in DB, but will allow me to have long-good-readable attributes in Code.

Yes while having a mapper we will get inconsistency in another place - between object mapping and raw SQL. But it could be good to think about options or possible ways.

[cebe]

Who forces you to use DB naming? I am always naming my column names in camel case when I am planning to use yii ActiveRecord. In general this introduces a conversion step when storing records and when finding records. Also asArray() could not just use the raw response from PDO it would have to go through all results and rename array keys. I don't think the benefits (what are the benefits btw?) are more than technical effort. Yii AR is fast and easy as it is not a complete abstraction but very close to the database.

[qiangxue]

I don't think we will implement this mapping because the only benefit is to make your attributes look nicer, but this comes at the cost of introducing more inconsistency - you would have to remember where to use virtual names and where to use actual names, and as a result, your code is more error prone and harder to maintain. Note that ActiveRecord::findBySql() is just one place you have to use column names, you also need to use column names in query conditions (not hash-based) and many other places.

[stephen-164]

(was most off the way through writing this when qiangxue closed it, so might as well finish!)

We do, it's part of the company's coding conventions. It has its advantages in terms of making it easier to debug SQL and do manual db edits; it's especially useful when, as we do, you have completely separate applications acting on the same databases (admin/reporting applications which are completely separated from customer interface applications but act on the same data(bases), for instance). You would want the flexibility if you wanted to interact with any data from a MediaWiki, Drupal or Wordpress application, for instance.

Again, though, that's somewhat beside the point: whether or not it's a good practice to do when building applications is for the consumer to determine. The question that's relevant to the framework is whether it's possible to implement the mapping in a way that's consistent and flexible. Seems people are generally not keen, though; which is perhaps unsurprising; it's a fairly niche feature...!

[iJackUA]

Who forces you to use DB naming? I am always naming my column names in camel case when I am planning to use yii ActiveRecord.

@cebe You know these old-school rules "lower-case, underscore - and you'll always be ok" :) Yes, it appears that mapping is not a basic feature for AR model (if to consider it as a DB layer). It is better to introduce another Data Models layer and implement mapping there. As for me it is related to app architecture considerations I have described in #1035

[mcd-php]

Prefixed name transparency is good indeed.

I used it in my first framework project, after looking at Wikipedia data schema. early in the project i was trying to de-prefix (Zend Framework 1), but soon realized what de-prefixed fields are hard to find by search through entire project.

@stephen-164 , imagine searching for prsn_name, cntg_name, lctn_name or just for chopped name, to locate a problem :)

My current project is NoSQL (Apache Cassandra) and i started it with short names in mind as they were to be stored with every row, but now Cassandra moves to schema-based tables and i will have to live with just name or id for everything.

P.S. On the other hand, prefixes may hinder code (and view) reuse and automatic query building for range of tables, but this can be mitigated by focused use of re-indexing array helper.

[rawtaz]

@mcd-php I think this is a flawed concept. Do you prefix your method names with an abbreviation of the class name they belong to as well? That would be the same thing as you're doing, since you can have method names that are not "easy" to find using a search in the project (though I guess this depends on your IDE).

[mcd-php]

@rawtaz No i do not ;) Then laying keel of my app under huge uncertainity, i was just monkeying after the biggest successful open-source projects, and media-wiki data schema diagram just got under my hand.

The prefixing and then backing out of de-prefixing in PHP are just report of factual experience, not of pre-thought.

Classes are easy if no magic and/or PHPdoc annotations, if any. Zend_Registry is evil :(

But fields are findable only if one has models transparent for static analysis (or annotated if magic) and object-driven views or again annotatioons in plain-php ones. BTW i now place annotations in views just for auto-completion, and magic properties can be annotated just at need of finding, and then Find usages.

BTW, how do you find unprefixed DB fields in SQL query builders ? Always write user.name ?

[rawtaz]

Okay :D