On Apr 14, 6:32 pm, Daniel Roseman <roseman.dan...@googlemail.com>
wrote:
> 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:
>
> > 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:...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.
>
> 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?
The order of the output was completely random, or so it appeared. A
country would show up, with a few randomly selected states, and the
corresponding cities. After a few records from another country, the
first would appear again. In other words, it didn't appear to be
sorted at all. Since adding the select_related, and changing my
template the following, I now get the correct results:
{% regroup providers by country.name as nations %}
<table cellpadding="0" cellspacing="0">
{% for nation in nations reversed %}
<tr>
<td class="country" colspan="2"><strong>{{nation.grouper|upper}}</
strong></td>
</tr>
{% regroup nation.list by state.name as provinces %}
{% for province in provinces
%}
<tr>
<td class="state" colspan="2"><strong>{{province.grouper}}</
strong></td>
</
tr>
{% regroup province.list by city as towns
%}
{% for town in towns
%}
<tr>
<td class="city">{{town.grouper}}</td>
<td class="listing">
{% for unit in town.list %}
> 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 had originally tried it without the order_by in the view.py. I
removed it when I added the select_related.
> 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?)
Good point. At the moment, only US and Canada, from the country table
are being used, but I suppose in an ideal world, the list of service
providers could expand...
> 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 %}
Me likey. Thanks. (At some point I tried going from country to state
to provider as you've just shown, but bolloxed that up too.)
>
> --
> 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
-~----------~----~----~----~------~----~------~--~---
