Django select_related() for multi-join query using model & filters

Question

Hi,

I have two Models: Job & Location:

class Job(models.Model):
   title = models.CharField(max_length=20)
   company = models.CharField(max_length=20)
   location = ForeignKey('Location')

class Location(models.Model):
   country = models.CharField(max_length=20)
   state = models.CharField(max_length=20)
   city = models.CharField(max_length=20)
   latitude = models.FloatField(blank=True, default=0.0)
   longitude = models.FloatField(blank=True, default=0.0)
   big-city = ForeignKey('Location')

Let's say: I have US/Calif/San-Fran, US/Calif/San_Jose, US/Calif/Alameda & US/Calif/Oakland in my database. I also have Manager/Ebay/San-Fran, Accountant/Amazon/San-Jose, Coop/IBM/Oakland & Director/Dell/Alameda.

Also: San-Fran has itself as big_city, while San-Jose, Alameda & Oakland have San-Fran as their big-city.

Now I do a query like this when someone is searching for all jobs in San-Fran.

Job.objects.filter(
location__country='US', 
location__state='Calif', 
location__city='San-Fran').selected_related('Location')

However, I'd like to allow for search by region where user can search for all jobs in San-Fran Region. This would be all jobs in San-Fran, Oakland, Alameda & San-Jose?

Like "Show me all the jobs that have their location is reference by other locations".

Would this be called a double-join?

Ideally, I would be using lat-lon-radius (later exercise), but for now I want to know how to do it with a double join.

Thx.

Vn44ca

Answer 1

Here is a query that should do what you want:

Job.objects.filter(location__big_city__city='San-Fran', location__big_city__state='Calif', location__big_city__country='USA')

And indeed, Django uses a join twice on the Location table when running this query:

SELECT "example_job"."id", "example_job"."title", "example_job"."company", "example_job"."location_id" FROM "example_job" INNER JOIN "example_location" ON ("example_job"."location_id" = "example_location"."id") INNER JOIN "example_location" T3 ON ("example_location"."big_city_id" = T3."id") WHERE (T3."country" = USA  AND T3."city" = San-Fran  AND T3."state" = Calif