Inner join query with distinct using Django ORM

One point of the Django documentation that I did not get right at first is : how to get the ORM to generate a bit complex join queries. Yeah, sorry, I learnt SQL before ORMs, as many people, and I definitely think in terms of relational models and SQL joins.

For example, given the following models involving deliveries of products in a driver schedule, how do you get the list of products appearing in a schedule ? (I know that this sounds a lot like a Data Base 101 course).

class Product(models.Model):
name = models.CharField(max_length=150)
class Schedule(models.Model):
name = models.CharField(max_length=150)
class Delivery(models.Model):
.... some data fields here
product=models.ForeignKey(Product)
schedule=models.ForeignKey(Schedule)

The answer is deceptively simple, just write :

s = Schedule.objects.get(....)
Product.objects.filter(delivery__schedule = s).distinct()

which roughly will translate into the following SQL :

select distinct(product.*) from product
inner join delivery on product.id = delivery.product_id
inner join schedule on schedule.id = delivery.schedule_id
where schedule.id = some_id

The important thing to notice is that in the line

Product.objects.filter(delivery__schedule = s).distinct()

you can use delivery as the beginning of the lookup parameters, while there is no delivery field in the Product class. Django will understand.

What is maybe a bit misleading (even if it is quite logical if you think about it), is that there actually is a field named delivery_set created on the Product class (it is a RelatedManager), but you can not use it in the lookup parameters. It took me a long time to figure this out, so it might be the case for other people too....

That said, everything is explained in the Django doc here even if for once, I find the explanation a bit perfunctory

Exporting a Schedule from a Django Application to Google Calendar

Disclaimer

This blog post is not very original, it is essentially a mix of two previous blog posts, one from Andrew Turner and one from Derek Willis.

And now, the real content

For my pet project, ZoFa, I wanted to allow my users to publish their schedule, computed on the site, to their favourite calendar application, particulary to Google Calendar, which is the calendar application I am using myself.

To do this, there are two steps to accomplish : First,create a view generating an ical file (with extension .ics) I used the vobject library to do this as showed here.

By the way, vobject is a part of the doomed Chandler project described in excellent book. Highly recommended.

Having battled quite some time with this, I think that the following cod snippet could be of interest to some people. So, here is the code of my view :

def ical(request, worker_id):
worker=Worker.objects.get(pk=int(worker_id))
cal = vobject.iCalendar()
cal.add(‘method’).value = ‘PUBLISH’ # IE/Outlook needs this
for shift in worker.shift_set.filter(published=True):
vevent = cal.add(‘vevent’)
vevent.add(‘dtstart’).value=shift.begin #begin and end are python datetime objects
vevent.add(‘dtend’).value=shift.end
vevent.add(‘summary’).value=shift.name
vevent.add(‘uid’).value=str(shift.id)
icalstream = cal.serialize()
response = HttpResponse(icalstream, mimetype=‘text/calendar’)
response[‘Filename’] = ‘shifts.ics’ # IE needs this
response[‘Content-Disposition’] = ‘attachment; filename=shifts.ics’
return response

Finally, just add the url to your urls.py :

url(r’^ical/(?P<worker_id>\d+)/shifts.ics$’,‘ical’,name=“shifts_ical”)

It seemed necessary for Google to pick it up to add a file name at the end of the url, but I am not sure about this.

The second step needs that your user import the url of your calendar into Google Calendar. To do this in the current interface :

  1. open the “other calendars” little frame
  2. click on “add” (at the bottom)
  3. click “add by url” and paste the url.

Some concluding remarks :

  • It can take some time before Google really requests your calendar and displays it
  • The .ics file generated can also be used in Outlook, ICal or Thunderbird.
  • Your url needs to be publicly available : there is no way to provide to Google Calendar a login and a password to your site.

The next step for me would be to automatize the addition to google calendar (with a nice “Add your schedule to Google Calendar” button) but I still have no idea about the feasability of this.

djangocon.eu 2010 Impressions

Last week, after two years of using and being delighted at the Django web development framework, I travelled to Berlin to meet the Django community at Djangocon Eu in Berlin.

Here are my impressions:

The attendance

People were very friendly, open and quite talkative, at the opposite of what I feared for such a technical conference. From my previous life in research, I am used to people brought together to present their research material, while not that much interested about others work. This was definitely not the case here.

I did not make any survey, but I think you could put most people in 3 big categories: web freelancers, web agency employees, and students . I did not meet any other person working like me in a company using Django for internal projects, although I think this is quite common. Most original occupation I encountered : a Swiss army lieutenant. And only men except for one exception...

The talks

The level of the talks was not necessarily always excellent, but again, coming from my experience of boring scientific talks beginning with an equation, I was quite enthusiastic. That said, this type of conference is suffering from the widely different levels of skills in the attendance that makes possible to get beginner-intermediate level talks (like Honza Král talk about testing) next to quite advanced talks (like Armin Ronacher talk about wsgi). Everything for everybody I guess, but also something inadequate for each one too.

My highlights are not very original : Jacob Kaplan-Moss Keynote, and Russel Keith-Magee talk about the Django development process. I liked those two presentations because they allowed me to get a hint of the opinions of key personalities in the Django community, while enjoying their very good presentation skills. They definitely made me want to contribue and gave me the feeling that the core developers of Django are constituting a well balanced team, where no one is the real star attracting all the attention (on the opposite of the Drupal or Ruby on Rails communities for example). The egos seem to be well managed. Furthermore, their attention to the human side of the problems is really interesting. I heard at leat thrice the motto "We must avoid technical solutions to people problems" coming out of their mouth, which I found quite significant. In my opinion, this makes for a really attractive community.

One final note on the talks topic : I know that NoSQL is the big buzzword currently and I can not decide if this is the case for good reasons but I am not sure that the attendance did enjoy so much time spent talking about it.

The organization

The organization, venue and food were all really great. Kudos to the organizers. As I have read on Twitter, those little tables with electrical plugs and excellent quality wireless network were really a plus.

Fun facts

  • Like in rock concerts, it seems cool to wear t-shirts of previous editions, the older the better.
  • There is a trend to make slides with no more than 5 words per slide, where almost all the space is devoted to huge pictures, possibly unrelated to the current topic and usually taken from Flickr
  • Most people were multitasking to death : at the same time talking to their neighbours, tweeting, reading web sites (generally the ones cited by the speaker, but not always), posting pictures to Flickr. It enforced to me the image of the web developper who needs to be able to swallow tons of information daily, and to switch context instantly.
  • Twitter is the second venue of the conference, where questions and answers are exchanged during and after the talks.
  • I think there were more smartphones than people.

Conclusion

I am very happy that I did the effort to go to Berlin to meet like-minded people in real life. I came back with lots of subjects I want to dig in (Hudson, Class based views, South, and so much more)

In summary, even if I definitely think that those words are hugely overused in this community : Djangocon was AWESOME!. Djangocon For The Win !

P.S.: Remember that English is not my mother tongue. I will gratefully commit all grammar and spelling patches...

Follow @madewulf.