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?

Answer 1

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)

Answer 2

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

Answer 3

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