javaCity javaCity - 1 day ago 4
Python Question

Grouping rows with condition

I have the following models:

class Datacomponent(models.Model):
id = models.IntegerField(db_column='ID', primary_key=True) # Field name made lowercase.
composition = models.ForeignKey(Composition, models.DO_NOTHING, db_column='Composition_ID', null=True, blank=True) # Field name made lowercase.
components = models.ForeignKey(Components, models.DO_NOTHING, db_column='Components_ID') # Field name made lowercase.
componentvalue = models.FloatField(db_column='ComponentValue') # Field name made lowercase.



class Components(models.Model):
id = models.IntegerField(db_column='ID', primary_key=True) # Field name made lowercase.
name = models.CharField(db_column='Name', max_length=45, blank=True, null=True) # Field name made lowercase.


In
Datacomponent
there are multiple rows of same composition (which is foreign key to
Composition
table).

My end goal is to get set of
Composition
if each of its component values are in range of some values. In my approach shown below I do not get any composition_ids at all.

CompositionID ComponentID ComponentValue
1 1 0.5
1 2 0.3
2 1 0.6
2 2 0.4
3 1 0.0
3 2 0.1


So the query for above table would be: 'Get all composition ids with componentid=1 and componentvalue__range=(minn[a], maxx[b]). If we want comp value gte 0.5 for componentID 1 and gte 0.3 for componentID 2 our result should be just 2 because 2's component 1 is 0.6 and component2's value is 0.4.

Here's my approach which is not working:

queries = [Q(componentvalue__range = (minn[v], maxx[v]),
components__name = "'"+v+"'",
) for v in minn]

query = queries.pop()
for item in queries:
query |= item

composition_ids = Datacomponent.objects.filter(query).values_list('composition_id', flat=True)

print (composition_ids.count())

Answer

If I understood you correctly, then something like this may help

Edit after your comment. (I hope I understand you better this time). You're actually after Composition objects that have both comp1 and comp2 in within specified ranges(?) Then you can actually go about selecting them from Composition model.

First add a related_name to your composition field in Datacomponent model, something like "datacomp", for reverse queries.

from django.db.models import Q

class Datacomponent(models.Model):
    composition = models.ForeignKey(Composition, related_name='datacomp', ..) 
    ...

comp_1_query = Q(datacomp__components_id=YOUR_COMP1_ID, datacomp__componentvalue_range=(YOUR_RANGE1_MIN, YOUR_RANGE1_MAX)
comp_2_query = Q(datacomp__components_id=YOUR_COMP2_ID, datacomp__componentvalue_range=(YOUR_RANGE2_MIN, YOUR_RANGE2_MAX)


Composition.objects\
           .select_related('datacomp')\ # you can for optimization  
           .filter(comp_1_query, comp_2_query)\ # Simply 'and' since you want both to match
           .values_list('id', 'datacomp__components_id', 'datacomp__componentvalue', flat=True)

This should give you the compositions with component1 and component2 within specified ranges.

Comments