On one of our projects that I am working on I had the following problem: I needed to create an aggregate temporary table in the database from a few different queries while still using Doctrine2. I needed to aggregate the results in the database rather than memory as the result set could be very large causing the PHP process to run out of memory. The reason I wanted to still use Doctrine to get the base queries was the application passes around a QueryBuilder object to add restrictions to the query which may be defined outside of the current function, every query in the application goes through this process for security purposes.
After looking around a bit, it was clear that Doctrine did not support (and shouldn’t support) what I was trying to do. My next step was to figure out how to get an executable query from Doctrine2 without ever running it. Doctrine2 has a built in SQL logger interface which basically lets you to listen for executed queries and to see what the actual SQL and parameters were for the executed query. The problem I had was I didn’t want to actually execute the query I had built in Doctrine, I just wanted the SQL that would be executed via PDO. After digging through the code a bit further I found the routines that Doctrine used to actually build the query and parameters for PDO to execute, however, the methods were all private and internalized. I came up with the following class to take a Doctrine Query and return a SQL statement, parameters, and parameter types that can be used to execute it via PDO.
In the ExampleUsage.php file above I take a query builder, get the runnable query, and then insert it into my temporary table. In my circumstance I had about 3-4 of these types of statements.
If you look at the QueryUtils::getRunnableQueryAndParametersForQuery function, it does a number of things.
Overall this was the best solution I could find at the time for what I was trying to do. If I was ok with running the query first, capturing the actual SQL via an SQL Logger would have been the proper and best route to go, however I did not want to run the query.
Hope this helps if you find yourself in a similar situation!
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:
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:
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:
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:
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:
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:
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:
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:
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:
For more on how to use joins and entity repository specific consult the current manual.