views:

460

answers:

1

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

+2  A: 

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.

AdamKG
VN44CA
I missed the JOIN somewhere ...
VN44CA
Use `.select_related("location")` on your `Item` query, then access `{{ item.location }}` in the template. That's really all there is to it...
AdamKG
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.
VN44CA