Group_by and filter Django

Question

I'm trying to make and query in Django,But I can't get the output I want. I want to use group by and filter in Django Query, I tried using annotate by looking at some answers on stackoverflow and some other sites but couldn't make it work . Here's my response on after using filter.

[
{
"id": 11667,
"rate_id": "FIT-PIT2",
"name": "FIT-PIT111",
"pms_room": null,
"description": null,
"checkin": "",
"checkout": "",
"connected_room": null
},
{
"id": 11698,
"rate_id": "343",
"name": "dfggffd",
"pms_room": "5BZ",
"description": null,
"checkin": null,
"checkout": null,
"connected_room": null
},
{
"id": 11699,
"rate_id": "343",
"name": "dfggffd",
"pms_room": "6BZ",
"description": null,
"checkin": null,
"checkout": null,
"connected_room": null
}]

What I want to do is group all those pms_rooms which have same rate_id, roughly something like this {'343':['5BZ','6BZ'],'FIT-PIT2':[null]} I can do it using dictionary or list .

But I want to do it directly from query like table.objects.filter(condition).group_by('rate_id') , something SQL equivalent of SELECT *,GROUP_CONCAT('name') FROM TABLE NAME WHERE PMS = hotel.pms GROUP BY rate_id . Can somebody please help me out . Thanks.

check this answer: https://stackoverflow.com/questions/10340684/group-concat-equivalent-in-django — Nalin Dobhal, Jan 24 '20 at 05:38

score 0 · Answer 1 · answered Jan 24 '20 at 05:59

0

table.objects.filter(condition).values('rate_id'), check out the doc https://docs.djangoproject.com/en/3.0/ref/models/querysets/

answered Jan 24 '20 at 05:59

bmons

3,352
2
10
18

score 0 · Answer 2 · answered Jan 24 '20 at 10:22

Since your example have mentioned GROUP_CONCAT, I'll assume that you are using MySQL. Django did not support GROUP_CONCAT natively, yet you can try django-MySQL, which is supporting an equivalent database function GroupConcat. Then you can make a query like this:

table.objects.values('rate_id').annotate(grouped_rooms=GroupConcat('pms_room'))

The result may be like this:

[
    {
        'rate_id': '343',
        'grouped_rooms': '5BZ,6BZ',
    },
    {
        'rate_id': 'FIT-PIT2',
        'grouped_rooms': '',
    },
    ...
]

Not actually meet the format you mentioned in OP, yet you may do some post process to this result in native python for making it meet what you expected.

Group_by and filter Django

2 Answers2