SelectQuery::fields and Drupal's naming conventions - how to make it all match?

Question

Convention for database column names is underscore_notation. Convention for class properties is camelCaseNotation. But when I'm using SelectQuery::fields('t') and then fetchObject(), fetched object's properties are in underscore_notation.

I know I can use code like:

foreach($fields as $field)
    $query->addField($table_name, $field, to_camel_case($field));

That's a solution when I know fields I'm supposed to get. What if I don't? If all I want is good old SELECT * FROM {table}? Is there a simple way to fetch object compatible with Drupal's naming convention?

I know that formally, as written, convention does not apply to objects generated dynamically and returned from PDO. I want to avoid mixed namings none the less. It feels wrong to make user input $object->camelCase and give him $output->underscored_answer back. Bad as in causing errors and confusion.

Answer 1

I've mucked to much with PDO and Drupal -- try this it out and let us know :).

The database layer in Drupal uses PDO. You can fetch results to a custom class typically by setting the FETCH_MODE .... welllll I was gonna link you here Fetching into a custom class

But, it looks the answer is more simple but probably doesn't buy you much. But maybe you'll find this helpful. Read on ...

You just need to tell PDO to respect the case semantics of your SQL statement for your queries (trying to do this on all queries in drupal, see below, will probably blow chunks).

Relevent Additional Documentation is here:

• https://stackoverflow.com/questions/9438860/php-pdofetch-class-is-mapping-to-all-lowercase-properties-instead-of-camelcase
• http://php.net/manual/en/pdo.setattribute.php
• https://drupal.org/node/726192
• (I replied here and use Migrate alot with funky PDO tweaks ...) Drush Fails to Connect to MySQL DB Server Using PDO SSL Options

I think you'd have to write your queries similar to:

// This gets the default drupal database connection, you can pass parameters to
// select alternate dbs defined in settings.php
$db = Database::getConnection();
// This tells PDO to use the case of the raw SQL statement allowing you to write:
// SELECT foo_bar AS fooBar, some_other_property AS someOtherProperty ...
// and they will return in your fetchObject object as camelCased (natual, whatever you AS'd as case) ...
$db->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
// This is the long route of writing db_select('table')->fields() ...
// caveat the db_select->fields() method doesnt allow aliasing, you have to
// add fields to your query via addField()
$query = $db->select('some_table', 'some_table_alias');
// Then add to your select query as needed.
$query->addField('some_table_alias', 'foo_bar', 'fooBar');

Some other notes:

• Without getting reallllly creative I dont think you can do this using db_query(). db_query() is a simple helper method to the default database connection (not any db) and I'm not sure you can Database::getConnection()->query("...");
• You could try to set this globally in drupal in your settings.php by using a patch https://drupal.org/node/726192 to allow abritrary PDO attributes to be set on the database connection defined in settings.php. I dont recommend this.

Answer 2

What the naming conventions actually say is that "Methods and class properties should use lowerCamel naming." (my emphasis).

The properties added to the object returned by a query are not class properties. This is confirmed by the PHP documentation which defines "properties" (or "class member variables") as "defined by using one of the keywords public, protected, or private, followed by a normal variable declaration". There is no such declaration that applies here.

The object you are looking at is of type StdClass. There isn't much about the concept behind StdClass in the PHP documentation (unless I missed it), but the concept of a class-less object with dynamic fields is sometimes known as "anonymous object". You can create one in PHP by doing:

$obj = (object)(array('field' => 'value'));
echo $obj->field; // Outputs 'value'

In the case of the object returned by the query, it doesn't even have methods - it could just be an array (which it would be if you fetched your data using DatabaseStatementInterface::fetchAssoc). So we can think of anonymous objects as containers, rather than instantiations of classes to which the coding style should apply.

Since this object is a container of fields from the database, I think it makes sense for the database convention to apply. It is an object wrapper around a database row.

As a Drupal programmer, if I get a result that is essentially a database row, I will actually expect it to be in underscore notation. While it may feel wrong, it is what people are used to expect. Every time I use [node_load][4], the result I get is an object in underscore notation. Even when you use the entity meta data wrappers, which essentially are there to provide an object interface around Drupal entities, you still use the underscore notation. Changing this approach would potentially confuse other Drupal programmers.

I understand that if you are writing, say, an API which returns your own data, you may want to abstract the fact the result is a database row and present it as an object like any other (as suggested by your param vs. result example). One option, though one that has some overheads, would be to create a class to hold such result, and casting the database result object to it. This would at least make it clear that what you are returning is a proper object, not just a container around a database row. You could do something like this:

Class MyApiResult {
  function __construct($row) {
    $vars = get_object_vars($row);
    foreach ($vars as $name => $value) {
      $cc_name = to_camel_case($field);
      $this->{$cc_name} = $value;
    }
  }
}

Then your API would return:

return new MyApiResult($row);

However this has some overheads, it depends whether those will be acceptable in your particular case.