Ryan Kanno: The diary of an Enginerd in Hawaii

Since I actually used this method to reduce the number of Update:”explicit” SQL calls made in WeGoEat, I figured I’d write a little blog explaining the context in which it was used, and maybe, just maybe, it’ll help shed some light on how others can take advantage of this neat little function.

Background

As a Django “proof-of-concept”, I’m working on a local restaurant review site for my home state of Hawai`i. (I actually just released it yesterday). For each restaurant, I want to be able to calculate the average of all reviews and display this listing in a paginated view. (Yes, I do realize there’s no average rating, but that has to do with there being no users. ;P).

The Problem

Having a serious “wtf was I thinking moment”, I initially wrote a Restaurant model function that returned the average (review) rating for each restaurant instance. Little did I realize that when I actually displayed the restaurant’s average reviews, I would be making an additional SQL avg() call for every restaurant. Though I’m paging “n” records at a time, this function added an additional “n” SQL calls for every view that contained a restaurant listing, just to name a few.

In pseudo-code, my initial naive function resembled the following: (I’m sure we’re all guilty of writing something of the sort… ok, fine, I know I was. ;P)

1
2
3
4
5
6

     def get_average_review(self):
         query = 'QUERY TO GET AVERAGE (SELECT AVG(rating)...); (I have the query below)'
         # Get cursor from connection
        cursor = connection.cursor()
        cursor.execute(query)
        return cursor.fetchall()

Duh.

Here’s a picture of the number of queries it took:

The “extra()” solution

After profiling my application and realizing what a bone-headed mistake I made, I began researching the extra() Queryset modifier. Yes, I realize that these extra lookups aren’t the most portable and often violate the DRY principle, but it’ll probably suffice for most of all my personal projects.

Since I’m already retrieving a list of Restaurants and filtering them via letter, island, and what not, I figured I could add an average rating subquery. The entire call looks as such:

1
2
3
4
5
6
7

     restaurants = Restaurant.objects.filter(name__istartswith = letter).extra(
             select={'<strong>avg_rating</strong>': 'SELECT AVG(overall_rating) FROM restaurants_restaurant as res, reviews_review, django_content_type \
                                          WHERE restaurants_restaurant.id = res.id \
                                          AND res.id = reviews_review.object_id \
                                          AND reviews_review.content_type_id = django_content_type.id \
                                          AND django_content_type.model = \'restaurant\''},
                       )

As you can see, I’m exploiting the fact that restaurants_restaurant will be available from the Restaurant.objects.filter() call. (I know, I know… bad for portability).

But voila!

Now, in my templates, when I iterate over the restaurants, I can get issue the following:

1
2
3
4
5
6
7
8
9
10
11

{% for restaurant in restaurant_list %}
<tr>
    <td><a href="{{restaurant.get_absolute_url}}">{{ restaurant.name }}</a></td>
    <td>{% if restaurant.avg_rating %}
	   {% load show_stars %} 
           <span class="average-rating">
	   {% show_stars <strong>restaurant.avg_rating</strong> of 5 round to quarter %}
           &lt;/span&gt;
           {% endif %}&lt;/td&gt;
&lt;/tr&gt;
{% endfor %}

Notice how I used my show_stars template tag that I blogged about a few weeks ago to display the average restaurant rating. (Cheap shameless plug, but damn effective! ) I’d link to a page in action, but since I just opened up my site to a few select users, I’ll update this post when I actually have any reviews.

Oh, and before I forget, thanks to my co-worker Stephen for assisting me with my SQL issues!

Here’s a picture of the final result:

Note:

As an added bonus, I also realized a few other ’spots’ where the .extra() Queryset modifier would come in handy. Since I’m also using the wonderful django-voting application from Jonathan Buchanan, I came across this post about accessing a dictionary via a template in the Django-users Google Group.

Basically, I had come across the same issue as the poster. Since I allow users to vote on reviews (similar to Amazon, Yelp, etc.), I wanted to retrieve the score of each Review instance to display on a paginated listing of all Reviews. Using the same extra() modifier, I was able to inject the total number of votes and the score when I retrieved all Reviews as such:

Btw, I just injected most of the code from Jonathan’s template tag.

1
2
3
4
5
6
7
8
9
10
11

.extra(select={'total_votes': 'SELECT COUNT(vote) FROM votes as v, reviews_review as rev, django_content_type \
                                        WHERE reviews_review.id = rev.id \
                                        AND v.object_id = reviews_review.id \
                                        AND v.content_type_id = django_content_type.id \
                                        AND django_content_type.model = \'review\'', 
 
                                        'score': 'SELECT SUM(vote) FROM votes as v, reviews_review as rev, django_content_type \
                                        WHERE reviews_review.id = rev.id \
                                        AND v.object_id = reviews_review.id \
                                        AND v.content_type_id = django_content_type.id \
                                        AND django_content_type.model = \'review\''},)

Pretty neat right?

Now, when iterating through the reviews, I can use the following:

1
2
3
4
5
6
7
8
9
10
11
12
13

{% for review in object_list %}
	<tr>
		<td><a href="{{review.content_object.get_absolute_url}}">{{ review.content_object.name }}</a></td>
		<td><a href="{% url profile-detail username=review.user.username %}">{{ review.user.username }}</a></td>
		<td><nobr>{% load show_stars %}
			<span class="rating">{% show_stars review.overall_rating of 5 round to half %}</span>
			</nobr>
		</td>
		<td>"<span style="font-weight:bold; color:#092e20;">{{ review.get_recommendation_display }}</span>"</td>
		<td><span style="font-size:.875em;">{{ review.submit_date|timesince }} ago</span></td>
		<strong>&lt;td&gt;Total of {{ review.score|default:0 }} from {{ review.total_votes }} {{  review.total_votes|pluralize:"person,people" }}.&lt;/td&gt;</strong>
	&lt;/tr&gt;
{% endfor %}

Hope y’all learned something like I did! Oh, and before I forget my standard disclaimer, “since this is on my blog, feel free to take/use/steal/distribute/copy/modify any code you see fit, but if you find any bugs, have any comments, or think the code can be cleaner, I’d love to hear from you.”

Enjoy!

Popularity: 44% [?]

Once upon a few blogs ago, I wrote about six excruciating hours as an extra on a relatively popular ABC television series. All my hard work finally came to fruition this past Wednesday as the second episode of this season’s Lost premiered.

And as much as I’d love to tell everyone of what a movie stud I am, my dad always told me, ‘honesty is the best policy‘. The result of six hours of filming, you ask? Well, put it this way – 6 hours of filming resulted in a 6 second clip in which you can barely make out the back of my head. So now that it’s cemented I’m an official movie stud dud, take a peek at my youtube to see what all the fuss is about.

Check out my youtube for clips of friends that were also extras!

Popularity: 5% [?]

Something really, really exciting happened to me this weekend.

(And no, I didn’t win a million dollars.)

For those of you that I haven’t told, one of my best friends seriously hooked me up (thanks, bro) – as an extra on a relatively popular ABC television series. Since I moonlight as an enginerd during the day, I wasn’t about to let this experience pass me by. Who wouldn’t want to experience the wonderful world of Hollywood?

And let me tell you, what an experience it was.

The day started early; at 6:45 am – all the extras needed to be on set. Even though I had crawled into bed at 10 the previous night, my nerves allowed for a sleepless night. After checking in and getting approved by makeup and wardrobe – off they shuttled us to the ‘undisclosed, secret set location’. Since I was to partake in an Asian funeral scene, all the males were outfitted in a black suit and tie. Additionally, since my family has been in Hawai`i for the better part of four generations, I decided to wear my black wired-rim glasses to appear more ‘Asian.’

Upon first arriving on the ’secret set’, I immediately noticed that there were some really attractive extras, both male and female alike. Yeah, these extras were ‘the beautiful people, the beautiful people‘… Of course, this made me even more nervous since I’m not exactly your Brad Pitt or Tom Cruise Ken Watanabe. My fears were mollified when I caught a glimpse of all the extras – it was quite apparent that even Hollywood needed ‘normal-looking’ people.

Since it was a funeral scene, all the extras were guests at the funeral. I was given various background ‘action’ roles that consisted of walking as though I had just arrived. All in all, we filmed for the better part of 5 and 1/2 hours – all for a glorious, 2 minute scene on television.

Based on the experience, I can tell you that I’m amazed at the director’s strive for perfection. We must’ve shot the same scene at least a dozen times, maybe even more, and this isn’t even including all the different angles. Being an enginerd, I can relate to the feeling of always trying to produce that ‘perfect product.’ Second, I now have the utmost respect for extras. In my wildest imaginations, I would never have guessed the amount of work that the job entailed. Albeit, the experience wasn’t exactly a stimulation of the mind, but it does require long hours, quite a bit of standing around looking ‘pretty’ (mind you – in the grueling, mid-day Hawaiian heat), and really, just enduring the madness of scene repetition. I know that doesn’t sound like much, but I can tell you first hand, I’m not so sure being an extra is all that it’s cracked up to be.

Overall, I can tell you that partaking as an extra was an amazing experience. I know, I know – I sure didn’t make it sound like one, but being trapped at a keyboard for 9 hours a day, it was something ’so fresh and so clean clean’. Even as a kid, growing up in Mililani, I’ve always dreamed of what it would be like in front of a camera. And after everything’s been all said and done, I’ve come to realize that I love being an enginerd.

I’ll do the thinking and let Hollywood and the pretty people worry about the acting.

Popularity: 7% [?]