doctrine Archives - {5} Setfive - Talking to the World

On many of our projects we use Gearman to do background processing. One of problems with doing things in the background is that the web debug toolbar isn’t available to help with debugging problems, including queries. Normally when you want to see your queries you can look at the debug toolbar and get a runnable version of the query quickly. However, when its running in the background, you have to look at the application logs to see what the query is. The logs don’t contain a runnable format of the query, for example they may look like this:

	SELECT
	t0.username AS username1, t0.username_canonical AS username_canonical2, t0.email AS email3,
	t0.email_canonical AS email_canonical4, t0.enabled AS enabled5, t0.salt AS salt6, t0.password AS password7,
	t0.last_login AS last_login8, t0.locked AS locked9, t0.expired AS expired10, t0.expires_at AS expires_at11,
	t0.confirmation_token AS confirmation_token12, t0.password_requested_at AS password_requested_at13, t0.roles AS roles14,
	t0.credentials_expired AS credentials_expired15, t0.credentials_expire_at AS credentials_expire_at16, t0.id AS id17,
	t0.first_name AS first_name18, t0.last_name AS last_name19
	FROM app_user t0
	WHERE t0.id = ? LIMIT 1 [1] []

view raw example_query.sql hosted with

by GitHub

Problem is you can’t quickly take that to your database and run it to see the results. Plugging in the parameters is easy enough, but it takes time. I decided to quickly whip up a script that will take what is in the gist above and convert it to a runnable format. I’ve posted this over at http://code.setfive.com/doctrine-query-log-converter/ . This hopefully will save you some time when you are trying to debug your background processes.

It should work with both Doctrine 1.x/symfony 1.x and Doctrine2.x/Symfony2.x. If you find any issues with it let me know.

Good luck debugging!

Recently I was working on a project where part of it was doing data exports. Exports on the surface are quick and easy – query the database, put it into the export format, send it over to the user. However, as a data set grows, exports become more complicated. Now processing it in real time no longer works as it takes too long or too much memory to export. This is why I’ll almost always use a background process (notified via Gearman) to process the data and notify the user when the export is ready for download. On separate background threads you can have different memory limits and not worry about a request timeout. I suggest trying to not use Doctrine’s objects for the export, but get the query back in array format (via getArrayResult). Doctrine objects are great to work with, but expensive in terms of time to populate and memory usage; if you don’t need the object graph results in array format are much quicker and smaller memory wise.

On this specific export I was exporting an entity which had a foreign key to another table that needed to be in the export. I didn’t want to create a join over the entire data set as it was unnecessary. For example, a project which has a created by user as a relation. If I simply did the following:

	// In ProjectRepository
	public function getBaseExportQuery()
	{
	return $this->createQueryBuilder('p')
	->getQuery()
	->getArrayResult();

	}

view raw gistfile1.php hosted with

by GitHub

I’d end up with an array which had all the project columns except any that are defined as a foreign key. This means in my export I couldn’t output the “Created by user id” as it wasn’t included in the array. It turns out that Doctrine already has this exact situation accounted for. To include the FK columns you need to set a hint on the query to include meta columns to true. The updated query code would look similar to:

	// In ProjectRepository
	public function getBaseExportQuery()
	{
	return $this->createQueryBuilder('p')
	->getQuery()
	->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true)
	->getArrayResult();

	}

view raw gistfile1.txt hosted with

by GitHub

Now you can include the foreign key columns without doing an joins on a query that returns an array result set.

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"

view raw gistfile1.txt hosted with

by GitHub

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 );

view raw gistfile1.php hosted with

by GitHub

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));
	}

view raw gistfile1.php hosted with

by GitHub

Recently I was doing a fairly common task on Symfony2, logging in a user programatically. Often applications do this on registration, via auto login links, complex login forms, etc. This time I was using an auto login link that expires that users get via email. I came across the issue that it seemed the first time the page loaded I was logged in properly but then as soon as I redirect or navigated anywhere I was logged out.

Here is the basic workflow we were using:

Create an auto login link, basically just an entity which had an expiration date and a special url hash
When clicked forward to action which gets the related entity, and then retrieves the user.
Login the user programatically
Redirect to whatever we want them to see

The issue was somewhere between step 3 and 4 something was amiss, if I eliminated step 3 the profiler toolbar showed I was properly logged in as expect, as soon as I redirect it showed me as unauthenticated. Here is the code for the most part:

	<?php

	/**
	* @Route("/login-token/{token}/{userHash}", name="participant_autologin_token")
	* @ParamConverter("autologinToken", class="MyBundle:AutologinToken")
	* @Template()
	*/
	public function autoLoginAction(Request $request, AutologinToken $autologinToken, $userHash){

	$csrf_token = $this->container->get('form.csrf_provider')->generateCsrfToken('authenticate');
	$now = new \DateTime();

	if( $autologinToken->getExpiresAt() != NULL && $autologinToken->getExpiresAt() < $now ){
	return array("csrf_token" => $csrf_token, "last_username" => "", "nextUrl" => $autologinToken->getUrl());
	}

	if( $autologinToken->getUser()->getAutologinToken() != $userHash ){
	return array("csrf_token" => $csrf_token, "last_username" => "", "error" => "Sorry! There is a problem with your link. Please contact support.");
	}

	$user = $autologinToken->getUser();

	$providerKey = $this->container->getParameter('fos_user.firewall_name');
	$token = new UsernamePasswordToken($user, null, $providerKey, $user->getRoles());
	$this->get('security.context')->setToken($token);

	$event = new InteractiveLoginEvent($request, $token);
	$this->get("event_dispatcher")->dispatch("security.authentication", $event);

	return $this->redirect( $autologinToken->getUrl() );
	}

view raw gistfile1.php hosted with

by GitHub

Fairly simple, used a ParamConverter to convert the incoming request to an entity. After a while of troubleshooting, I noticed that the ‘$user’ in this case wasn’t an actual Entity, it was a ProxyClass that Doctrine2 had generated. I had read that Doctrine2 ProxyClasses when serialized don’t properly bring over some of their attributes, namely the ID. This caused an issue with FOSUserBundle as the UserProvider looks up the user by their ID. Since the ID was blank this kept causing it to not find my user on the next page load.

There are a number of ways you can fix this, two that come to mind is to override the ‘refreshUser’ method of the UserProvider to look up by username as that is properly serialized from Proxy objects. Instead, as this was only for this one action and I wanted to be more efficient I switched the query to do a join to the user from the get go. This means when you do getUser Doctrine will return the actual Entity and not a Proxy class. Here is my update annotation:

	<?php
	/**
	* @Route("/login-token/{token}/{userHash}", name="participant_autologin_token")
	* @ParamConverter("autologinToken", class="MyBundle:AutologinToken", options={"repository_method" = "findByTokenJoinWithUser"})
	* @Template()
	*/
	public function autoLoginAction(Request $request, AutologinToken $autologinToken, $userHash){
	....
	}

view raw gistfile1.php hosted with

by GitHub

For more on how to use joins and entity repository specific consult the current manual.

Good luck!

Earlier this week we were repeatedly getting notifications about a “usort() Array was modified by the user comparison function” warning for one of our new Symfony2 projects. The weird thing was the sort function was relatively straightward and looked something like:

	<?php

	usort($arr, function($a, $b){
	return strcmp($a->getBrand()->getName(), $b->getBrand()->getName());
	});

view raw gistfile1.php hosted with

by GitHub

Obviously not modifying the array. Anyway, Daum dug up this StackOvervlow thread which suggested that using introspection methods silently modify the array and trigger the warning but I’m clearly not using any of those either.

After some more poking around, we ran across a Doctrine bug specifically mentioning the usort issue. It turns out, because of how Doctrine’s lazy loading functionality works if the usort callback function causes Doctrine to lazy load it’ll silently modify the array and cause that warning. Great, so how do you fix it? It’s actually pretty straightforward, you just need to force the lazy loading before sorting the collection. I ended up with something like:

	<?php

	foreach($arr as $ab){
	$n = $ab->getBrand()->getName();
	}

	usort($arr, function($a, $b){
	return strcmp($a->getBrand()->getName(), $b->getBrand()->getName());
	});

view raw gistfile1.php hosted with

by GitHub

Anyway, fun fact of the day. Questions and comments always welcome.

Tag: doctrine

Making Doctrine and Symfony Logged Queries Runnable

Including Foreign Keys in Doctrine2 Array Results

Doctrine2: Using ResultSetMapping and MySQL temporary tables

Symfony2: usort() Array was modified by the user comparison function