Django, query question

I have a mysql table full of geo data like so:

locations
  - city
  - state
  - zipcode
  - lat
  - lon 

Since major towns have more than one zip code, there are multiple records for each town.

example:

"city": "BOULDER"开发者_运维技巧, "state": "CO", "zipcode": "80310"
"city": "BOULDER", "state": "CO", "zipcode": "80322"
"city": "BOULDER", "state": "CO", "zipcode": "80308"
...
"city": "BOULDER CITY", "state": "NV", "zipcode": "89005"
"city": "BOULDER CITY", "state": "NV", "zipcode": "89006"

I'm creating an autocomplete plugin for my site and so I need to do a query for city='BOULDER', state='CO' and have it only return 1 result for that city, not 3 like it would using the data above.

I tried using group by with .values('city'), but then I only get back the city name, not a dictionary like object of all of my fields (like I need).

Do any of you expert query folks know how I could accomplish this in Django 1.2?

The best solution is to change your table, but this would get you a dictionary with all the information you need:

def location_query(my_city, my_state):
    my_dict = {'city':my_city,'state':my_state}
    zips = Location.objects.filter(city = my_city, state = my_state).values_list('zip', flat = True)
    my_dict['zips'] = zips
    return my_dict

boulder_dict = location_query('Boulder', 'CO')

You can accomplish what you want using the values() method like you mentioned, but including the other fields you need, and excluding zipcode. I think there's an actual exclude method as well: check the documentation.

However, your design is flawed: it's redundant. The fact that you get 3 city entries for Boulder City is, well, because there's 3 city entries for Boulder City!

You should have a zipcodes multi-value field.

You can create a Zipcode class and connect city with ForeignKey.