doctrine query builder join materialized view

1
votes

using Symfony 3.2.4, doctrine and postgres 9.6, I've 2 tables with big data, and I need to obtain result join table records (with sql function es: sum, avg, etc) in acceptable times.

Well, I've create a materialized view, and now it's so really speedy!

YEAH!

But... how I can execute DQL query with Doctrine/Symfony??

I tried to generate entity from materialized view, but it is not detected.

D:\eclipse_neon_workspace\myproject>php bin\console doctrine:mapping:import BackendBundle annotation --filter= Database does not have any mapping information.

So, I tried to write simple query, but doctrine expect a bundle Entity...

[2017-04-03 23:13:37] request.CRITICAL: Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Semantical Error] line 0, col 80 near 'Stats': Error: Class 'Stats' is not defined." at D:\eclipse_neon_workspace\myproject\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php line 63 {"exception":"[object] (Doctrine\ORM\Query\QueryException(code: 0): [Semantical Error] line 0, col 80 near 'stats': Error: Class 'Stats' is not defined. at D:\eclipse_neon_workspace\myproject\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php:63, Doctrine\ORM\Query\QueryException(code: 0): SELECT stasgeojson(l.geom) as geom FROM BackendBundle:MyTable l LEFT JOIN Stats stats WITH stats.key=l.key WHERE l.filter in (77004) at D:\eclipse_neon_workspace\myproject\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php:41)"} []

How the hell do I use a materialized view with doctrine????

Thx in Advance

1
phpsymfonydoctrinedqlmaterialized-views
Can you please show the code that caused the above error? - Alvin Bunk
$qb->select("field1")->from("BackendBundle:Table", "t")->join("Stats","stats",Join::WITH,"t.key=stats.key")->where("t.field2 in (3456)"); - mardif

1 Answers

1
votes

Finally, I've found no solutions.... DQL don't support VIEW or MATERIALIZED VIEW, and there's nothing for generate entities from these object.

So, I've create a simple $em->createNativeQuery using native SQL, and it all works, but I think that's a gap of no small importance