Question 40228 in PHP Question posted in
Official manual can be found here.

Can Doctrine hydrate my entity with a custom scalar values (e.g. aggregate, CASE result, etc) out of the box? – PHP

If I have a Querybuilder instance and apply something like this:

$queryBuilder->addSelect("
   CASE
      WHEN c.modifyStamp > c.createStamp THEN 'draft'
      ELSE 'published'
   END AS state
");

then, on using getResult, I will get something like this:

[
   0 => [
      0 => AppEntityTest instance,
      'state' => 'draft'
   ],
   1 => [
      0 => AppEntityTest instance,
      'state' => 'published'
   ]
]

Which is not really ideal (but I know is expected, by default). To my Entity, I tried adding this:

private $state;

public function getState(): string
{
   return $this->state;
}

public function setState(string $state)
{
   $this->state = $state;
}

…in the hope that the hydrator could see a property (or setter) of the right name, populate it just like any other property, and then just give me a regular array of objects back (pure), rather than the array of arrays (mixed). If you used Symfony serializer for example, you could comfortably take an array of data and apply it to an object with properties the same name as your array keys.

getScalarResult will bunch all the fields in each record together, but I don’t want to work with arrays here. I’d like an array of objects, just like a regular getResult would give me if i DIDN’T use the statement above.

I guess I could loop through the results (when in the ‘mixed’ form) and invoke the setters directly, or use getScalarResult and somehow hydrate manually, but these options just seem clunky. So:

  1. Is there a built in option/config/hydrator/trick I’m missing? OR..
  2. Can/Should I create a custom hydrator for this case? OR
  3. Should I just suck it up, loop the data, apply the dangling extra fields, and return the data in the expected format?

Thanks 🙂

EDIT: Using PHP post-search isn’t viable, because the draft field (example above) also needs to be part of a set of search filters (via a UI that also has pagination – so all needs doing in the DB and not as after-the-fact PHP code.)

Tags:

2

Answers


  1. 0

    1. MySQL (>= 5.7) Generated columns

    namespace Entity;

use DoctrineORMMapping as ORM;

#[ORMEntity]
class Test {
    #[ORMId]
    #[ORMGeneratedValue]
    #[ORMColumn(type: 'integer')]
    private int|null $id = null;

    #[ORMColumn(type: 'integer')]
    private int|null $modifyStamp = null;

    #[ORMColumn(type: 'integer')]
    private int|null $createStamp = null;

    #[ORMColumn(
        type: "string",
        insertable: false,
        updatable: false,
        generated: "ALWAYS",
        columnDefinition: "VARCHAR(9) AS (CASE WHEN modifyStamp > createStamp THEN 'draft' ELSE 'published' END)"
    )]
    private string|null $state = null;
}

    $queryBuilder = $entityManager->createQueryBuilder();
$queryBuilder->select('c')->from('EntityTest', 'c');
echo '<pre>';
var_export($queryBuilder->getQuery()->getResult());
echo '</pre>';

    See: https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/reference/attributes-reference.html#column and: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

    2. Custom Hydrator

    (Ugly but doesn’t need MySQL support)

    The only thing I can find to make it work in PHP is an "ugly" hack on the hydration code. And this hack is made uglier by some of the internal conventions of Doctrine’s ObjectHydrator.

    use DoctrineORMInternalHydrationObjectHydrator;

class CustomHydrator extends ObjectHydrator {

    private function remapScalars($mixedResultRow) {
        $object = $mixedResultRow[0];
        $remainingScalars = [];
        foreach($mixedResultRow as $name => $value) {
            if ($name == 0) {
                continue;
            }
            $setter = 'set' . ucfirst($name);
            if (method_exists($object, $setter)) {
                $object->$setter($value);
            } else {
                $remainingScalars[$name] = $value;
            }
        }
        return count($remainingScalars) == 0 ?
            $object :
            array_merge([$object], $remainingScalars);
    }

    protected function hydrateRowData(array $row, array &$result) {
        parent::hydrateRowData($row, $result);
        $latestKey = array_key_last($result);
        $latestItem = $result[$latestKey];
        if (is_array($latestItem)) {
            $result[$latestKey] = $this->remapScalars($latestItem);
        }
    }
}

    The entity is pretty much how you had it:

    namespace Entity;

use DoctrineORMMapping as ORM;

#[ORMEntity]
class Test {
    #[ORMId]
    #[ORMGeneratedValue]
    #[ORMColumn(type: 'integer')]
    private int|null $id = null;

    #[ORMColumn(type: 'integer')]
    private int|null $modifyStamp = null;

    #[ORMColumn(type: 'integer')]
    private int|null $createStamp = null;

    private string|null $state = null;

    public function setState(string $newState) {
        $this->state = $newState;
    }
}

    And I use the following in the controller:

    $entityManager->getConfiguration()->addCustomHydrationMode(
    'CustomHydrator',
    'CustomHydrator'
);

$queryBuilder = $entityManager->createQueryBuilder();

$queryBuilder->select('c')->from('EntityTest', 'c')->addSelect(
    "CASE WHEN c.modifyStamp > c.createStamp
        THEN 'draft' ELSE 'published' END AS state"
);

echo '<pre>';
var_export($queryBuilder->getQuery()->getResult('CustomHydrator'));
echo '</pre>';

    3. Database Views

    If all else fails, you could use a MySQL view and make your entity map to that. That way the view does the CASE WHEN and from your entity’s point of view it’s just another column.

    Login or Signup to reply.
  2. 0

    You could use the Doctrine postLoad event to add a "real" property to your entity and use it as is

    /**
 * Test
 *
 * @ORMEntity
 * @ORMHasLifecycleCallbacks
 */

class Test
{

    ...

    private $state;

    public function getState(): string
    {
       return $this->state;
    }

    /**
     * @ORMPostLoad()
     */
    public function initState()
    {
        $this->state = $this->modifyStamp > $this->createStamp ? 'draft' : 'published';
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search