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 => \App\Entity\Test instance,
'state' => 'draft'
],
1 => [
0 => \App\Entity\Test 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:
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.)
namespace Entity;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
class Test {
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column(type: 'integer')]
private int|null $id = null;
#[ORM\Column(type: 'integer')]
private int|null $modifyStamp = null;
#[ORM\Column(type: 'integer')]
private int|null $createStamp = null;
#[ORM\Column(
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('Entity\Test', '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
(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 Doctrine\ORM\Internal\Hydration\ObjectHydrator;
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 Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
class Test {
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column(type: 'integer')]
private int|null $id = null;
#[ORM\Column(type: 'integer')]
private int|null $modifyStamp = null;
#[ORM\Column(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('Entity\Test', '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>';
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.