CODEWITHSUNDEEP
pythondjangodjango-models
javaCity
javaCity

Reputation: 4318

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())

Upvotes: 0

Views: 51

Answers (1)

Tiny Instance
Tiny Instance

Reputation: 2671

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.

Upvotes: 1

Related Questions