Make WordPress Core

Opened 13 years ago

Closed 11 years ago

#18694 closed enhancement (fixed)

Improved date arguments for WP_Query

Reported by: viper007bond's profile Viper007Bond Owned by: viper007bond's profile 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)

18694.test.patch (11.6 KB) - added by Viper007Bond 13 years ago.
Preliminary patch for testing purposes, still a work in progress (see comments)
18694.patch (13.5 KB) - added by Viper007Bond 13 years ago.
Possibly good to go, minus phpdoc. Needs testing!
test-wordpress-dates.php (1.9 KB) - added by Viper007Bond 13 years ago.
My test file to make sure SQL statements are being correctly generated
18694.2.patch (15.2 KB) - added by Viper007Bond 12 years ago.
Final patch? Adds date query support to posts and comments
test-wordpress-dates.2.php (2.6 KB) - added by Viper007Bond 12 years ago.
Generates some sample queries and provides some examples
18694.3.patch (15.7 KB) - added by Viper007Bond 12 years ago.
18694-unit-tests.patch (19.2 KB) - added by Viper007Bond 12 years ago.
18694.4.patch (18.4 KB) - added by Viper007Bond 11 years ago.
Patch for 3.7
18694-unit-tests.2.patch (26.0 KB) - added by Viper007Bond 11 years ago.
Unit tests for 18694.4.patch
18694-unit-tests.3.patch (26.0 KB) - added by Viper007Bond 11 years ago.
Actually working unit tests for 18694.4.patch
18694.5.patch (43.3 KB) - added by Viper007Bond 11 years ago.
Code changes + updated unit tests

Download all attachments as: .zip

Change History (84)

#1 @nacin
13 years ago

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.

#2 @aaroncampbell
13 years ago

  • Cc aaroncampbell added

#3 @scribu
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: @Viper007Bond
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.

Last edited 13 years ago by Viper007Bond (previous) (diff)

#5 @sbressler
13 years ago

  • Cc sbressler@… added

#6 @johnbillion
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 @johnbillion
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: @GaryJ
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 @Viper007Bond
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 and post_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: @dd32
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 @Viper007Bond
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.

Last edited 13 years ago by Viper007Bond (previous) (diff)

#12 @dd32
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.

#13 @DrewAPicture
13 years ago

  • Cc xoodrew@… added

@Viper007Bond
13 years ago

Preliminary patch for testing purposes, still a work in progress (see comments)

#14 @Viper007Bond
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 @Viper007Bond
13 years ago

Oh, a few more things:

  • If before or after 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 and after sub-queries, you can set inclusive to true if you want to include exactly the before/after value(s). Basically it turns > and < into >= and <= respectively.

#16 @Viper007Bond
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) ) )

Version 1, edited 13 years ago by Viper007Bond (previous) (next) (diff)

#17 @nacin
13 years ago

So cool. Great work.

#18 @DrewAPicture
13 years ago

This is awesome, thanks for the effort.

#19 @Viper007Bond
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 return 0 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.

#20 @sabreuse
13 years ago

  • Cc sabreuse@… added

#21 @ethitter
13 years ago

  • Cc ehitter@… added

#22 @tollmanz
13 years ago

  • Cc zack@… added

#23 @mbijon
13 years ago

  • Cc mike@… added

@Viper007Bond
13 years ago

Possibly good to go, minus phpdoc. Needs testing!

@Viper007Bond
13 years ago

My test file to make sure SQL statements are being correctly generated

#24 @Viper007Bond
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: @scribu
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 @Viper007Bond
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: @scribu
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 @Viper007Bond
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.

#29 @jaredatch
13 years ago

  • Cc jared@… added

#30 @jeremyfelt
13 years ago

  • Cc jeremy.felt@… added

#31 @billerickson
13 years ago

  • Cc bill.erickson@… added

#32 @kovshenin
13 years ago

  • Cc kovshenin@… added

#33 @simonwheatley
12 years ago

  • Cc simon@… added

#34 @batmoo
12 years ago

  • Cc batmoo@… added

#35 @nbachiyski
12 years ago

  • Cc nbachiyski added

#36 @Mamaduka
12 years ago

  • Cc georgemamadashvili@… added

@Viper007Bond
12 years ago

Final patch? Adds date query support to posts and comments

#37 @Viper007Bond
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.

@Viper007Bond
12 years ago

Generates some sample queries and provides some examples

#38 @Viper007Bond
12 years ago

  • Keywords needs-unit-tests added

This is in dire need of unit tests.

#39 @hsatterwhite
12 years ago

  • Cc whsatterwhite@… added

#40 @Viper007Bond
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.

#41 @greenshady
12 years ago

  • Cc justin@… added

#42 @goto10
12 years ago

  • Cc dromsey@… added

#43 @Viper007Bond
12 years ago

Working on unit tests over at #UT126.

#44 follow-up: @Viper007Bond
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 @ethitter
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.

#46 @mordauk
12 years ago

  • Cc pippin@… added

#47 @sunnyratilal
12 years ago

  • Cc ratilal.sunny@… added

#48 @Viper007Bond
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 @Viper007Bond
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.

#50 @trepmal
12 years ago

  • Cc trepmal@… added

#51 @tomauger
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 @Viper007Bond
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 @TomAuger
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.

#54 @eddiemoya
11 years ago

  • Cc eddie.moya+wptrac@… added

#55 @mcsf
11 years ago

  • Cc mcsf added

#56 @Viper007Bond
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. :)

#57 @alex-ye
11 years ago

  • Cc nashwan.doaqan@… added

#58 @iseulde
11 years ago

  • Component changed from General to Query

#59 @wonderboymusic
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

#60 @jazzs3quence
11 years ago

  • Cc jazzs3quence added

@Viper007Bond
11 years ago

Patch for 3.7

#61 @Viper007Bond
11 years ago

Attached patch includes the patch from #24884 since it's needed to pass unit tests (which I'll be uploading soon).

@Viper007Bond
11 years ago

Unit tests for 18694.4.patch

#62 @Viper007Bond
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.

#63 @DrewAPicture
11 years ago

  • Keywords has-patch has-unit-tests added; needs-patch removed

#64 @Viper007Bond
11 years ago

  • Keywords needs-testing added

#65 @Viper007Bond
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...

#66 @desrosj
11 years ago

  • Cc desrosj@… added

@Viper007Bond
11 years ago

Actually working unit tests for 18694.4.patch

#67 follow-up: @Viper007Bond
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 @SergeyBiryukov
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 @Viper007Bond
11 years ago

  • Owner changed from viper007bond to Viper007Bond
  • Status changed from new to assigned

@Viper007Bond
11 years ago

Code changes + updated unit tests

#70 @Viper007Bond
11 years ago

Latest patch has updated unit tests per Nacin's recommendations. I also caught a few bugs in the unit tests themselves.

It's also a singular patch to match the new repository layout.

#71 @nacin
11 years ago

In 25139:

WP_Date_Query.

props Viper007Bond.
see #18694.

#72 @nacin
11 years ago

#16882 was marked as a duplicate.

#73 @helen
11 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

Woohoo. New tickets for any specific issues encountered, please.

Note: See TracTickets for help on using tickets.