CODEWITHSUNDEEP
pythonmysqlsqldjangopython-2.7
Marvin
Marvin

Reputation: 243

How to group rows containing similar element from a table using MYSQL/ Python / Django?

I have a table being rendered in my HMTL page. The data comes from a MYSQL query and being rendered using a loop. Let’s say I have this table:

enter image description here

I'm able to get these data by models -> views - > html

views:

def context(request):
    context = {
        "contents": Something.objects.get_contents()
    }
    return render(request, 'contents.html', context)

Notice that the only repeating priority is a type of ‘news.’ I am expecting 4 news to show up and they can share similar priorities. Any other genre should have their own priority. How can I write a query and render them to an html page to have an output like this:

enter image description here

I appreciate all the help! Thank you.

Upvotes: 0

Views: 117

Answers (2)

Lie Ryan
Lie Ryan

Reputation: 64923

You can use GROUP BY and GROUP_CONCAT, something like this:

SELECT priority, type, GROUP_CONCAT(content SEPARATOR ', ') FROM table GROUP BY priority, type;

Reference: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Upvotes: 1

Alex
Alex

Reputation: 11

I don't think there's an easy way to do that with a SQL query, try doing the collection in Python. Make a defaultdict which lets you collect your contents field into a list:

from collections import defaultdict
contents_dict = defaultdict(list)
for priority, type, content in Something.objects.get_contents():
    contents_dict[(priority, type)].append(content)

Then rebuild into a list of tuples

table = [key + (','.join(s),) for key, s in contents_dict.items()]

then your view looks like:

def context(request):
context = {
    "contents": table
}
return render(request, 'contents.html', context)

Upvotes: 1

Related Questions