Is it possible to annotate/count against a prefetched query?
My initial query below, is based on circuits, then I realised that if a site does not have any circuits I won't have a 'None' Category which would show a site as Down.
conn_data = Circuits.objects.all() \ .values('circuit_type__circuit_type') \ .exclude(active_link=False) \ .annotate(total=Count('circuit_type__circuit_type')) \ .order_by('circuit_type__monitor_priority') So I changed to querying sites and using prefetch, which now has an empty circuits_set for any site that does not have an active link. Is there a Django way of creating the new totals against that circuits_set within conn_data? I was going to loop through all the sites manually and add the totals that way but wanted to know if there was a way to do this within the QuerySet instead?
my end result should have a something like:
[ {'circuit_type__circuit_type': 'Fibre', 'total': 63}, {'circuit_type__circuit_type': 'DSL', 'total': 29}, {'circuit_type__circuit_type': 'None', 'total': 2} ] prefetch query:
conn_data = SiteData.objects.prefetch_related( Prefetch( 'circuits_set', queryset=Circuits.objects.exclude(active_link=False).select_related('circuit_type'), ) ) 2 Answers
Answers 1
I don't think this will work. Its debatable whether it should work. Let's refer to what prefetch_related does.
Returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.
So what happens here is that two queries are dispatched and two lists are realized. These lists are then partitioned in memory and grouped to the correct parent records.
Count() and annotate() are directives to the DBMS that resolve to SQL
Select Count(id) from conn_data Because of the way annotate and prefetch_related work I think its unlikely they will play nice together. prefetch_related is just a convenience though. From a practical perspective running two separate ORM queries and assigning them to SiteData records yourself is effectively the same thing. So something like ...
#Gets all Circuits counted and grouped by SiteData Circuits.objects.values('sitedata_id)'.exclude(active_link=False).select_related('circuit_type').annotate(Count('site_data_id')); Then you just loop over your SiteData records and assign the counts.
Answers 2
Ok I got what I wanted with this, probably a better way of doing it but it works never the less:
from collections import Counter import operator class ConnData(object): def __init__(self, priority='', c_type='', count=0 ): self.priority = priority self.c_type = c_type self.count = count def __repr__(self): return '{} {}'.format(self.__class__.__name__, self.c_type) # get all the site data conn_data = SiteData.objects.exclude(Q(site_type__site_type='Data Centre') | Q(site_type__site_type='Factory')) \ .prefetch_related( Prefetch( 'circuits_set', queryset=Circuits.objects.exclude(active_link=False).select_related('circuit_type'), ) ) # create a list for the conns conns = [] # add items to list of dictionaries with all required fields for conn in conn_data: try: conn_type = conn.circuits_set.all()[0].circuit_type.circuit_type prioritiy = conn.circuits_set.all()[0].circuit_type.monitor_priority conns.append({'circuit_type' : conn_type, 'priority' : prioritiy}) except: # create category for down sites conns.append({'circuit_type' : 'Down', 'priority' : 10}) # crate new list for class data conn_counts = [] # create counter data conn_count_data = Counter(((d['circuit_type'], d['priority']) for d in conns)) # loop through counter data and add classes to list for val, count in conn_count_data.items(): cc = ConnData() cc.priority = val[1] cc.c_type = val[0] cc.count = count conn_counts.append(cc) # sort the classes by priority conn_counts = sorted(conn_counts, key=operator.attrgetter('priority')) 
0 comments:
Post a Comment