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

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

From stackoverflow

• 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
  

  VN44CA : Doesn't have to be: Job.objects.filter().selected_related('Location') or was it assumed? If so, do we need to put two of these: .selected_related('Location').selected_related('Location') to indicated a double join? If so, What if Class A has B as FK and Class B has C as FK and you want to perform a double join, then would it be: A.objects.filter().selected_related('B').selected_related('C') Thanks Ayman

  Ayman Hourieh : select_related() is recommended if you plan to access ForeignKey fields in the resulting QuerySet. Only one call without args is necessary; select_query() follows foreign keys as far as possible. So if you want to access the location field in the result, use select_related().