How to fetch results into a custom object now that fetchAll() and FetchMode are deprecated?

15.4k views Asked by At

On some existing code I have the following statement (after a rather long query building exercise):

return $statement->fetchAll(
    DBAL\FetchMode::CUSTOM_OBJECT,
    PublishedLead::class
);

This works (so far), but I see now that both fetchAll() and FetchMode are deprecated since DBAL 2.11:

// ResultStatement::fetchAll()
/* 
 * @deprecated Use fetchAllNumeric(), fetchAllAssociative()
 * or fetchFirstColumn() instead.
 */
// FetchMode
/* 
 * @deprecated Use one of the fetch- or iterate-related 
 * methods on the Statement
 */

In the interest of keeping my code as forward-compatible as possible, how can write this to fetch the results hydrating into a custom objects? Do I have to write a custom hydrating logic from the results, or can DBAL do this for me?

1

There are 1 answers

2
scenia On BEST ANSWER

DBAL 3

The API had some significant changes with DBAL 3. Notably for this answer, statements are no longer re-used for storing and accessing results, so executing the statement and then simply looping over it is no longer possible. Unlike an executed Statement, the Result object returned from $statement->execute() is not Traversable, so the code suggested below must be changed to explicitly call $result->fetchAllAssociative before looping (or right in the foreach statement), but is otherwise still compatible (the variable just has a different type at that point):

function getDatabaseResult(): Generator { // change return type hint, if applicable
    // rest of your function/method
    $result = $statement->execute(); // or ->execute($values);
    foreach ($result->fetchAllAssociative() as $row) {
        yield PublishedLead::fromArray($row);
    }
}

DBAL 2

As far as I can tell from reading through the DBAL source code, the use of fetch modes in general is deprecated, and the provided helper methods should be used instead, which limits results to either numeric or associative arrays.

This means the process of marshalling results into your own class is probably supposed to be handled outside of DBAL now. This might be a tactical decision to promote the use of Doctrine ORM or maybe they just want to focus on what's in the name (abstracting database access) and leave out things that don't really relate to that task. Either way, writing custom hydrating logic isn't actually that complex, you can basically just write a Trait that offers a static method fromArray($data) which iterates through the array and sets all object properties, then returns the object (See the answer to the corresponding question). Use this trait in all classes that you want to build from an associative array.

I assume you're looping through your array of objects at some point, so you can actually turn your function into a generator. If you end up using foreach to iterate through your result set, this doesn't even require any changes in the code using the result. This would mean replacing your return statement with the following loop:

foreach ($statement as $row) {
    yield PublishedLead::fromArray($row);
}

In case you're unfamiliar with generators, this turns your function into one that returns a \Generator, which can be used like an array inside a foreach, but doesn't actually take up the entire memory space to hold all the data. Instead, whenever the next value is needed, execution of your original function resumes until the next yield statement is reached, at which point the yielded value is returned and immediately used.

Also, in case you're wondering, the statement does in fact implement Traversable, so you can simply foreach through it right after getting it from execute without actually calling any of the fetch methods, which is what I'm doing in the example above; $row will be an associative array or more precisely, an array gained from the \PDO::FETCH_BOTH default fetch mode.

Here's a complete prototype:

<?php
// newly created
trait FromArrayTrait {
    public static function fromArray(array $data = []): self {
        foreach (get_object_vars($obj = new self) as $property => $default) {
            $obj->$property = $data[$property] ?? $default;
        }
        return $obj;
    }
}

class PublishedLead {
    use FromArrayTrait; // add this line
    // rest of your class
}

function getDatabaseResult(): Generator { // change return type hint, if applicable
    // rest of your function/method
    // the following 3 lines replace 'return $statement->fetchAll(...);'
    foreach ($statement as $row) {
        yield PublishedLead::fromArray($row);
    }
}

// your actual main code, this is unchanged assuming you already use foreach
foreach (getDatabaseResult() as $lead) {
    $lead->doSomething();
}

Obviously consider namespaces and put those parts wherever they should be in your code. By the way, I've slightly changed the fromArray method so it uses defaults in case an array value is null. If you actually want to be able to replace defaults with null, revert to the original version linked above. In case you want to set dynamic properties even if they're not explicitly declared in your class, loop through $data instead of get_object_vars():

    public static function fromArrayDynamic(iterable $data = []): self {
        $obj = new self;
        foreach ($data as $property => $value) {
            $obj->$property = $value;
        }
        return $obj;
    }

Of course, this one will overwrite defaults with null if nulls are included in the array. As a bonus, this one is compatible with iterable inputs, so it won't only take arrays, it can also work with Generators and Traversables.