Queryset explanations

Using Django Using the ORM
1

Im a bit confused on how to carry on with my project, some guidance required :grin:

Inventory model have Equipment, TechFloat
I would like to get a query result on how many and what type of equipments checked out today, this week, this month…

class TechFloat(BaseModel):
    emp = models.ForeignKey(Employee, related_name='float_owner', on_delete=models.DO_NOTHING)
    equ = models.OneToOneField(Equipment, related_name='float_equipment', on_delete=models.DO_NOTHING)
    installed = models.BooleanField(_("Installed"), default=False)

class Equipment(BaseModel):
    class Stat(models.IntegerChoices):
        LOST = 0, "Missing"
        WAREHOUSE = 1, "Warehouse"
        TECHFLOAT = 2, "Tech"
        INSTALLED = 3, "Installed"
        RETURN = 4, "Return"

    serial = models.CharField(max_length=128, unique=True)
    stat = models.PositiveSmallIntegerField(choices=Stat.choices, default=Stat.WAREHOUSE)
    cpe = models.ForeignKey(cpeEquipment, related_name='cpeEquipment', on_delete=models.CASCADE)

currently I can get some count but nowhere what Im looking for and out of ideas how to proceed with it

def get_queryset(self):
        today_start = timezone.now().replace(hour=0, minute=0, second=0)
        today_end = timezone.now().replace(hour=23, minute=59, second=59)
        self.cpe_result = TechFloat.objects.filter(created_at__gte=today_start, created_at__lte=today_end) \
            .values('equ__cpe') \
            .annotate(count=(Count('equ__cpe'))) \
            .values('equ__cpe')

        return self.cpe_result
<QuerySet [{'equ__cpe': 14}, {'equ__cpe': 3}, {'equ__cpe': 9}, {'equ__cpe': 7}, {'equ__cpe': 1}, {'equ__cpe': 5}, {'equ__cpe': 2}, {'equ__cpe': 15}, {'equ__cpe': 11}, {'equ__cpe': 8}]>

What I would like to have as output is the following.

<QuerySet [{'emp':132, {'equ__cpe': 14}, {'equ__cpe': 3}, {'equ__cpe': 9}}, {'emp':205, {'equ__cpe': 7}, {'equ__cpe': 1}, {'equ__cpe': 5}}, {'emp': 27, {'equ__cpe': 2}, {'equ__cpe': 15}, {'equ__cpe': 11}, {'equ__cpe': 8}}]>

The table I like to have as output:

John Doe: XU6 14 piece, XIONE 3 piece, CGM_2250 9 piece
Jim Prentis: XU6 7 piece, XIONE 1 piece, ART20 5 piece
Jeff Gogan: XU6 2 piece, XIONE 15 piece, ART20 11 piece, PID10 8 piece
2

I would recommend manipulating the data structure in your view into the shape you’d like. The {'emp': 132} dict doesn’t make sense to me from your example, so without more information I’d suggest looking that up or calculating it manually.

Additionally, your ideal output has a syntax error. {'emp':132, {'equ__cpe': 14}, ...} is not a valid dictionary.

3

Emp 132 would be the employee pk and not a counted/calculated number

4

You’d need to include emp in the values call.

You might be able to do what you want with a ArraySubquery expression using JSONObject. Check this part of the docs out: PostgreSQL specific query expressions | Django documentation | Django.

The outer query would have the emp_id, then you’d have some field that would have a JSON list of objects containing the aggregates.

5

I’ll add that it makes a difference as to how you’re going to use the output from these queries.

If you’re creating a JSON feed for some API, then I can see it being worthwhile to try and build these objects directly.

However, if you’ve building a “traditional” page using Django templates, then any work to “preformat” this is wasted effort. It doesn’t matter what the data structure is that is being returned from the query, only that it’s providing you with the data that you will need to render your template.

6

I got this far with the Query below, now just somehow need to group and count the same type of equipments.
And yes planning to use Django template to render simple table.

TechFloat.objects.filter(emp=emp['emp']).annotate(count=Count('equ')).values('emp','count','equ__cpe__name')
<QuerySet [
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1},
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 1}
]
7

If you’re filtering down to a specific emp value, why do you need that in the queryset results? You can include that value in the template context and access it as necessary.

8

yes emp value isn’t necessary, still how would i group and count it

{'equ__cpe__name': 'DCX3200', 'count': 6},
{'equ__cpe__name': 'XIONE', 'count': 8},
{'equ__cpe__name': 'XB7', 'count': 6},
9

I think I got it :grin:

TechFloat.objects.filter(emp=emp['emp'])
.values('equ__cpe')
.distinct()
.annotate(count=Count('equ'))
.values('emp','count','equ__cpe__name')

[
{'emp': 105, 'equ__cpe__name': 'XIONE', 'count': 16}, 
{'emp': 105, 'equ__cpe__name': 'DCX3200', 'count': 6}, 
{'emp': 105, 'equ__cpe__name': 'CGNM-2250', 'count': 5}, 
{'emp': 105, 'equ__cpe__name': 'XG1V4', 'count': 1}, 
{'emp': 105, 'equ__cpe__name': 'XB6', 'count': 6}, 
{'emp': 105, 'equ__cpe__name': 'STRMTV', 'count': 4}, 
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 12}
]
10

Quick if you know it :grin: how would I return empty rows ? Like there is a cpe equipment what have 0 transaction how would I get that ?

{'emp': 105, 'equ__cpe__name': 'XB6', 'count': 0}, 
{'emp': 105, 'equ__cpe__name': 'STRMTV', 'count': 0}, 
{'emp': 105, 'equ__cpe__name': 'XB7', 'count': 12}
11

You can’t with your queryset. The equ__cpe__name is predicated on the fact that there is at least one equ relation. If the count is zero, that means there is no equ relation, which means you don’t have a record to fetch from for equ__cpe__name, etc.