custom filter in grid

[Carlos Cesar Caballero Díaz]

Hi guys, I have a datetime field but I need (using the grid) filter it
as a date field, so, if I put something like:

mydatetime = 2017-04-04

I need to show all records with datetimes that belongs to this day.

Greetings.

[黄祥]

in grid search form field you can use :

table.table_datetime >= "2017-04-04 00:00:00" and table.table_datetime <= "2017-04-04 24:00:00"

table.table_datetime >= "2017-04-04 00:00" and table.table_datetime <= "2017-04-04 24:00"

table.table_datetime >= "2017-04-04 00" and table.table_datetime <= "2017-04-04 24"

or

table.table_datetime >= "2017-04-04 00:00:00" and table.table_datetime < "2017-04-05 00:00:00"

table.table_datetime >= "2017-04-04 00:00" and table.table_datetime < "2017-04-05 00:00"

table.table_datetime >= "2017-04-04 00" and table.table_datetime < "2017-04-05 00"

best regards,

stifan

[Carlos Cesar Caballero Díaz]

Sitfan, thanks for your answer, I know about that, but is annoying for the users to put datetime values twice and they are asking about use only the day, they don't need to filter in that grid by date and time, just by date.

Greetings.

El 10/08/17 a las 20:15, 黄祥 escribió:

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit MailScanner ha detectado un intento de fraude en la siguiente página web "groups.google.com". No confíe en esta página web: MailScanner ha detectado un intento de fraude en la siguiente p�gina web "groups.google.com". No conf�e en esta p�gina web: https://groups.google.com/d/optout.

[黄祥]

just an idea why not create the new field with the date type with default value = request.now? so that user can search through the new date field

best regards,

stifan

[Carlos Cesar Caballero Díaz]

Yes, that's a possible solution, but I don't want to duplicate information that is already there, I just want to find a way to use it in the way I need.

Greetings.

El 11/08/17 a las 16:07, 黄祥 escribió:

just an idea why not create the new field with the date type with default value = request.now? so that user can search through the new date field

best regards,

stifan

[Leonel Câmara]

Here's one way to do it. Note that when I start having this kind of problems I usually stop using the grid and make a custom solution.  

  

In the grid, the searchable argument, can be a function. In that case it gets the fields and keywords and its responsibility is to generate and return a query. You can disable advanced_search and then only have a search field. Then you will have a searchable function which will detect if one of the keywords entered in the search is a date, if that is the case then you will make a query where only records from that date are acceptable.

Your controller function would be something like this:

def mygrid():
    import datetime


    def searchable(fields, keywords):
        query = db.my_table.id > 0:
        for keyword in keywords:
            date = None
            try:
                date = datetime.datetime.strptime(keyword,'%Y-%m-%d')
            except ValueError:
                pass
            if date:
                query &= db.my_table.created_on == date
            else:
                # this is just an example
                query &= db.my_table.name.like('%' + keyword + '%', case_sensitive=False)
        return query


    return {'grid': SQLFORM.grid(db.my_table, searchable=searchable, advanced_search=False)}

[Carlos Cesar Caballero Díaz]

Thanks Leonel, I will go with this.

Greetings.

El 14/08/17 a las 11:34, Leonel Câmara escribió: