For most cases, the Django ORM does a fine job of handling the queries I want. Sometimes I run into difficult queries and I get a little discouraged because I don't want to break all the way out of the model and use raw SQL. I want to stay in the framework and still get the results I neeed. I ran into one of those situations today and found out about QuerySet's extra() method.

Let me give you a little background on our system before I go further. The central and most critical piece of information we store are User Profiles. Profiles have gender and sexual preference attributes which are the most important two attributes for generating possible matches of people. The possible matches get voted on by anyone interested in playing matchmaker and these votes get tallied constantly. Weekly, each user will have a report of their top matches generated for them. It's simple until you start trying to filter out matches based on who has blocked who.

The options here are to do the filtering programatically after the results are returned or to do some special SQL. I opted for doing some SQL subqueries which required an understanding of Django that I didn't have before. Essentially, you can modify the SELECT statement that Django generates for QuerySets by using the extra() method. The where parameter of that method takes a list of strings, then ANDs them together into the WHERE clause of the query.

So I could write code like this:
sbuquery1 = 'profile2 NOT IN (SELECT blocked_user FROM blockuser WHERE blocker = %d)' % self.id
matches = Match.objects.filter(profile1 = self.id).extra(where=[subquery1])And the resulting SQL would look something like this:
SELECT * FROM match WHERE profile1 = 1 AND profile2 NOT IN (SELCT blocked_user FROM blockuser WHERE blocker = 1)I'm leaving out a lot of details like what fields the tables contain and that Django will put every column name into the SELECT statement so the SQL statement is much longer than what I have above, but I didn't want to clutter up this example with all that. There are other examples that I found that go into more detail that I have.

These two links had some valuable information for me:
Kyle Fox's : Minimize database queries using Django’s QuerySet.extra()
Django snippets: Custom SQL via subquery

Our error search engine, bug.gd, was created out of the desire to prevent the world from solving the same problems again and again. We feel that the community can thrive best when people solve problems and then share those approaches with everyone else. Many unlucky people have to abandon what they were doing simply because they couldn’t figure out the error on their own– but this won’t be as common if we all share our workarounds either on a blog, mailing list, or a site like ours.

A lot of people are inspired during the holidays to help humanity, give to charity, and generally do a lot of nice things. We all know we should be helping out more often, but this time of year brings out that spirit in a lot of us. In that same spirit, we have a challenge for you.

Holiday Challenge for Charity

Here’s our holiday challenge to you: For the next month, search for all of your error messages on bug.gd or through our Firefox extension. You’ll hopefully find your solutions quickly, but do the right thing– provide your solution. We’ll even remind you, in case you forget.

If you do, your findings might just help the unlucky dad with the crashing app on Christmas Day. Or maybe you’ll help the poor student trying to compile some open source project for the first time over vacation. Whatever it is, even if it’s only a little hint or a reboot recommendation– share it. We’ve got to work together on these things, you know?

Still, if that’s not enough to inspire your giving spirit, we’ll donate $1000 to a charity if we hit our goal of 1,000 new, valid solutions through the end of the year. It’s just our way of showing that we mean what we say– we want to work together with you to help humanity.

And before I forget:

Happy Holidays from everyone here at bug.gd!

We're busy working on a great new way to meet people and a fun way to that you can help be a matchmaker yourself.

More details coming soon.

I’ve never been to Buffalo before, but WIVB-TV News4 in Buffalo, NY found our site and ran a story on it recently. Take a look on Lindsay’s Links:

[ Full Coverage ]

Just a quick update on some of the things people are saying on bug.gd.

Altsearchengines gave a solid write-up on our new launch:

There’s also a nice and succinct write-up on our new bug.gd relaunch over at makeuseof.com:

We’re very excited to announce a major new release of bug.gd and our new corporate error search engine, ErrorHelp.com.

Stop repeating research

If this is the first you’ve heard of bug.gd, here’s our goal: No one should ever have to solve an error that someone else already solved. We all need to stop wasting time on things people have solved! Let’s work together.

We make this work on bug.gd by remembering the errors you search for and reminding you to come back and provide a solution. Our service has been used by thousands of people and over the past year, we’ve accumulated thousands of solutions.

Solving errors better than ever

Today we launch the second generation of bug.gd. The three biggest changes are:

• Web Results– We now offer web results for your error messages, which we can optimize to find the best matches for your error better than any other search engine.
• Easier to use– We’ve streamlined the solution interface– you can enter your solutions quickly and easily just by visiting your My Bugs page.
• Fresh decor– We got a lot of feedback from people who loved our site concept, but couldn’t stand the strong black/white theme. We’ve redesigned the look of the site from the ground up to be more comfortable.

Combined with our recent Firefox extension, there’s no reason you should search anywhere else for error messages.

The guy down the hall solved your error before– but how would you know?

Along with our relaunch comes ErrorHelp.com, which is our service for employees who are tired of solving the same stuff their coworkers solved beforehand.

Think about it. Surely you’re not the only one who has run into that intranet error, data-entry program crash, or random Windows popup. Should you send out a public email blast? Nope. You search for the error on ErrorHelp.com and you find others who had the same problem. If you’re the first, you can save the company time down the road by saving your solution forever.

ErrorHelp.com is for companies trying to get the most out of their IT budget without waiting on the help desk– any employee can start their own free error database and leverage their coworker’s knowledge.

The base service is completely free with no obligation– you can begin searching right now. Over time, we’ll add premium features for privacy, metrics, monitoring, tools, and other things that IT managers love. But you don’t need any of that to get started.

We’re very excited about the new features and will be talking about them again. We’d *love* to hear your feedback, so let us know how we’re doing.