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.