On Apr 14, 6:11 pm, Kevin Cole <dc.l...@gmail.com> wrote:
> Hi,
>
> First let me say I've looked at documentation, but feel a bit dyslexic
> when it comes to this stuff.
>
> The situation: I have three tables: a service provider table with
> city, state abbreviation and country abbreviation (among other other
> info), a states table with state names, abbreviations, and other state
> info, and a country table with country names, abbreviations and other
> country info. I want an output like:
> _______________________
>
> FULL COUNTRY NAME
>
> Full State Name
> City..... Provider
> Provider
> City..... Provider
>
> Full State Name
> City..... Provider
> Provider
> City..... Provider
>
> FULL COUNTRY NAME
> _______________________
>
> I only want Country and State headings for countries and states having
> providers. Experimenting at the shell, it appears my model is okay:
> _________________________________________________
>
> >>> from mhd.models import *
> >>> countries = Country.objects.filter(iso2__in=["US","CA"]).distinct()
> >>> countries
>
> [<Country: Canada>, <Country: United States of America>]
>
> >>> countries[0].state_set.all()
>
> [<State: Alberta>, <State: British Columbia>, <State:
> Manitoba>, ...]
>
> >>> countries[1].state_set.all()
>
> [<State: Alabama>, <State: Alaska>, <State: Arizona>, ...]>>>
> countries[1].state_set.all()[1]
>
> <State: Alaska>
>
> >>> countries[1].state_set.all()[1].provider_set.all()
>
> [<Provider: Anne B. ... PsyS, LPC None>]
>
> >>> countries[1].state_set.all()[1].provider_set.all()[0]
>
> <Provider: Anne B. ... PsyS, LPC None>
>
> >>> countries[1].state_set.all()[1].provider_set.all()[0].practitioner
>
> u'Anne B. ... PsyS, LPC'
>
> >>> countries[1].state_set.all()[1].provider_set.all()[0].city
>
> u'Anchorage'
> _________________________________________________
>
> For the next 5 days, a whittled down version of what I thought I was
> trying to do lives on dpaste.com:
>
> http://dpaste.com/32704/model.pyhttp://dpaste.com/32755/views.pyhttp://dpaste.com/32706/listings.html
> template
>
> However my later experiments in the manage.py shell (above) lead me to
> suspect maybe I'm bass-ackwards about the whole thing. Unfortunately,
> suspecting that is still not enough to get me where I'm trying to go.
>
> Help?
>
> Thanks.
I very much doubt that select_related is the answer to your problem,
as proposed in your other post. select_related is purely for query
optimisation, and doesn't actually change the results of your queries.
That said, I am having trouble working out what your actual problem
is. If I understand correctly, it's how to order multiple nested query
sets. But in what way does the code you have posted fail to give the
right output? What does it actually give?
I can say that this line in your view:
providers = Provider.objects.order_by("country").order_by
("state").order_by("city")
does nothing at all. Each successive order_by will override the one
before.
I'm also intrigued by the way you've set up your tables. Are they
based on an already existing database schema? If not, I would
seriously reconsider the way you have the foreign keys set up to
reference character fields in the related model. Better to leave these
as the default so that Django manages the relationship with the
automatic 'id' field. (What happens if two states in different
countries have the same abbreviation?)
Looking closer, I suspect that the problem is that both state and
country are related to provider, instead of relying on the
relationship between state and country. I would leave out the FK from
provider to country, so the relationship goes like this:
provider -> state -> country
You might even think about having another intermediate model for City,
so the provider only has a relationship with that.
Finally, I would leave out all the 'regroup' tags. Your data is
already grouped, since it is in a set of nested relationships.
In the view, get all the countries that have providers:
countries = Country.objects.filter(state__provider__isnull=False)
And in the template, without all the table tags:
{% for country in countries %}
{{ country.name }}
{% for state in country.state_set.all %}
{{ state.name }}
{% for provider in state.provider_set.all %}
{{ provider.name }}
{% endfor %}
{% endfor %}
{% endfor %}
--
DR.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
