0
votes

I've got next issue.

Having next tables:

products_table:

id product_type serial_number
1 1 FX2002
2 1 FX2003
3 2 FX2004
4 2 FX2005
5 2 FX2006

product_types_table:

id element
1 laptop
2 mouse
3 screen

In products_table, product_type is a foreign key to product_types_table.

I need to execute next query:

SELECT product_type, count(product_type) AS quantity FROM products_table

And get:

product_type quantity
1 2
2 3

I've tried:

queryset = products_table.objects.all().values("product_type").annotate(quantity=Count("product_type"))

What returns next queryset:

[{'product_type':1, 'quantity':2}, {'product_type':2, 'quantity':3}]

I need 'product_type' field to be an object instead of integer, so element field can be called as:

element = queryset[0]['product_type'].element

Getting 'laptop' if I print element variable.

I need to convert this data into JSON as well, and I need next response:

[
    {
        id: 1,
        product_type: {
            id: 1,
            element: 'laptop'
        },
        serial_number: 'FX2002'
    },
    {
        id: 2,
        product_type: {
            id: 1,
            element: 'laptop'
        },
        serial_number: 'FX2003'
    },
    {
        id: 3,
        product_type: {
            id: 2,
            element: 'mouse'
        },
        serial_number: 'FX2004'
    },
    {
        id: 4,
        product_type: {
            id: 2,
            element: 'mouse'
        },
        serial_number: 'FX2005'
    },
    {
        id: 5,
        product_type: {
            id: 2,
            element: 'mouse'
        },
        serial_number: 'FX2006'
    }
]

How can I do it???

Thank all of you for your time.