zend framework - can result array from sql query contain database names?

Question

If we run a query such as the following:

SELECT `user`.`user_id`
     , `user`.`username`
     , `profile`.`user_id`
     , `profile`.`name`
     , `profile`.`location`
  FROM `user`
  JOIN `profile`
 USING (`user_id`)
 WHERE `user`.`user_id` = 1;

then we get the result set:

object(ArrayObject)
    private 'storage' => 
        array
           'user_id' => string '1'
           'username' => string 'ExampleUsername'
           'name' => string 'Example name'
           'location' => string 'Example location'

Notice that user_id field is only returned once, even though it exists twice in the SQL query.

Is there a way to return table names as part of the result set? For example, the following result set would be desired:

object(ArrayObject)
    private 'storage' => 
        array
           'user' => array
               'user_id' => string '1'
               'username' => string 'ExampleUsername'
           'profile' => array
               'user_id' => string '1'
               'name' => string 'Example name'
               'location' => string 'Example location'

I have seen this done in other frameworks (Laravel, CodeIgniter) but am not seeing the option for Zend Framework version 2 or 3.

This is just an example SQL query. We are running much more complex queries in our project where a returned associative array with table names as keys would be ideal.

Answer 1

I think you mean you want the keys to include table names, not database names.

IIRC there's no built-in way to do this in Zend Framework.

You can make each key distinct, but it's up to you to do this by defining column aliases:

SELECT `user`.`user_id` AS user_user_id
     , `user`.`username`
     , `profile`.`user_id` AS profile_user_id
     , `profile`.`name`
     , `profile`.`location`
  FROM `user`
  JOIN `profile`
 USING (`user_id`)
 WHERE `user`.`user_id` = 1;

This is a common problem with any database library that returns results in an associative array, not just Zend Framework and not even just PHP.

The second example you show, of fetching columns into some kind of nested data structure broken down by tables, is not supported in any database library I've ever used. How would it return the results of the following query?

SELECT user.user_views + profile.profile_views AS total_views
FROM user JOIN profile USING (user_id)

Would total_views belong under the user key or the profile key?

There are many other similar examples of SQL queries that return results that don't strictly "belong" to either of the joined tables.