# PDO: table name of field in SQLite #42589 and a new fetch style FETCH_ASSOC_TREE

- Thread: https://externals.io/message/42467
- Original archive: https://news-web.php.net/php.internals/42467
- Started by Oskar Eisemuth on 2009-01-03T20:44:10+00:00
- Messages: 3
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #42467 — Oskar Eisemuth — 2009-01-03T20:44:10+00:00

URL: https://externals.io/message/42467#42467

Hi

Receiving the table name for an column is quite useful.
It is not possible for all PDO database drivers to support this 
functionally, still at least the SQLite version could support it when 
Bug #42589 is resolved. ATTR_FETCH_TABLE_NAMES should be added to SQLite 
too.

Currently we have 2(3) ways to get a table name for a column.

PDOStatement -> getColumnMeta
PDO::ATTR_FETCH_TABLE_NAMES (Not in the SQLite patch)
A patch to support new fetch modes ( FETCH_2D) by Hans-Peter Oeri

It would be nice if something like FETCH_2D would be possible, so let's 
purpose a fourth way in the spirit of Hans-Peters patch. :o)

PDO::FETCH_ASSOC_TREE
Using the column key to build a hierarchical tree structure by using the 
dot as delimiter for the keys.

Together with PDO::ATTR_FETCH_TABLE_NAMES this would result into mostly 
the same functionality as FETCH_2D | FETCH_ASSOC as in the FETCH_2D 
patch. Joining with different tables (with same column names) without 
doing any manually aliasing would end up to be quite easy:

SELECT test1.*, test2.* FROM test1 LEFT JOIN test2 ...

And could then post processed to get the relations right and/or 
converting to objects quite easily.
FETCH_CLASS_TREE would be even nicer but there isn't a good way to do 
that. (Would need new interfaces to connect keys/table names to specific 
classes)

When ATTR_FETCH_TABLE_NAMES isn't support it is still possible to 
manually redirect every field by an alias name in the SQL string and 
benefit from the automatically converting of result rows into a tree 
like structure.

Surely FETCH_ASSOC_TREE could be written at script level, I do think the 
"split at dot and traversing" part is better done in the PDO extension 
and comparing to FETCH_2D it only really touches PDO fetch without any 
need to change the PDO drivers. (But could be optimized later to skip 
parts of ATTR_FETCH_TABLE_NAMES in the driver)

So what do you think?

------
Oskar Eisemuth

---

## #42487 — Lukas Kahwe Smith — 2009-01-05T15:32:31+00:00

URL: https://externals.io/message/42467#42487 · In reply to: #42467

> Hi
>
> Receiving the table name for an column is quite useful.
> It is not possible for all PDO database drivers to support this  
> functionally, still at least the SQLite version could support it  
> when Bug #42589 is resolved. ATTR_FETCH_TABLE_NAMES should be added  
> to SQLite too.

Scott said he would look into committing the patch for SQLite.

> PDO::FETCH_ASSOC_TREE
> Using the column key to build a hierarchical tree structure by using  
> the dot as delimiter for the keys.


Personally I am not such a fan of the bit-wise parameter approach.  
Then again it provides a lot of flexibility. That being said finding a  
good API for this particular feature is non trivial (maybe we can  
borrow from LINQ?). Of course having PHP do this work internally is  
more efficient than doing it in userland and I think its an often seen  
enough use case. So I guess file a feature request, ideally with a  
solid proposal for an API for this.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

---

## #42517 — Oskar Eisemuth — 2009-01-06T21:54:21+00:00

URL: https://externals.io/message/42467#42517 · In reply to: #42487

Hello

Lukas Kahwe Smith wrote:
> > […]
> PDO::FETCH_ASSOC_TREE
>
> Personally I am not such a fan of the bit-wise parameter approach. 
> Then again it provides a lot of flexibility. That being said finding a 
> good API for this particular feature is non trivial (maybe we can 
> borrow from LINQ?). Of course having PHP do this work internally is 
> more efficient than doing it in userland and I think its an often seen 
> enough use case. So I guess file a feature request, ideally with a 
> solid proposal for an API for this.

LINQ/DBLINQ translates to SQL and has an understanding how to access the 
SQL server.
PDO doesn't try to understand much of SQL and passes SQL to the database 
and so doesn't know much of the structure.

I guess a LINQ extension system for PHP could use PDO as backend but my 
intention wasn't to add ORM functionality with tree fetching.

Currently I would describe the tree proposal as a way to route the 
columns to the right "end-point" by using SQL aliasing specially when 
complex queries are done.
As with FETCH_2D the mapping comes from the database driver/sql. Only 
being N dimensional and as well cover the other fetch modes.

The other way would be to do the mapping by column index in user land 
with something like getColumnMeta lite in a stable api.
Without breaking the current api this could be done with something like

getColumnMeta(int $column, [int $meta_type])

PDO::META_NAME
PDO::META_TABLE
...

getColumnMeta($column, PDO::META_NAME) would return the column name as 
string.

In userland this information can be used to create a "path" for each 
column, (split at dots, prefixing by table name) then apply the path for 
every fetch.
With Objects and ArrayAccess this should be quite transparent, only 
mixing Objects and arrays will be tricky as ArrayAccess::offsetGet can't 
be forced to return references (#32983), so an array in an object would 
break,
however plain arrays won't be a problem.

A general and fast way to traverse the object path would be nice, then 
the whole thing would end up being something like this:

---
$stmt->execute();

/* Create a map array with getColumnName and/or getColumnMeta to the 
tree locations */
$map = my_createmap($stmt);

/* Example Map:
$map = array(
 array('prop1'),
 array('prop2', 'a'),
 array('prop2', 'b')
);

*/

while($row = $stmt->fetch(PDO::FETCH_NUM)) {
    /* Create an Object instance or new array */
    $objorarray = new MyObject();

    /* Apply the data */
    applymap($objorarray, $map, $row);
    $result[] = $objorarray
}
------
Or the functionality is provided as PDO internal:

$stmt->execute();
$stmt->setFetchMode( PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 
'MyObject', array())

$map = my_createmap($stmt);

/* new function to register the map */
$stmt->setFetchMap($map);

while($obj = $stmt->fetch(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE | 
PDO::FETCH_USEMAP)) {
    $result[] = $obj
}
----

How should the path be stored, it could be a string with dots "a.b.c" or 
an array like array('a','b','c')?
Should the map functionality exposed to userland, only build-in or maybe 
both?

If applymap would be some kind of generic functionality, the impact on 
PDO would be limited to getColumnMeta and the PDO change could be done 
later.
The script may decide to cache the map for later use too.

Anyway applymap($objorarray, $map, $data) should really have a better 
name....

Best regards,
Oskar Eisemuth