`PDO::FETCH_CONSTRUCTOR` fetch mode

[xpple]

Description

Dear PHP team,

I hereby want to propose a new fetch mode for PDO.

Motivation

To explain its necessity, first suppose you have a class User:

readonly class User {
    public function __construct(public string $userId, public string $userName) {
    }
}

Next, suppose you have a table in a database storing these users:

CREATE TABLE users (
    user_id int NOT NULL AUTO_INCREMENT,
    user_name varchar(256) NOT NULL,
    PRIMARY KEY (user_id)
);

All good. Now, suppose you want to fetch these users from the database using PDO and automatically convert them to objects of type User. You might try the following:

$connection = /* ... */
$statement = $connection->prepare(<<<SQL
    SELECT user_id, user_name
    FROM users
    SQL);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_CLASS, User::class);

$var_dump($results);

However, you'll get the following error:

Uncaught Error: Cannot create dynamic property User::$user_id

Shame, but expected. After all:

PDO::FETCH_CLASS (int)
Specifies that the fetch method shall return a new instance of the requested class, mapping the columns to named properties in the class.
Note: The magic __set() method is called if the property doesn't exist in the requested class

https://www.php.net/manual/en/pdo.constants.php#pdo.constants.fetch-class

That is, the property names of the class don't match with the fields in the table (userId vs. user_id and userName vs. user_name). In general, the names of the fields of the table need not match the names of the properties of the class. This actually also goes wrong for a second reason. Even if the names had matched, the constructor would be called with zero arguments, resulting in the following error (check this by supplying PDO::FETCH_PROPS_LATE as well):

Uncaught ArgumentCountError: Too few arguments to function User::__construct(), 0 passed and exactly 2 expected

Is it, then, really impossible to create instances of User automatically? Well, maybe we can use PDO::FETCH_FUNC together with the constructor to fix this:

$statement->fetchAll(PDO::FETCH_FUNC, User::class.'::__construct');

However, the __construct method can not be used statically in contrary to Java for example (User::new), so you get the following error:

Uncaught TypeError: non-static method User::__construct() cannot be called statically

Note that you also cannot do $statement->fetchAll(PDO::FETCH_FUNC, new User(...)); for reasons described here. So yeah, it seems it is not possible to do this neatly using the current fetch modes.

Workarounds

With that said, let's look at some of the workarounds. Before I was looking into all these fetch options, I simply used the following:

$results = $statement->fetchAll(PDO::FETCH_NUM);
$results = array_map(static fn($result) => new User(...$result), $results);

This is not ideal, as the results need to be traversed again only to be typed correctly. Another solution would be to simply create a wrapper for the constructor in the User class:

readonly class User {
    public function __construct(public string $userId, public string $userName) {
    }

    public static function wrapper(string $userId, string $userName): User {
        return new User($userId, $userName);
    }
}

And then use PDO::FETCH_FUNC:

$statement->fetchAll(PDO::FETCH_FUNC, User::class.'::wrapper');

This is also not ideal, as you have to keep on changing the wrapper function as the class changes. You could also inline the function if you do not want to make a wrapper:

$statement->fetchAll(PDO::FETCH_FUNC, static fn (...$props) => new User(...$props));

This is slightly less efficient than the wrapper function, but works relatively well. This way, you do not have to maintain a wrapper function. However it is still quite a hack in my opinion. At least such a hack, that I doubt many people will even find this solution.

Proposal

For this reason, I propose a new fetch mode: PDO::FETCH_CONSTRUCTOR. It would basically do what my last workaround did: unpack the results from each row into the constructor. Now, I understand some concerns with this approach. For one, the arguments might not be in the correct order. Although this could easily be fixed by changing the SQL query, you could also pass an (associative) array that describes the order among other things. As usual, you could use setFetchMode to provide fixed constructor arguments.

In this example case, the resulting code would be the following:

$statement->fetchAll(PDO::FETCH_CONSTRUCTOR, User::class);

I said that my last workaround would probably not be found by many people, so I guess another option would be to give this workaround as an example on the PHP website.

Thank you for reading, please let me know what you think! :)

[xpple]

Perhaps related: #12480. I couldn't seem to find the proposal over on php.internals though.

[damianwadley]

There is a much simpler workaround than closures and utility methods: as mentioned in the other report,

readonly class User {
    public string $userId;
    public string $userName;
}

It tends to be more reasonable for table entities, given that database tables often contain a good number of columns and you probably wouldn't want to list them all out as parameters of a constructor...

That aside, other than bike-shedding on the name of the constant, this doesn't sound obviously difficult or unmanageable or impractical or whatever else. So the next step would be taking this to the internals mailing list.
https://www.php.net/mailing-lists.php

[xpple]

This doesn't actually solve the problem here. Note that you will still get the error

Uncaught Error: Cannot create dynamic property User::$user_id

because of the mismatching names. I also must add that I personally dislike that way of designing a class. I understand that this is just my opinion, though.

As for the mailing list, do I just create a new mail thread in the php.internals mailing list? I'm quite new to PHP in general, so I have no clue how this works.

[damianwadley]

Note that you will still get the error

Well yes, the property names do have to match the column names. I just didn't check what they were when I wrote that.

As for the mailing list, do I just create a new mail thread in the php.internals mailing list? I'm quite new to PHP in general, so I have no clue how this works.

Below the table of the different lists there's a form to try to begin the subscribe process, but it's occasionally buggy so if that doesn't seem to work then read the section below it on how to do the step yourself. Then you'll receive an email to confirm you want to subscribe to the list (follow the instructions it gives), and after that you'll be able to send emails to the list - works like just about any other email.

There are some common sense rules you should check, but the main thing to avoid doing is "top-posting" your replies:

Replying to the message up here above it is top-posting and strongly disliked.

On some date, someone wrote:
> ...

versus

On some date, someone wrote:
> ...

Replying down here below it is bottom-posting and good.

> ...more...

Splitting the message into pieces to reply to piecewise is nice too.

[xpple]

Thanks for the instructions! I think I have it all set up now. I sent a mail to the internals mailing list.

[sanderdlm]

There is a much simpler workaround than closures and utility methods: as mentioned in the other report,

readonly class User {
    public string $userId;
    public string $userName;
}

It tends to be more reasonable for table entities, given that database tables often contain a good number of columns and you probably wouldn't want to list them all out as parameters of a constructor...

That aside, other than bike-shedding on the name of the constant, this doesn't sound obviously difficult or unmanageable or impractical or whatever else. So the next step would be taking this to the internals mailing list. https://www.php.net/mailing-lists.php

This offers no support for updating entities? From what I remember, FETCH_CLASS works with private properties, which is useful when you want to update the values later. Or would you rather clone the object to preserve immutability? I recall cloning causing a bunch of issues in Doctrine, because the EntityManager thinks the cloned object is actually a new entity.

Anyway, big fan of this proposal. I like to avoid heavy ORMs and anything that allows PDO to do more heavy lifting is nice :)

[github-actions]

There has not been any recent activity in this feature request. It will automatically be closed in 14 days if no further action is taken. Please see https://github.com/probot/stale#is-closing-stale-issues-really-a-good-idea to understand why we auto-close stale feature requests.

[xpple]

I'd like to re-open this issue.

[CoeusCC]

I came across this very issue after using readonly heavily and finding a use case for fetching a PDO with a named class instead of fetching as an array and spreading to the constructor. I don't know if there's a valid feature request in the internals mailing list, but I do want to voice my support for more discussion on the feature.

final readonly class Header extends CloneableRecord
{
    public function __construct( // NOSONAR
        public ?string           $description = null, // varchar
        // ...
    ) {}
}

$this->adapter->query($sql)->fetchObject(Header::class)
// Cannot modify readonly property Testing\Data\Table\Header::$description