Django query to group records with created field

By: summonagus ● at Feb. 9, 2017, 11:53 p.m. ● and modified at Feb. 9, 2017, 11:53 p.m.
Posted under: #Django, #Solution, #Problem ● Your ip address: 23.20.129.162 ● views: 94 times.

AD Space Available, Book Now!

Hello guys, this night I want to share a simply tutorial how to querying the objects to group records with created field. This case is what already aksed by someone at stackoverflow.

And the question is;

in the models.py

class Post(models.Model):
    caption = models.CharField(max_lenght=200)
    description = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

I am looking for a single Django query that will return results like:

data = {
    "YYYY-MM-DD": [{<post data one>}, {<post data two>}],
    "YYYY-MM-DD": [{<post data one>}, {<post data two>}],
    "YYYY-MM-DD": [{<post data one>}, {<post data two>}],
    ....
}

"YYYY-MM-DD" is the date value of created field in Post model.


And hereis my answer;

I think you can handle it with groupby from python module.

I'll give you an example;

>>> rows = Post.objects.published().values_list('id', 'created')
<QuerySet [
  (100, datetime.datetime(2017, 2, 9, 14, 35, 9, 358, tzinfo=<UTC>)), 
  (99, datetime.datetime(2017, 2, 9, 14, 35, 0, 760347, tzinfo=<UTC>)), 
  (98, datetime.datetime(2017, 1, 22, 3, 32, 18, 570201, tzinfo=<UTC>)), 
  (97, datetime.datetime(2017, 1, 3, 0, 44, 35, 277663, tzinfo=<UTC>)), 
  (96, datetime.datetime(2017, 1, 1, 14, 18, 30, 920143, tzinfo=<UTC>)),
  (95, datetime.datetime(2016, 12, 24, 3, 31, 2, 370658, tzinfo=<UTC>)),
  '...(remaining elements truncated)...']>
>>>
>>> from itertools import groupby
>>> items = []
>>> for k, v in groupby(rows, key=lambda row: row[1].strftime('%Y-%m-%d')):
...     data = dict({k: list(dict(v).keys()) })
...     items.append(data)
...     print(data)
... 
{'2017-02-09': [99, 100]}
{'2017-01-22': [98]}
{'2017-01-03': [97]}
{'2016-10-09': [81]}
{'2016-10-07': [80, 79]}
{'2016-10-05': [78]}
{'2016-10-04': [77]}
{'2016-10-02': [1, 2, 3, 4, 5, 6, 7]}
>>> items
[
  {'2017-02-09': [99, 100]}
  {'2017-01-22': [98]}
  {'2017-01-03': [97]}
  {'2016-10-09': [81]}
  {'2016-10-07': [80, 79]}
  {'2016-10-05': [78]}
  {'2016-10-04': [77]}
  {'2016-10-02': [1, 2, 3, 4, 5, 6, 7]}
]

My previous test, the values is id/pk from the objects. you also can create a function to get title, slug or else.. an example:

>>> def get_titles(ids):
...     final_titles = []
...     for id in ids:
...         final_titles.append(Post.objects.get(id=id).title)
...     return final_titles
>>>
>>> items = []
>>> for k, v in groupby(rows, key=lambda row: row[1].strftime('%Y-%m-%d')):
...     titles = get_titles(list(dict(v).keys()))
...     items.append(dict({k: titles}))
>>> items
[
  {'2017-02-09': ['Test Post 1', 'Test Post 2']}, 
  {'2017-01-22': ['Fixed DNS PROBE on Ubuntu']}, 
  {'2017-01-03': ['Django: Custom safe excludes from dangerous XSS Injection']}, 
  {'2017-01-01': ['DracEditor - Django Markdown Editor built for Dracos Linux']}, 
  {'2016-12-24': ['Top 10 Python libraries of 2016']}, 
  {'2016-12-19': ['How to custom html choose image upload for django markdownx']}, 
  {'2016-12-18': ['Command to handle deploy Django with quickly']}
  ....
]
By default, if you try with final_titles.append(Post.objects.get(id=id)) should return the string from function of def __unicode__(self): or def __str__(self): inside your models.Post

And finally, simply way to get meta data from the objects.

def get_data(ids):
    final_data = []
    for id in ids:
        post = Post.objects.get(id=id)
        final_data.append({'title': post.title, 'created': post.created, 'slug': post.slug})
    return final_data

items = []
rows = Post.objects.published().values_list('id', 'created')

for k, v in groupby(rows, key=lambda row: row[1].strftime('%Y-%m-%d')):
    data = get_data(list(dict(v).keys()))
    items.append(dict({k: data}))

Result of items;

[
  {
    '2017-02-09': [
      {
        'created': datetime.datetime(2017, 2, 9, 3, 32, 18, 570201, tzinfo=<UTC>),
        'title': 'Test Post 1',
        'slug': 'test-post-1'
      },
      {
        'created': datetime.datetime(2017, 2, 9, 2, 21, 9, 570201, tzinfo=<UTC>),
        'title': 'Test Post 2',
        'slug': 'test-post-2'
      }
    ]
  }, 
  {
    '2017-01-22': [
      {
        'created': datetime.datetime(2017, 1, 22, 3, 32, 18, 570201, tzinfo=<UTC>), 
        'title': 'Fixed DNS PROBE on Ubuntu', 
        'slug': 'fixed-dns-probe-on-ubuntu'
      }
    ]
  }, 
  {
    '2017-01-03': [
      {
        'created': datetime.datetime(2017, 1, 3, 0, 44, 35, 277663, tzinfo=<UTC>), 
        'title': 'Django: Custom safe excludes from dangerous XSS Injection', 
        'slug': 'django-custom-safe-excludes-from-dangerous-xss-injection'
      }
    ]
  }, ....
]


Author

summonagus

Python Developer, Linux Enthusiast, and Senior writer at python.web.id
Website: https://github.com/agusmakmun
blog comments powered by Disqus