django orm and 3 relations

Question

Hi,

I have a problem to transpose my sql request in the orm.

Well, this my sql request :

SELECT DISTINCT accommodation.id from accommodation
LEFT JOIN product on product.accommodation_id=accommodation.id
LEFT JOIN date on date.product_id = product.id
WHERE date.begin> '2010-08-13';

So i want all the accommodations for a period, without doubloon.

My models are like this :

class Accommodation(models.Model):
    ...

class Product(models.Model):
    ...
    accommodation = models.ForeignKey(accommodation)

class Date(models.Model):
    ...
    begin = models.DateField()
    product = models.ForeignKey(Product)

So for the moment i do that :

dates = Date.objects.filter(begin__gte=values['start_day'],

        ...)
...
accommodations_dict = {}

for date in dates : accommodations_dict[date.product.accommodation.slug] = True

accommodations = Accommodation.objects.filter(slug__in=accommodations_dict.keys())

Well, it works, but it's slow, I have a lot of dates.

So I think I can get these accommodations with sql, but I don't know how ?

if you have an idea, thanks.

Answer 1

This should do what you want:

 Accommodation.objects.filter(product__date__begin__gte=values['start_day'])