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

Hello

Lukas Kahwe Smith wrote:

Using the column key to build a hierarchical tree structure by using
the dot as delimiter for the keys.
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