Django: Which days have objects?

Question

I'm currently creating a simple calendar for one of my Django projects. The calendar will display the current month and the days. Any day which has a item for that day, will be highlighted in red so that the user knows that there are items for that day. The number of items or what items they are don't matter. All we care about is whether a day has items.

Lets say I have the following model.

class Items(models.Model):
    name = models.CharField(max_length=140)
    datetime = models.DateTimeField(auto_now_add=False)

    def save(self, *args, **kwargs):
        if datetim is None:
            created = datetime.now()
        super(Items, self).save()

Here is my current logic for finding which days have items:

from calendar import monthrange

# Find number of days for June 2015

num_days = monthrange(2015, 6)[1]
days_with_items = []

'''
Increase num_days by 1 so that the last day of the month 
is included in the range
'''
num_days =+ 1

for day in range(0, num_days):
    has_items = Items.objects.filter(datetime__day = day,
                                     datetime__month = 6,
                                     datetime__year = 2015).exists()
    if has_items:
        days_with_items.append(day)
return days_with_items

This returns me a list with all the days that have items. This works however I'm looking for a more efficient way of doing this since Django is making multiple trips to the DB for the .exists()

Any suggestions?

score 1 · Accepted Answer · edited May 23 '17 at 11:51

I see two possible options. The first one is to add counts at DB level, the second is to have an efficient loop over available data at python level. Depending on data size and db-efficiency you can choose which suits you best.

Counting in the database is explained here: Django ORM, group by day

Or solution two, a simple script (not so elegant.. but just as an example):

days_with_items_hash = {}
items = Items.objects.filter(
   datetime__month = 6, 
   datetime__year = 2015
)
for item in items:
    days_with_item_hash[item.datetime.day] = True

days_with_item = days_with_item_hash.keys()

I would stick with the database solution because it can be optimised (sql views, extra column with just the day, etc)

Thank you Wouter. Currently looking at the first suggestion you mentioned. Counting at the DB level is what I had originally thought of. Having the DB do all the heavy lifting is probably the way to go. — deadlock, Jun 09 '15 at 21:06

sobolevn · Answer 2 · 2015-06-09T21:07:55.927

At first, let's get all the items for the required month.

items = Items.objects.filter(datetime__month=6, datetime__year=2015)
days = set([item.datetime.day for item in items]) # unique days

If you want to make a partial query, specify values you need, here's the concept:

    items = Item.objects.filter(
        date_added__month=6, date_added__year=2015
    ).values('date_added')
    days = set([item['date_added'].day for item in items])

It will result in the following SQL query:

QUERY = u'SELECT "main_item"."date_added" FROM "main_item" WHERE
(django_datetime_extract(\'month\', "main_item"."date_added", %s) = %s 
AND "main_item"."date_added" BETWEEN %s AND %s)' 
- PARAMS = (u"'UTC'", u'6', u'datetime.datetime(2015, 1, 1, 0, 0, tzinfo=<UTC>)', 
            u'datetime.datetime(2015, 12, 31, 23, 59, 59, 999999, tzinfo=<UTC>)')

If you are dealing with big amout of Items, you can break your query into parts (< 15 and >=15 for example). This will result in extra database hit, but the memory usage pick will be smaller. You can also consider different methods.

Please, also note:

that datetime is not the best name for a field. Name it meaningfully, like: "date_added", "date_created" or something like that
if self.datetime is None is 'almost' equal to if not self.datetime

thank you for the quick reply. I like your solution. This allows for one trip to the DB. But what if the number of items is huge? For the sake of it, lets say there happens to be 50,000 items for that month. All of that has to be stored in memory for the duration of the set operation. I'm not sure how that will affect things but this is definitely something I could try. — deadlock, Jun 09 '15 at 20:58

score 0 · Answer 3 · answered Jun 09 '15 at 21:53

0

Use the dates method.

items = Item.objects.filter(date_added__month=6, date_added__year=2015)
dates = items.dates('date_added', 'day') # returns a queryset of datetimes
days = [d.day for d in dates] # convert to a list of days

answered Jun 09 '15 at 21:53

Alasdair

298,606
55
578
516

Django: Which days have objects?

3 Answers3