Category: PHP

SQL Join Checker, Making Sure Your Joins Are Right

By Matt Daum | Leave a Comment on SQL Join Checker, Making Sure Your Joins Are Right

Recently on a project we came across the need to generate a bunch of different reports from the database.  Due to different requirements we weren’t able to use the ORM (Doctrine2 on the specific project), so we wrote the queries by hand.  As we continued to build the different reports we noticed sometimes we’d typo a join, for example join something on `id` versus `user_id`.  These small typos would cause the reports to still run fine, however have the incorrect data, often it was difficult to pinpoint the exact issue in the given report, as only certain conditions could reproduce the results.  After a while Ashish said it’d be great if we had some sort of sanity checker to make sure the queries we were writing were going across the proper joins.  To me, this was:

challenge-accepted

 

At first I thought about just using Regular Expressions to parse out the join parts of the SQL queries.  However, I found http://code.google.com/p/php-sql-parser/ which appears to do the job.  I downloaded it and wrote a class which uses it and some expressions to discover FK’s in the database.  I ended up with something which, albeit not the most elegant, gets the job done.  Here is an example output of it:

Basically it will run through whatever query you give it, and make sure that the columns you are joining on are defined in the DB. If you are trying to join on a column that is defined as a constraint, it will output the part of the join that failed the check as well as what FK’s currently do exist. Another issue this may help with, is if your database is missing a constraint (FK) that should be defined it will point it out.

I wrote this really quickly, so let me know (or make a pull request) if you find any bugs. I’ve put the code up on Github. Let me know if it helps out!

Doctrine2: Using ResultSetMapping and MySQL temporary tables

By Ashish Datta | 2 Comments on 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:

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:

Drupal 7 Views: Directly Edit Content Rendered In Views

By Jared Clapp | 1 Comment on Drupal 7 Views: Directly Edit Content Rendered In Views

1

Thought I’d share a trick that I learned from Metal Toad while working on a Drupal 7 development project. This trick may make you very popular with your clients if they hate the being forced to dig through the content table in the standard admin overlay until they find the specific piece of content in a view they want to edit. Instead, using this method will create a gear button when you hover over the content on the front end, with a link that says “Edit” when you click on it.

I should note that if you style each row in the view using ‘Content’ under the Format > Show menu then views will add the link for you automatically. If you have a very simple view and this is all you need, no need to read further.

Unfortunately for me, many of the views I tend to create are formatted using ‘Fields’ because it provides me more flexibility to customize the output. The drawback is that it doesn’t automatically add these useful contextual edit links for content. But don’t worry, a pretty simple solutions follows..

1.)  Open up your view and navigate to the ‘Fields’ section.  Click ‘Add’ and search for ‘Content: Edit link’, check the box next to it and apply it to the display.

3

2.)  Navigate back to the ‘Add’ button next to ‘Fields’ section of your view and click on the small arrow to right of of it. Next select ‘Rearrange’ and move your ‘Content: Edit Link’ field to the top of the Fields list. Apply the change.

3.)  Now go back and click on the ‘Content: Edit Link’ to bring up the field configuration screen.  Expand the ‘Style Settings’ section and make the following changes.  Be sure to change the HTML element to DIV and spell the class names exactly as below.

4

4.)  Scroll down further in the same screen until you see the ‘Rewrite Results’ section and expand it. Check the “Rewrite the output of this field” box and put the following HTML into the text box:

<ul><li>[edit_node]</li></ul>

5

5. )  Scroll up to the ‘No Results Behavior’ section and make sure that “Hide if empty” and “Hide rewriting if empty” check boxes are checked.  Apply your changes.

6.)  Lastly, you need to add some styling to the edit links wrapper. For my example I used the following which put the edit links in the top left of the content box.  If you want it to appear at the top right just leave out the ‘top:0px’ line.

.views-field-edit-node .contextual-links-wrapper {

    height: 50px;

    width: 50px;

    top: 0px;

    left: 0px;

}

If you want the wheel to be a different color than standard grey, you can use image editing software to alter the color of the image of the wheel at the following location:  “[your projects base url]/modules/contextual/images/gear-select.png “.

Hope this trick helps you as much as it has helped me! Feel free to reach out with any questions.

 

 

Symfony2: Using FOSUserBundle with multiple EntityManagers

By Ashish Datta | Leave a Comment on Symfony2: Using FOSUserBundle with multiple EntityManagers

Last week, we were looking to setup one of our Symfony2 projects to use a master/slave MySQL configuration. We’d looked into using the MasterSlaveConnection Doctrine2 connection class, but unfortunately it doesn’t really work the way you’d expect. Anyway, the “next best” way to set up master/slave connections seemed to be creating two separate EntityManagers, one pointing at the master and one at the slave. Setting up the Doctrine configurations for this is pretty straightforward, you’ll end up with YAML that looks like:

At face value, it looked like everything was working fine but it turns out they weren’t – the FOSUserBundle entities weren’t getting properly setup on the slave connection. Turns out, because FOSUserBundle uses Doctrine2 superclasses to setup it’s fields there’s no way to natively use FOSUserBundle with multiple entity managers. The key issue is that since the UserProvider checks the class of a user being refreshed, you can’t just copy the FOSUserBundle fields directly into your entity:

So how do you get around this? Turns out, you need to add a custom UserProvider to bypass the instance class check. My UserProvider ended up looking like:

And then the additional YAML configurations you need are:

The last step is copying all the FOSUserBundle fields directly into your User entity and update it to not extend the FOSUserBundle base class. Anyway, that’s it – two EntityManagers and one FOSUserBundle.

PHP: Geocoding with MaxMind and nginx

By Ashish Datta | Leave a Comment on PHP: Geocoding with MaxMind and nginx

Earlier this week, one of our adtech clients reached out asking if we could setup IP based geocoding for one of their applications. At a high level, what the application basically does is serve as a backend for an advertising pixel which is embedded on the sites of various publishers. When users are visiting a publisher’s site, the JS pixel makes a HTTP request to our backend from the client’s browser, receives a data payload from the backend, and then does various computations on the frontend.

What our client was looking to do was add the geocoding data into the payload that is returned by the backend. We’ve had some success with the MaxMind database in the past so we decided to investigate using that solution here as well. Initially, we implemented the geocoding using the static MaxMind database along with PHP and memcached to cache the “warmed” MaxMind PHP object. Unfortunately, using PHP presented significant performance issues at the scale we were serving requests. At an average of 20,000 requests/minute, the additional load introduced by the PHP processes serializing and deserializing the MaxMind objects would have ultimately been prohibitively expensive, even across 3 frontends.

So what’s the alternative? Turns out, there’s actually an nginx module that leverages the MaxMind database to make the geocoding data available as CGI parameters. Effectively, this lets you access the geocoding variables for the client’s IP address directly from the $_SERVER variable in PHP. Here’s how you set it up:

Depending on what your setup is, you’ll just need to enable the geoip module in ngnix by following the directions here. Once you have ngnix recompiled with the module on, you’ll need to add the configuration parameters specified into your “server” block. The final configuration step is to add the variables that you want exposed as CGI parameters. All together, you’ll need to end up making these modifications to your config files:

Reload or restart ngnix. Then, to access the variables in PHP you can just grab them out of the $_SERVER variable like:

That’s about it. From our tests, adding the geo module has a negligible effect on performance which is awesome. Of course, your mileage may vary but it’ll certainly be faster than using PHP directly.