如何使用字符串过滤Django中的DateField或DateTimeField? [英] How can I filter a DateField or a DateTimeField in Django using a string?

问题描述

我在前端使用 DataTables ，它允许进行全局搜索(遍历所有字段查找匹配项).

I am using DataTables for my front end and it allows a global search (iterating through all the fields to find matches).

这就是我想要在Django(1.9.1版)中可以做的事情:

This is what I want to be able to do in django (version 1.9.1):

• 使用"ma"作为DateField的过滤器，以便所有三月和五月日期返回

或

• 使用"3月1日"作为DateField的过滤器，以便所有日期之间3月10日至3月19日返回

经过一番修补，我有了这个自定义查询

After some tinkering, I have this custom lookup

from django.db.models import Lookup
from django.db.models.fields import DateField

@DateField.register_lookup
class DateTextFilter(Lookup):
    lookup_name = 'dttxt'
    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs = self.rhs.strftime("%b %d, %Y")
        return "to_char(%s,'Mon DD, YYYY') ~* '%s'", ([lhs,rhs])

我这样运行:

outputQ = Q(**{"appt_date"+"__dttxt" : "Mar 09"})
Appts.objects.filter(outputQ)

它产生运行的SQL(在Postgres中)，但是当我运行filter()时，出现错误

It produces SQL that runs (in Postgres) but when I run filter(), I get the error

ValidationError: [u"'Mar 09' value has an invalid date format. 
It must be in YYYY-MM-DD format."]

足够公平，"3月9日"不是有效日期-但原始SQL有效且可以运行(我用"2016-03-09"进行了测试，以绕过此错误并让django创建SQL)

Fair enough, "Mar 09" is not a valid date – but the raw SQL is valid and runs ( I tested this with '2016-03-09' to bypass this error and get django to create the SQL)

SELECT "appts"."appt_id", "appts"."client_id", "appts"."therapist_id",
 "appts"."agency_id", "appts"."appt_date", "appts"."appt_start", 
"appts"."appt_end", "appts"."cpt_code", "appts"."client_owe", 
"appts"."insur1_owe", "appts"."insur2_owe", "appts"."note", 
"appts"."note_flag", "appts"."ct_fee_pd", "appts"."subm_date", 
"appts"."updated" FROM "appts" WHERE to_char("appts"."appt_date",'Mon DD, 
YYYY') ~* 'Mar 09'

我可以使用 extra()，但这已被弃用.

I could use extra() but this will be deprecated.

我可能会使用

I am probably going to wind up parsing the string using this SO algorithm (using raw sql is too large of a compromise just to gain this functionality), but I will lose some of the functionality that I want .

不确定这是否可行...

Not sure if this is doable...

推荐答案

解决方案1 ​​

创建一个构建Q对象的方法来测试零件中的日期:

Create a method that builds Q objects to test the date in parts:

def _filter_by_date_field(self, fld_value, searchableColumn, outputQ):
    mon_val = -1
    Mons = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec']
    if len(fld_value) >= 3 and fld_value[0:3].lower() in Mons:
        mon_val = next(i for i,v in enumerate(Mons) \
                    if v == fld_value[0:3].lower())+1
        outputQ |= Q(**{searchableColumn+"__month" : mon_val})
    if len(fld_value) >= 6 and mon_val > -1 and \
              re.search(r'^\d{2}$', fld_value[4:6]):
        outputQ &= Q(**{searchableColumn+"__day" : fld_value[4:6]})
    if len(fld_value) >= 8 and mon_val > -1 and \
              re.search(r'^\d{4}$', fld_value[8:12]):
        outputQ &= Q(**{searchableColumn+"__year" : fld_value[8:12]})
    return outputQ

如果该字段是DateField，则该方法的调用方式如下:

If the field is a DateField, the method is called like this:

 if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
        outputQ |= self._filter_by_date_field(customSearch,\
                                              searchableColumn,outputQ)

下面是一个完整日期的SQL Django构建示例:

Here is an example of the SQL Django builds for a complete date:

EXTRACT('month' FROM "get_appts_vw"."appt_date") = 3) AND
   EXTRACT('day' FROM "get_appts_vw"."appt_date") = 5 AND 
   "get_appts_vw"."appt_date" BETWEEN '2016-01-01'::date AND '2016-12-31'::date)

解决方案2

使用CharField为模型创建数据库视图(原始模型使用DateField)

create a database view for models using CharField (where the original model used a DateField)

class ApptView(models.Model):
    appt_date = models.CharField(max_length=12)
            ….
class Meta:
    db_table = u'get_appts_vw' # view name

视图

   CREATE VIEW get_appts_vw AS
    select to_char(a.appt_date,'Mon DD, YYYY') as appt_date
    ….
    from appts

这可行，但仍然需要额外的()子句进行排序

This works but still requires an extra() clause to do sorts

解决方案3

(我正在使用它)

from django.db.models import Lookup
from django.db.models.fields import DateField
from django.db.models import CharField, Transform

@DateField.register_lookup
class TextDate(Transform):
    lookup_name = 'txtdt'

    @property
    def output_field(self):
        return CharField()


@TextDate.register_lookup
class DateTextFilter(Lookup):
    lookup_name = 'dttxt'

    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = compiler.compile(self.lhs.lhs)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return 'to_char(%s,\'Mon DD, YYYY\') ~* %s ' % (lhs, rhs), params

这样叫

from .CustomFilters import * # or whatever you name your custom filters module
....
    if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
            outputQ |= Q(**{<column_name>+"__txtdt__dttxt" : <search_value>})

这篇关于如何使用字符串过滤Django中的DateField或DateTimeField?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！