Django, query question

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?

Daniel C · Accepted Answer · 2011-07-08 19:20:26Z

1

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')

answered Jul 8, 2011 at 19:20

Daniel C

1139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

salezica · Accepted Answer · 2011-03-01 00:10:12Z

0

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.

answered Mar 1, 2011 at 0:10

salezica

77.6k29 gold badges113 silver badges169 bronze badges

Comments

Tarsis Azevedo · Accepted Answer · 2011-03-01 13:25:00Z

0

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

answered Mar 1, 2011 at 13:25

Tarsis Azevedo

1,52315 silver badges22 bronze badges

Collectives™ on Stack Overflow

Django, query question

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related