EricMattison@Harvard

Here at RCS, our main Django app (Spinal) uses key=value pairs to generalize our database. Which is great, because you can shove anything in there. However, it makes Django queries difficult. For example, if you want to get all sessions after a certain date, you might have to do something like this:


str_month_start = str(start_datetime)
rulsessions = ResourceUsageLogSession.objects.filter(resourceusagelogsessionkvpair__key='start_datetime', resourceusagelogsessionkvpair__val__gte=str_month_start)

(Aside: Remember, since everything is in string form, you can’t compare dates, you need to first convert dates into Y-m-d format and then compare items alpha-numerically.)

However, if you want to get the all of the dates before a certain date, you can’t use .exclude()

This won't work!
str_month_start = str(start_datetime)
rulsessions = ResourceUsageLogSession.objects.exclude(resourceusagelogsessionkvpair__key='start_datetime', resourceusagelogsessionkvpair__val__gte=str_month_start)


The above code will exclude on both lookup fields, not on their intersection. Instead, you have to filter on the logical inverse of the first statement:

str_month_start = str(start_datetime)
rulsessions = ResourceUsageLogSession.objects.filter(resourceusagelogsessionkvpair__key='start_datetime', resourceusagelogsessionkvpair__val__lt=str_month_start)


What’s the lesson here? Databases (and web frameworks built on top of them) are best when used with standardized fields, and generic models may lead to some tough coding.