Doctrine2: Using ResultSetMapping and MySQL temporary tables

Note: I haven’t actually tried this in production, it’s probably a terrible idea.

We’ve been using MySQL temporary tables to run some analytics lately and it got me wondering how difficult would it be to hydrate Doctrine2 objects from these tables? We’ve primarily been using MySQL temporary tables to allow us to break apart complicated SQL queries, cache intermediate steps, and generally make debugging analytics a bit easier. Anyway, given that use case this is a bit of a contrived example but it’s still an interesting look inside Doctrine.

For arguments sake, lets say we’re using the FOSUserBundle and we have a table called “be_user” that looks something like:

"id","username","enabled","first_name"
"161965","8857049215","1","Tom"
"161964","7783806403","1","Larry"
"161963","1702340214","1","Mark"
"161962","6140583379","1","Nick"
"161961","5474626482","1","Wes"
"161960","2335083853","1","Mark"
"161959","6807578353","1","Mark"
"161958","5928840701","1","Mark"
"161957","1695583227","1","Mark"
"161956","1276759998","1","Mark"
"161955","1714401966","1","Mark"
"161954","9967684636","1","Mark"
"161953","4778383968","1","Mark"
"161952","9616931158","1","Mark"
"161951","8597344302","1","Mark"
"161950","4661522024","1","Mark"
"161949","8085406700","1","Mark"
"161948","1766644457","1","Mark"
"161947","3052034756","1","Mark"
"161946","2151235620","1","Mark"
"161945","6028556862","1","Mark"
"161944","4428949926","1","Mark"
"161943","8650207427","1","Mark"
"161942","6847827313","1","Mark"
"161941","9572768113","1","Mark"
"161940","3230071069","1","Mark"
"161939","7336484313","1","Mark"
"161938","9209189107","1","Mark"
"161937","8708069230","1","Mark"
"161936","7537175130","1","Mark"

Now, for some reason we’re going to end up creating a separate MySQL table (temporary or otherwise) with a subset of this data but identical columns:

<?php

$sql = "CREATE TEMPORARY TABLE active_users AS (SELECT * FROM be_user WHERE enabled = 1 AND LOCATE('968', username) > 0)";
$doctrine->getEntityManager()->getConnection()->query( $sql );

So now how do we load data from this secondary table into Doctrine2 entities? Turns out it’s relatively straightforward. By using Doctrine’s createNativeQuery along with ResultSetMapping you’ll be able to pull data out of the alternative table and return regular User entitites. One key point, is that by using DisconnectedClassMetadataFactory it’s actually possible to introspect your Doctrine entities at runtime so that you can add the ResultSetMapping fields dynamically.

Anyway, my code inside a Command to test this out ended up looking like:

<?php

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Doctrine\ORM\Tools\Console\MetadataFilter;
use Doctrine\ORM\Tools\EntityGenerator;
use Doctrine\ORM\Tools\DisconnectedClassMetadataFactory;
use Doctrine\ORM\Query\ResultSetMapping;

protected function execute(InputInterface $input, OutputInterface $output) {

  $doctrine = $this->getContainer()->get("doctrine");
  $em = $doctrine->getEntityManager();

  // This lets us introspect Doctrine entities
  $cmf = new DisconnectedClassMetadataFactory();
  $cmf->setEntityManager($em);
  
  // Get the entity data
  $classMetadata = $cmf->getMetadataFor("CT\\BEBundle\\Entity\\User");

  $rsm = new ResultSetMapping();
  $rsm->addEntityResult('CT\\BEBundle\\Entity\\User', 'u');
  
  // Add the field info for each column/field       
  foreach( $classMetadata->fieldMappings as $id => $obj ){
    $rsm->addFieldResult('u', $obj["columnName"], $obj["fieldName"]);
  }	    

  $res = $doctrine->getEntityManager()
                ->getRepository("CTBEBundle:User")
                ->createQueryBuilder("u")
                ->select("COUNT(u.id) AS u_cnt")
                ->getQuery()
                ->getResult();

  $output->writeln("Total users: " . $res[0]["u_cnt"]);

  // Select some data into the other table
  $sql = "CREATE TEMPORARY TABLE active_users AS (SELECT * FROM be_user WHERE enabled = 1 AND LOCATE('968', username) > 0)";
  $doctrine->getEntityManager()->getConnection()->query( $sql );

  $query = $doctrine->getManager()->createNativeQuery('SELECT * FROM active_users', $rsm);
  $users = $query->getResult();

  $output->writeln("Active users: " . count($users));
}