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:

Boston Tech Startup Spotlight: Tomorrowish

This is the first of a series of short blog posts highlighting interesting local Boston tech startups – up to bat is Tomorrowish, whose company tagline is “The First Social Media DVR”.

Website:  http://tomorrowish.com
Twitter:  @tomorrowish
Headquarters:  Cambridge, MA

Selection_030

Initially founded 2011 under the name “TweePlayer”, the company was re-branded to “Tomorrowish” in 2012 and is currently headquartered in Cambridge, MA. The privately held company is now looking to raise $1.5 million in funding with a focus on the US Market.

Tomorrowish’s tools and services are targeted towards creators and viewers of digital media.  For creators, their platform lets them capture, curate, and stream social media commentary about their broadcast. That conversation is archived and synchronized in time with the media. When their content is viewed at a later date, their audience can engage in both the current conversation as well as what others have said about certain moments during of the show. Tomorrowish supplies creators with APIs and white-labeled customizable widgets and services to stream the content.

Viewers have access to content available from the Hulu content library as well as other from other media players such as Youtube and Vimeo. They can access content on http://www.tomorrowish.tv/ or through a similar feed setup on their content provider’s website.

The “brains” behind their service offerings lies within what they refer to as the Tomorrowish Machine Curation (TMC), an algorithmic system which filters through the thousands of social media comments about a show and chooses the most interesting ones to display.  It also uses standard metrics such as popularity and language, along with customized black-listing and white-listing rules to further filter content.  Additional manual filtering can also be applied if the content provider wants to make sure a certain phrase, person, or keyword is included (or not).

Here’s a link to a youtube video and accompanying slideshare posted by Mick Darling, CEO at Tomorrowish from a presentation given at Turner Broadcasting’s Media Camp Demo-day on Sept 12th 2013.

http://www.youtube.com/watch?v=3hjV0Q-Sl1o&feature=youtu.be

http://www.slideshare.net/mickdarling/tomorrowish-pitchdeck

You can read more about how it works at http://tomorrowish.com/.

Stay tuned for the next Boston tech start-up spotlight!

Musings: Could you leverage Twitter to make some money this holiday season?

A few days ago, I was browsing my Feedly dashboard and ran across this AdWeek post describing how big retailers are gearing up to poach their competitors customers this holiday season. The article goes into some specifics, but the idea is basically that brands are planning to monitor Twitter for relevant conversations and then “at” message potential customers with special offers, product details, or even local store inventory information.

So imagine @MikeBruins65 from Boston tweeting “Wtf! @BestBuy offering 25% off all 4K TVs in-store…except nothing in stock.” and then @target replying “Cheer up @MikeBruins65! We have 4K TVs in-stock in Everett, MA! Grab coupons at http://bit.ly/target-4k-ma”. Since these brands are certainly leveraging powerful tools like Radian6 or even the full Twitter Firehose, it seems like it would be straightforward for them to execute strategies like this around high value markets. But what about as an individual, could you employ a similar strategy to make a few bucks?

Amazon Associates Links

The most obvious, least risky, and least lucrative approach would be to monitor Twitter for tweets that sounded like they were from frustrated buyers and then message them Amazon associates links for the product they’re looking for. Looking at Amazon’s fee structure, you’d want to target high margin categories with moderately expensive products and then hopefully end up doing a decent amount of volume. So imagine searching for Tweets from users frustrated that they can’t checkout on a small eCommerce site, finding the product they’re searching for on Amazon, and then Tweeting them the link to buy with your Associates link.

Dropshipping

More risky and potentially more upside. I’m not entirely sure how feasible this would be, but I think the idea would be to use a SaaS eCommerce platform like Shopify to setup an eCommerce shop and then dynamically list items which you’ll later dropship. The challenge would be two fold, using Twitter to identify which previously obscure items are starting to trend and then figuring out how to introduce enough margin so that you end up profiting on the sale. It might be feasible though, with the explosion of small, boutique eCommerce sites it might be possible to negotiate a “I’ll buy 400 for 50% off!” type deal quickly enough to introduce a profitable sale. The bigger challenge would probably be identifying these items as they start trending, but that could be solved by….

Pinterest

Recent member of the billion dollar boys club and frequent target of “haters”, it’s current traction and latent purchase intent potentially make it the perfect place for affiliate marketing. Beyond that, the wealth of potential gift pins and the follower/repin graph might hold the key to identifying relatively obscure products right before they begin to go viral. Anyway, I don’t have any concrete ideas on how you could leverage Pinterest but it definitely seems like the ingredients for success are there.

Totally coincidentally, this article just came across TechCrunch – A Pin On Pinterest Is Worth 25% More In Sales Than Last Year, Can Drive Visits & Orders For Months

Anyway, are any of these actually feasible? Who knows, but I’d love to hear any other ideas.

First LinkedIn Intro, then BonzyBuddy 2.0

Last week, LinkedIn published an indepth technical explanation of how their new LinkedIn Intro mobile product works on iOS. What Intro does is basically display LinkedIn data about your contacts directly in your email client – similar to what Rapportive did for gmail. It’s a cool app but the implementation details LinkedIn shared ignited an Internet firestorm, especially among the startup/hacker crowd.

How Intro works is it basically modifies the users normal iOS email client so that it connects through a LinkedIn proxy server instead of interacting with their webmail provider directly. What this does, is allow LinkedIn to dynamically modify a user’s email before it reaches their mail client, depending on if the user is connected to the sender on LinkedIn. From a IT security standpoint, introducing a third party that would sit between a user and the mail server they’re connecting to undoubtedly introduces a new attack vector but what really caught my interest was how LinkedIn was achieving this. In order to smoothly update the user’s proxy settings, LinkedIn is using a iOS feature known as Configuration Profiles.

I’m not familiar with the iOS SDK or APIs so this was the first time I’d heard about Configuration Profiles. In short, what they allow an app to do is install a set of settings on an iOS device – from email and web proxy settings to additional credentials and SSL keys. Configuration profiles are typically used in enterprise environments to allow a company’s IT department to quickly configure the settings on an employee’s iOS device. When provisioning a new device, IT would basically use the configuration profile to install things like a VPN, internal credentials, etc. So what’s the problem?

Well according to the LinkedIn post and comments from users that have used profiles before, the user experience of installing a profile which radically alters your iOS system settings is surprisingly unassuming. As a user, you click through a couple of prompts and boom, all of a sudden Safari is using a proxy server to fetch websites. So what nefarious things could you do by routing iOS mobile traffic through a proxy server? Unsolicited injected display advertising.

On the desktop web, unscrupulous extension developers have been monetizing their install base by injecting display ads into the browsing experience of their users for years. From companies like Bonzi Buddy to newer companies like PageRage, the model is tried, true, and profitable. However, on mobile there isn’t an obvious opportunity to inject ads and get access to the rapidly growing number of mobile web impressions. It seems like using configuration profiles would be the perfect vector to change this. Crapware iOS developers could quietly prompt their users to install a configuration profile to get access to “hot new features” and then surreptitiously start injecting display ads into websites on the proxy server.

I’m not familiar enough with iOS development to speak to how easy developing an app like this would be or if it would get past the app store approval process, but if it’s feasible someone is certainly going to do it. If anyone is familiar with an app already doing this, I’d love to know about it.