Opened 13 years ago
Closed 11 years ago
#18694 closed enhancement (fixed)
Improved date arguments for WP_Query
Reported by: | Viper007Bond | Owned by: | Viper007Bond |
---|---|---|---|
Milestone: | 3.7 | Priority: | normal |
Severity: | normal | Version: | 3.3 |
Component: | Query | Keywords: | has-patch has-unit-tests needs-testing |
Focuses: | Cc: |
Description
Currently there is no way to get a set of posts that fall within a date range, are older/newer than a certain date, etc.
A new set of arguments should be created for WP_Query
that work similarly to the relatively new meta and taxonomy arguments.
Argument structure suggestions welcome.
Attachments (11)
Change History (84)
#3
@
13 years ago
Sign me up. It would make it easier to use WP_Query in get_adjacent_post() - #17807 - calendar plugins, etc. etc.
#4
follow-up:
↓ 7
@
13 years ago
Nacin and I have been brainstorming a bit and here's some requirements and potential argument formats that we've come up with.
'date_query' => array( // Find posts between two dates array( 'before' => '2011-09-17 16:40:23', 'after' => array( 'year' => 2011, 'month' => 8, 'day' => 12, ), 'inclusive' => true, // Including exactly the dates ), // Find posts NOT between two dates // due to before being less than after array( 'before' => '2011-09-10', 'after' => '2011-09-15', ), // Find all posts after 2PM on any day array( 'time' => '14:00:00', 'compare' => '>', ), // Find posts made during // Nacin's crazy work hours array( 'after' => '8:30:00', 'before' => '17:15:45', // Is this even possible? 'dayofweek' => array( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ), ), ),
The two range ones are easy -- basic MySQL.
The "after 2PM" example shouldn't be hard either. We're thinking you covert the time to a number, like 4:32:45
would turn into 4.3245
. You can't independently compare hours and minutes because that last example would be greater than 30 and less than 15. You will have to merge hours, minutes, and seconds into a single number you can compare.
I have no idea if the day of the week thing is possible, but it's an interesting idea.
#6
@
13 years ago
Great idea.
Could we get a 'field' parameter too? We have post_date
, post_date_gmt
, post_modified
and post_modified_gmt
which could all work with this query format. post_date
would be the default of course.
#7
in reply to:
↑ 4
@
13 years ago
Replying to Viper007Bond:
// Is this even possible? 'dayofweek' => array( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )
Should be able to do this with MySQL's DAYOFWEEK()
function, eg. to select all weekend posts:
WHERE DAYOFWEEK( post_date ) IN ( 1, 7 )
#8
follow-up:
↓ 9
@
13 years ago
Highlighting in case others hadn't spotted it - the first before/after is an AND, whereas the second before/after would be an OR.
I agree with the field argument too, since someone might want posts published before one date, and edited after another.
C/W/Should the arguments here also eventually cover dates stored in meta fields too?
#9
in reply to:
↑ 8
@
13 years ago
I think I'm ditching string support. It's so much easier from a code perspective (hard to parse) and less ambiguous if you just pass an array of year, month, day, hour, minutes, and/or seconds.
Replying to johnbillion:
Could we get a 'field' parameter too? We have
post_date
,post_date_gmt
,post_modified
andpost_modified_gmt
which could all work with this query format.post_date
would be the default of course.
Totally.
Replying to GaryJ:
C/W/Should the arguments here also eventually cover dates stored in meta fields too?
No, I don't think so. So much added complexity and I think the existing meta_query
arguments should be able to handle most (but not all) of that kind of stuff.
#10
follow-up:
↓ 11
@
13 years ago
I think I'm ditching string support. It's so much easier from a code perspective (hard to parse) and less ambiguous if you just pass an array of year, month, day, hour, minutes, and/or seconds.
Less ambiguous for sure, however less flexible and will end up with people having to re-invent the wheel processing timestamps themselves.
Something like this might be usable:
$a = array(); list($a['year'], $a['month'], $a['day'],$a['hour'],$a['minute'],$a['second']) = explode(',', date('Y,m,d,H,i,s', strtotime('today'))); //TODO: Filter out any items which are equal to 00 (h:i:s when only a date was specified) var_dump($a);
has the advantage you could use "6 hours ago" as well :)
and I think the existing meta_query arguments should be able to handle most (but not all) of that kind of stuff.
Definitely agree, This can utilise date functions on a date column, meta fields are well, strings :)
#11
in reply to:
↑ 10
@
13 years ago
Replying to dd32:
Something like this might be usable:
But what about 2011-09
? Should that be September 1st, 2011 (which is what strtotime()
will give you) or should it be any day in September (my intent). How do you know from the strtotime()
response that it wasn't 2011-09-01
that was passed (a specific day rather than a specific month)?
Perhaps if is_string()
, then just run it through strtotime()
and use the result as-is. This provides 6 months ago
and full MySQL date/time support but users will just have to realize that wildcards need to be passed as an array instead of a partial date/time string.
#12
@
13 years ago
But what about 2011-09 ? Should that be September 1st, 2011 (which is what strtotime() will give you) or should it be any day in September (my intent).
If you want to be specific, you need to use a specific time format, simple :)
That being said, 2011-09 would work in some situations, just not every situation. After 2011-09 wouldn't work, as it'll include sept. Before 2011-09 would work fine though.
To me it sounds like a reasonable "shortcut" to prevent reinventing the wheel, it simplifies parsing for 99% of situations and if you're specific in your input, it'll respect it. Some people will get caught up by it doing things they don't expect if they're vague in what they request.
#14
@
13 years ago
Okay, attached is a patch that contains what should be a fully flushed out WP_Date_Query
class. The current integration into WP_Query
is just preliminary and for testing purposes. It will need to be replaced with some code that uses WP_Date_Query
to process the legacy date/time parameters. There's also a bit more PHPdoc work to be done.
For now though this patch will do nicely for testing and feedback which is what I'm really after at this point.
Here's some examples that you can use to test:
// Between 8:45 AM and 5:12 PM on weekdays $query_test = new WP_Query( array( 'date_query' => array( 'column' => 'post_date', // Default 'relation' => 'AND', // Default array( 'hour' => 8, 'minute' => 45, 'compare' => '>=', ), array( 'hour' => 17, 'minute' => 12, 'compare' => '<=', ), array( 'dayofweek' => array( 2, 6 ), 'compare' => 'BETWEEN', ), ), ) );
// Posted between 2-3 weeks ago $query_test = new WP_Query( array( 'date_query' => array( array( 'after' => '3 weeks ago', 'before' => '2 weeks ago', ), ), ) );
// Posted before March 1st, 2010 $query_test = new WP_Query( array( 'date_query' => array( array( 'before' => array( 'year' => 2010, 'month' => 3, 'day' => 1, ), ), ), ) );
// Posted during the 2 PM hour on a Sunday or during the 6 PM hour on a Monday or Tuesday $query_test = new WP_Query( array( 'date_query' => array( 'relation' => 'OR', array( 'hour' => 14, 'dayofweek' => 1, ), array( 'hour' => 18, // Will be cast to an array 'dayofweek' => array( 2, 3 ), 'compare' => 'IN', ), ), ) );
#15
@
13 years ago
Oh, a few more things:
- If
before
orafter
is set in a sub-query, then all other parameters will be ignored (month
,hour
, etc.). Perhaps it shouldn't be this way ("before XXX and month=5") but I only just thought of this now and you can likely accomplish that anyway with the current code.
- For
before
andafter
sub-queries, you can setinclusive
to true if you want to include exactly thebefore
/after
value(s). Basically it turns>
and<
into>=
and<=
respectively.
#16
@
13 years ago
Oh, and here's the SQL of the above examples:
AND ( ( DATE_FORMAT( wp_trunk_posts.post_date, '%H.%i' ) >= 8.450000 ) AND ( DATE_FORMAT( wp_trunk_posts.post_date, '%H.%i' ) <= 17.120000 ) AND ( DAYOFWEEK( wp_trunk_posts.post_date ) BETWEEN 2 AND 6 ) )
AND ( ( wp_trunk_posts.post_date > '2011-09-05 01:44:13' AND wp_trunk_posts.post_date < '2011-09-12 01:44:13' ) )
AND ( ( wp_trunk_posts.post_date < '2010-03-01 00:00:00' ) )
AND ( ( DAYOFWEEK( wp_trunk_posts.post_date ) = 1 AND HOUR( wp_trunk_posts.post_date ) = 14 ) OR ( DAYOFWEEK( wp_trunk_posts.post_date ) IN (1,2) AND HOUR( wp_trunk_posts.post_date ) IN (18) ) )
#19
@
13 years ago
Thanks guys. :)
Also a few bugs I noticed:
$valid_args
array is out of date (missing some args) but that's just there to prevent notices and make life easier by filling in the arrays.
$value = $this->build_value( $compare, $query['foo'] )
can return0
which means it will be ignored. I think only the week parameters are affected here.
- Speaking of the week parameters, I should make use of
_wp_mysql_week()
in my code.
#24
@
13 years ago
Okay! Minus a little bit of missing phpDoc, I think this might be good to go. This is certainly not WordPress 3.4.0 material because it will need major testing but maybe it can go in early into 3.5.0 or something.
The legacy individual date/time parameters have been ported over to the new class and appear to be working the same, but this needs to testing to make sure it's 100% backwards compatible. Not sure how to design unit tests around this.
I also wasn't sure the best way to integrate it into WP_Query
since WP_Tax_Query
and WP_Meta_Query
are both integrated differently, but I took a stab at it.
Thoughts, comments, and feedback is desired! :)
#25
follow-up:
↓ 26
@
13 years ago
- Keywords has-patch added
For consistency with the other query classes, get_sql() should return an associative array.
Also, I don't think the other methods should be public.
But a bigger issue is that it's currently limited to the wp_posts table. It should be usable with any table.
#26
in reply to:
↑ 25
@
13 years ago
Replying to scribu:
For consistency with the other query classes, get_sql() should return an associative array.
They only do that because they need to modify both the JOIN
and the WHERE
. My stuff is strictly WHERE
-based so there's no need to return array( 'where' => $sql_string )
is there?
But a bigger issue is that it's currently limited to the wp_posts table. It should be usable with any table.
I hadn't thought of making it support comments. That's a great idea.
#27
follow-up:
↓ 28
@
13 years ago
They only do that because they need to modify both the JOIN and the WHERE. My stuff is strictly WHERE-based so there's no need to return array( 'where' => $sql_string ) is there?
Speaking of which, it would be useful if we could treat custom fields as date columns. For example, there are many 'event' post type definitions, with two custom fields: 'start_date', 'stop_date'.
#28
in reply to:
↑ 27
@
13 years ago
Replying to scribu:
Speaking of which, it would be useful if we could treat custom fields as date columns. For example, there are many 'event' post type definitions, with two custom fields: 'start_date', 'stop_date'.
Yeah, I guess that's possible. WHERE HOUR( post_meta.meta_value ) = 1
or whatever.
#37
@
12 years ago
- Keywords needs-testing added
I believe the above patch is ready to go into core.
It adds support for querying both posts and comments based on date parameters:
$query = new WP_Query( array( 'date_query' => array( 'column' => 'optional, column to query against, default is post_date', 'compare' => 'optional, see WP_Date_Query::get_compare()', 'relation' => 'optional, OR or AND, how the sub-arrays should be compared, default is AND', array( 'column' => 'see above', 'compare' => 'see above', 'after' => 'string or array, see WP_Date_Query::build_mysql_datetime()', 'before' => 'string or array, see WP_Date_Query::build_mysql_datetime()', 'inclusive' => 'boolean, for after/before, whether exact value should be matched or not', 'year' => '4 digit int', 'month' => 'int, 1-12', 'week' => 'int, 0-53', 'day' => 'int, 1-31', 'hour' => 'int, 0-23', 'minute' => 'int, 0-60', 'second' => 'int, 0-60', ), ), ) );
$comments = get_comments( array( 'date_query' => array( // ... ), ) );
Not supported is querying against post meta values however the class is specifically written to be able to query against any database table or column. WP_Meta_Query
just needs to be updated to use this new class and I wasn't sure the best way to go about doing that argument wise.
Legacy date/time parameters are handed by this new class and it's important that testing be done to make sure that queries function the same way as before.
Please apply the patch, test, and post your feedback! :)
Incoming test file next.
#40
@
12 years ago
Over the weekend, I learned the magic of unit tests and managed to install PHPUnit. I'll see if I can get some unit tests going.
#44
follow-up:
↓ 45
@
12 years ago
When passing an array to before
, items default to the current year and the minimum values for everything else.
For after
, perhaps these values should default to the maximum, for cases like this:
'date_query' => array( array( 'after' => array( 'year' => 2009, ), ), ),
Currently that would mean after January 1st, 2009 rather than anything from the year 2010 or newer, which could be confusing.
#45
in reply to:
↑ 44
@
12 years ago
Replying to Viper007Bond:
For
after
, perhaps these values should default to the maximum
I think that makes a lot more sense and is what users will expect from what the syntax implies.
#48
@
12 years ago
I think I'm happy with this latest patch and I've written 39 unit tests (which are attached). The non-date_query
tests, i.e. legacy date/time parameters, pass on both unpatched and patched trunk WordPress meaning there's (hopefully) no regressions.
#49
@
12 years ago
- Keywords 3.6-early added; needs-unit-tests removed
I believe Nacin wanted to get this into 3.6 early so proper real-world testing could be done.
#51
@
12 years ago
Okay, we'll be testing this out over the next few days. Will report back if we encounter any issues. Would like to see this get in early, too.
#52
@
12 years ago
- Keywords 3.6-early removed
Nacin pointed out some flaws in my code. For example I should use a whitelist for the column name and I missed a legacy date argument. I need to refresh my patch when I get a chance (probably too late for 3.6 anyway).
That said it could still use testing as the SQL itself should be finalized.
#53
@
12 years ago
Thanks for the update. It's running well right now on 10,000+ queries (overnight cron task). So far we haven't encountered any issues and the performance seems good. If you want to post further details of Nacin's feedback here, I can probably do the refresh if it's clear what needs to be done.
#56
@
11 years ago
Just an update: I refreshed and updated this patch during the WCSF dev day and have been writing additional unit tests. When I'm finished and happy with it, I'll upload it. :)
#59
@
11 years ago
- Keywords needs-patch added; has-patch needs-testing removed
- Milestone changed from Awaiting Review to 3.7
Just waiting for the new patch so we can test for 3.7
#61
@
11 years ago
Attached patch includes the patch from #24884 since it's needed to pass unit tests (which I'll be uploading soon).
#62
@
11 years ago
I'm having some trouble lately running unit tests locally so there's potential for there to be issues with those unit tests.
They were working fine a few weeks back though and I've done some manual testing as well.
If someone could apply the two patches and then run the unit tests for me, that'd be great.
#65
@
11 years ago
Turns out my copy of PHPUnit isn't broken and that all of my tests really are skipping. Now to figure out why they suddenly broke...
#67
follow-up:
↓ 68
@
11 years ago
Fixed the unit tests by removing the @ticket 18694
which apparently means it checks the ticket status and makes sure its closed before trying to run the tests. Oops.
Also fixed a unit test that was failing by accident.
Should be all good to go now hopefully.
#68
in reply to:
↑ 67
@
11 years ago
Replying to Viper007Bond:
Fixed the unit tests by removing the
@ticket 18694
which apparently means it checks the ticket status and makes sure its closed before trying to run the tests. Oops.
You can add back the @ticket
annotation and just specify the ticket number when running the tests:
phpunit --group 18694
See http://make.wordpress.org/core/handbook/automated-testing/#running-specific-tests.
#69
@
11 years ago
- Owner changed from viper007bond to Viper007Bond
- Status changed from new to assigned
I would love to see a date_query that allows for BETWEEN two dates (or years, or months, etc.), greater than or less than, IN, etc.