Trouble with Query

Hey Ken, looking for a life raft …

I think you know my project well enough that I can share this query. The results are great, yet when I try to apply a sort, things break, and I don’t understand why. I think it might be best to start the query with the Country object, yet I’m not sure. In case it’s not clear, I need to retrieve all collections with a specific country id, then sort them by collector name.

def location_country_collections(request, pk):
    collectors_country = Collection.objects.filter(country_id=pk).select_related(
        'country').prefetch_related('collector').distinct()
    template = 'collections_app/browse_collections_location_country.html'
    context = {'collectors_country': collectors_country}

    return render(request, template, context)

Thanks as always.

So is this the query that is working or the one that isn’t?

Is pk in this case the pk of the Country object that you want to find all the Collections for?

Just to confirm, is country the name of the foreign key to the Country object?

If that’s the case, your filter should work as (country=pk), or, if you prefer being more explicit, country__id=pk. (Notice the double-underscore to perform the foreign key relationship transversal)

What sort are you trying to apply?

This is the query that is working, yet when I apply a sort it blows up. Yes, the PK is the country id I’m using. I just took Distinct off, and it does not change anything. I want to sort by ‘collector__sort_name.’

When I apply the sort …

def location_country_collections(request, pk):
    collectors_country = Collection.objects.filter(country_id=pk).select_related(
        'country').prefetch_related('collector').order_by('collector__sort_name')
    template = 'collections_app/browse_collections_location_country.html'
    context = {'collectors_country': collectors_country}

I get duplicates on the collections that have multiple collectors.

Sam

Unfortunately, that all makes sense based upon your data structures. What you’re trying to do can’t be done the way you’re trying to express it.

Since a Collection may be related to multiple Collectors, it doesn’t make sense to sort Collection by Collector. The results would be ambiguous.

I tried to address this topic in an earlier reply and did a really poor job of it.

Assume that you have Collection A related to Collectors “W” and “Y”, and Collection B related to Collectors “X” and “Z”.

If you’re only going to include one instance each of Collection A and B, but try to sort by Collectors, either order is valid, depending upon which Collector you use for the sort.

Does it make sense to construct the query starting with Collector? If so, are you able to get me going? Is there another solution?

I’m sorry, I don’t think I can be any more clear.

What you are asking to do just doesn’t make sense. When you have a many-to-many relationship, there is no query that makes sense to sort table “A” through the many-to-many to any element on table “B”, when you only want to retrieve a single instance of each element of table “A”.

OK, take a look at this query …

def location_city_collections(request, pk):
    city = City.objects.get(id=pk)
    collectors_city = Collector.objects.prefetch_related('collections').filter(
        collections__city_id=pk).order_by('sort_name', 'inst_sub_name', 'inst_sub2_name').distinct()
    template = 'collections_app/browse_collections_location_city.html'
    context = {'city': city,
               'collectors_city': collectors_city}

    return render(request, template, context)

Why does this search that is supposed to find collections in Fort Worth, Texas also retrieve collections in Canada and Mexico? Also related to the issue you’re describing? Seems so counterintuitive when you’re sending a specific city ID.

Yes it is related in principle.

Your query is looking for Collector objects that are related to any Collection having a city ID = pk. But then you’re getting all the Collection associated with that Collector.

If you want to only get the Collector objects related to Collection in a particular city, perform your query on the Collection (by city id), then iterate over the Collector objects related to it.