Refactoring user search to allow search/filter by custom fields

Note: We are moving the topics of this forum and it will be deleted at some point

Publish your own request for comments/change or patches for the next version of phpBB. Discuss the contributions and proposals of others. Upcoming releases are 3.2/Rhea and 3.3.
Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: Refactoring user search to allow search/filter by custom fields

Post by Lucifer4o » Wed Apr 23, 2014 1:30 am

Hmmm I have a question regarding dates in SQL DBs other then MySQL/Maria DB.

As I started to work on the date field, the date is kept in D-M-YYYY format. In MySQL/Maria DB - this is valid request:

SELECT * FROM phpbb_users
WHERE user_birthday > '30- 7-1984'
ORDER BY phpbb_users.user_birthday ASC

Will this be a valid request in other SQL databese (MS SQL, Postgre) ?

If not I will have to request a change of the dateformat on other ticket.

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: Refactoring user search to allow search/filter by custom fields

Post by DavidIQ » Wed Apr 23, 2014 2:36 am

Most, if not all DBMSs actually follow or allow/understand the format YYYY-MM-DD
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: Refactoring user search to allow search/filter by custom fields

Post by Lucifer4o » Wed Apr 23, 2014 2:41 am

This will not do the trick ... that missing 0 on the lower then 10 days and months ... Can't we just change the datefield to accept timestamp?

*Nevermind the first part ... I had mistake in my request ... but still a valid qestion - can't we change the format of the date field?

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: Refactoring user search to allow search/filter by custom fields

Post by DavidIQ » Wed Apr 23, 2014 2:57 am

What benefit would there be in doing that?
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: Refactoring user search to allow search/filter by custom fields

Post by Lucifer4o » Wed Apr 23, 2014 3:14 am

It will allow searchability in custom date fields.

Easear sort ... and evrywhere else there is used a timestamp. Only in custom date fields (including birthday) it is used with this date statement. And the field is not in date type, but a VARCHAR.

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: Refactoring user search to allow search/filter by custom fields

Post by DavidIQ » Wed Apr 23, 2014 3:55 am

Ah yes...was thinking that the field was VARCHAR but thought something else was going on now in 3.1 since the comparison still worked. Now I see that the database seems smart enough to be able to do a conversion. If it gets changed to another type it would have to be integer (timestamp) like all other date/time fields plus a conversion would be needed, which may not work very well in all cases.
Image

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: Refactoring user search to allow search/filter by custom fields

Post by Lucifer4o » Wed Apr 23, 2014 5:08 am

I can write a migration script ...

Something along the lines of:

Get all field names for type date
For each of them do the following:
Change name to some hash (so we don't duplicate it)
Create new column with type int.
Start converting old dates to unix timestamp and write them to the new column
Drop hashed column (old one)
Go to next ...

Should I implement it with this ticket, or should I create a new one?

This will take a long time but ... it can be done. What is the conversion limit? 100, 250, 500 rows at a time?

I think I can have it working to tomorrow morning. I'll just need some help with migration part - where should I put the code exactly.

User avatar
EXreaction
Registered User
Posts: 1555
Joined: Sat Sep 10, 2005 2:15 am

Re: Refactoring user search to allow search/filter by custom fields

Post by EXreaction » Wed Apr 23, 2014 10:50 pm

1960-01-01

Just try to make a UNIX timestamp for it. ;)

Lucifer4o
Registered User
Posts: 23
Joined: Wed Dec 11, 2013 6:50 pm

Re: Refactoring user search to allow search/filter by custom fields

Post by Lucifer4o » Thu Apr 24, 2014 10:23 am

Rofl ... that is a fair point ...

But you only get negative timestamp.

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1731
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: Refactoring user search to allow search/filter by custom fields

Post by DavidIQ » Thu Apr 24, 2014 4:31 pm

Some forums have modified the collection of this data as well so they only collect certain portions of the date, I.e. only the year, or month and year, or month and day, etc. Since this is a VARCHAR field the data then gets stored strangely in some cases: "19-12- " or " - -1990" so on so forth making a conversion a bit more difficult.
Image

Post Reply