Django -- ForeignKey() uses different column than the row ID?

Hi,

I have an application called Location. Location has Country, State, City, Big_City_Nearby, Longitude, latitude.

I have an application for an item for sell. Item has Title, Link, Description, Price & Location which is a ForeignKey().

Now, if someone wants to see all items for sell in the US, they click on a link (let say http://example.com/United-States/) and the URL will pass in the "United States" as country with state, city and big_city_nearby empty.

#in view.py
class by_location(request, country, state, city, big_city_nearby)
   location = Location.objects.filter(country=country, state=state, city=city, big_city_nearby=big_city_nearby)
   items = Items.objects.filter(location__in=[location.id for loc in location])
   return render_to_response('item/by_location.html', {"items":itmes, "country":countyr, "cit":city, "nearby":big_city_nearby})

There is no problem if they pass in (http://example.com/United_State/California/Oakland) as I can go ahead and list all the items in Oakland without a need to display the location per items found.

The problem is when the select http://example.com/United_States/ or http://example.com/United_States/California/. As I have to now display the location of each items as the items can be from different cities.

So when the template gets the list of items, it only gets the ForeignKey(Location) in each item. I can put in place another loop, where each item is examined and the Location object is fetched and put in a tuple with the item object. But that would be very SQL inefficient. As I would have to hit the database for each item found to get the actual location object.

Is there a way where I can say the ForeignKey is holding the city instead of the Id to the location row in the database.

To make the question simple: Is there a better way of leaving the location out as an application, without a need to provide country, state, city ...etc. in each item row.

Thx,

VN44CA

From stackoverflow

• Item.objects.filter(...).select_related("location"). This will JOIN against the locations table, so it'll just be one query.

  Even better, Item.objects.filter(location__country=country, location__state=state).select_related("location") and you can omit the Location query entirely.

  Honestly, I'm having trouble figuring out if that's all you need, but that handles the O(N) queries issue.

  VN44CA : You allow people to sell stuff on your site. Person A only sells item A locally in US/CA/Oakland, Person B only sells item B in US/CA/* and person C sells item C in US/*. Now if you don't want item A, B & C contain any information about country, state, city, long & lat as you might have thousands of items and that would take up much space. What you have here works to get the items, but how do you pass the location of each item to the template? How would I get/pass the location objects for each item to the template so I can print the related city where the item is found?

  VN44CA : I missed the JOIN somewhere ...

  AdamKG : Use `.select_related("location")` on your `Item` query, then access `{{ item.location }}` in the template. That's really all there is to it...

  VN44CA : Thank you very much for clearing thing out! It is exactly what I need. Now, how efficient is a join of this sort? Also, If the location was using itself as foreignKey, would a double join be possible? Like: items = Item.objects.filter(location__country=country, location__state=state,location__location__city=city).select_related('Location').select_related('Location')

  VN44CA : What I want is: return all items whose locations points to a common/center location. Location is using itself as foreignKey. So if you select the /us/ca/oakland, simple join works, but if you select /us/ca/oakland/area/ then all cities around oakland (e.g. Alameda, Berkeley .. etc.) that have oakland as foreignKey (big_city_nearby) would be selected. don't know if we have double join or not as I just learned about join today. Thx very much.