Earlier today, a patch for WordPress that I’ve been working on got committed to WordPress trunk. “Trunk” is the in-development version of WordPress and will eventually become the next version of WordPress, in this case 3.7.
My patch introduces the ability to do complex date-based queries for fetching both posts and comments from the WordPress database. In the past, you could select posts that had a specific value for year, month, etc. but there was no way to do things like selecting all posts before (or after) a certain date or selecting all posts between two different dates. With my patch, this and more is now easily possible.
Here’s some examples:
// Get the 10 most recent posts made // between 9AM and 5PM on weekdays $some_posts = new WP_Query( array( 'date_query' => array( array( 'hour' => 9, 'compare' => '>=', ), array( 'hour' => 17, 'compare' => '<=', ), array( 'dayofweek' => array( 2, 6 ), 'compare' => 'BETWEEN', ), ), 'posts_per_page' => 10, ) ); // Get all posts from this summer // June 1st to August 31st, inclusive // Note that strtotime()-compatible strings can be used $some_posts = new WP_Query( array( 'date_query' => array( array( // String via strtotime() 'after' => 'June 1st, 2013', // Or if you want, an array 'before' => array( 'year' => 2013, 'month' => 8, 'day' => 31, ), 'inclusive' => true, ), ), 'posts_per_page' => -1, ) ); // Any posts made over a year ago // but modified in the past month $some_posts = new WP_Query( array( 'date_query' => array( array( 'column' => 'post_date_gmt', 'before' => '1 year ago', ), array( 'column' => 'post_modified_gmt', 'after' => '1 month ago', ) ), 'posts_per_page' => -1, ) );
It works for comments too:
// All comments from post ID 123 // that are within the past week $some_comments = get_comments( array( 'post_ID' => 123, 'date_query' => array( array( 'after' => '1 week ago', ), ), ) );
As you can see, the possibilities and combinations of cool things you can do are endless.
Here’s all of the possible arguments:
'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', ), array( ... ), .. ),
Additionally, all of the old-school date and time arguments for WP_Query
are now handled by my code as well. They will continue to work as before and you only need to use the date_query
parameter if you want more advanced control of your results.
Questions? Anything you want me to clarify? Leave a comment below. 🙂
This is freaking sexy.
I already know how it could be used for some client work, as well. Congrats on this his props Alex.
Does this also have the ‘relation’ option like meta_query had? If so, can you nest the date_query arrays for multiple ‘or’/’and’ combinations?
Yes, this has
relation
(see the documentation code block in the post). I usedmeta_query
for inspiration and guidance. However liketax_query
andmeta_query
it can only be applied overall — either all conditions areAND
to each other or they areOR
. You can’t mix.If you want to do more complex things then you’ll have to filter the SQL. You can make a new instance of
WP_Date_Query
and have it help you out with constructing the SQL though.OK cool, thanks! Was just curious because I’ve been considering adding a patch for nested ‘relation’ arrays, so I now need to look at this new class for what implementation would take there too.
That would be cool. I just wasn’t sure how to go about doing it without getting into insane array structures.
We considered nested arrays for relations when doing the original tax_query stuff (to allow mixing of OR and AND cases), but I believe people thought it too complex and to have somewhat limited use cases. In the end, I think if you’re making relationships that complex, you might be outside the primary use case of “meta” and should probably consider making your own table instead.
I may end up adding a filter or two for my purposes, so my project can extend WP_Query meta_query handling to support nested arrays. Pods currently supports nested arrays for it’s own ‘where’ handling, and it would be great to give our users that option too. We’ll be hooking into WP_Query for certain meta fields which will actually be mapped to their own table in some cases.
Also, does this new date handling work for meta_query fields cast as date/time?
No, it does not. This would fall under an enhancement of the existing
meta_query
argument rather than falling under my newdate_query
argument. MyWP_Date_Query
class could be used to for this though as all it does is generateWHERE
clauses for the specified column.Open a ticket if this is something you want. 🙂
When I next hit a project that needs it I’ll definitely do that, thanks for your 2 years worth of fighting for this, even just with the post date, it’s a huge step forward.
No problem. I wrote it as a generic SQL helper class so that it can be used for pretty much anything, I just only added support for the posts and comments tables for now as those were low hanging fruit and pretty straightforward.
Considering this would have required modifications to WP_Query::get_posts what measures did you take to prevent an increase in NPath complexity despite adding a new feature? ( In 3.6 it has an NPath complexity of 1,435,733,941,397,422,709,124,940,625,188,500,371,668,992,000,000 with a recommendation of 200 or below )
Sorry, but I don’t know what you’re talking about. This simply adds a single new argument to
WP_Query::get_posts()
. If you don’t use the parameter, then queries stay as simple as before. It’s not adding overhead or query complexity if you don’t use it. If you do use it, then it’s using MySQL’s built-in date and time functions to do the hard work which should allow normal optimizations by MySQL.The change to the class method can be found here:
http://core.trac.wordpress.org/changeset/25139/#file2
If you have concrete recommendations for improvements to my code, I am more than willing to hear them and incorporate them into my code. Please leave them over on Trac instead of here though as that’s the canonical discussion location:
http://core.trac.wordpress.org/ticket/18694
NPath complexity is a measure of code quality from Computer Science, not performance/cpu overhead, You bring it down by encapsulating and moving code into discrete units so it’s easier to understand and test.
This link will explain it: http://unassumingphp.com/npath-complexity-demystified/
I ask because that function has the highest NPath complexity in all of WordPress by orders of magnitude.
I’ve heard of NPath before but that’s about it. My lack of formal education is failing me here. I went to school for mechanical engineering before realizing that wasn’t for me. 🙂
Anyway, all of the code that does this work is off on its own inside of a class, outside of
WP_Query
. The only new code added toWP_Query::get_posts()
is literally anif
statement or two, checking to see if the parameter is set.Now if it is set, then the new
WP_Date_Query
class gets called and that’s when things get a little more complex. It is abstracted out into various class methods in an effort to keep things simple but it is also not overly abstracted just for the sake of keeping the number of conditionals low.Feel free to judge for yourself. The code can be found here:
http://core.trac.wordpress.org/browser/trunk/src/wp-includes/date.php
Eh I wasn’t aware of NPath and cyclomatic complexity until the last year myself, but I’m glad to see you’ve put things in a module to one side =]
awsome!!!
This is really great, thanks for your contribution! I’m excited to use this soon.
Pingback: Date queries in WordPress 3.7 : Post Status
I saw the patch being committed and thought just how epic it really was. I’ve already got ideas for how to make use of it. This should make my Timeline plugin look a little more snazzy! 🙂
Very nice. Great work Alex!
This is just awesome, thank you for your contribution!
Beautiful.
Thank you 🙂
Nice work 😉
That’s great news and well done mate. It’s actually kinda strange that this was not added to core a long time ago. This will definitely come in handy. 🙂
Great look into the date queries.
Perfect. This definitely beats the current method of adding a filter to ‘posts_where’. I can’t believe it’s not already available. I need to be able to do this now. Do you think you could take a look at this post in the support forum about using the posts_where approach inside of a class? I’ve searched high and low.
http://wordpress.org/support/topic/posts_where-filter-doesnt-work-in-a-class?replies=1#post-4634263
Looks like you sorted it out yourself, but I left a comment with some additional tips anyway.
Pingback: A practical use for the new date queries in WP 3.7
Thank you. Exactly what I needed and released just a few weeks before I needed it. Good work… add_filter(“posts_where”, “…”) was annoying and really didn’t work right.
Alex, the WP_Meta_Query used in WP_Query, WP_Comment_Query and WP_User_Query.
What about your new WP_Date_Query, did you implement it only in WP_Query ?
As mentioned in the post, it’s supported for comments as well. It is not supported for querying users though but it could easily be extended to support this. Open a ticket and assign it to me (my WP.org username is Viper007Bond).
Did this ever get implemented? Would be great to be able to use date params with WP_User_Query
I updated the wordpress codex. you can add new examples to the codex:
http://codex.wordpress.org/Class_Reference/WP_Query#Date_Parameters
Pingback: ?? WordPress 3.7 ??? WP_Date_Query | ????
Pingback: WordPress 3.7 ?????????? WP_Date_Query | WordPress??
Its really nice idea. I think it would be helpful for all. Thank you for sharing with us.
before and after
Pingback: wp-tricks.co.il ??????? ??????? ???????? 3.7
I need this in my mouth RIGHT NOW. 3.7 cannot come soon enough! This is a lovely way to handle date-based queries, and funnily enough fulfils my exact requirements at this very moment (which is kinda how I ended up here in the first place). Kudos!
Pingback: Endlich! Date Queries! Funktion "Zeige Posts von Datum A bis Datum B" bald im Core! | WP-Entwickler.at
Pingback: WordPress 3.7 Introduces Advanced Date Queries
Pingback: WordPress 3.7 ?????????? WP_Date_Query | WP??
Pingback: What's new in WordPress 3.7, "Basie" : Post Status
Pingback: What’s new in WordPress 3.7 | Business & Technology
Pingback: Separatista
Pingback: WordPress 3.7 Now Available :: WebDesign.com
Pingback: What’s new in WordPress 3.7, “Basie”
Pingback: wp-tricks.co.il ??????? 3.7
Nice. How would I do to list most commented posts from last 24h/today, this week, this month, this year or all time? Or since X days ago. My start as a newbie: http://pastebin.com/8bEMriVL
Figured it out, I think – it was simpler than I thought: http://alxmedia.se/code/2013/10/how-to-list-most-commented-posts-with-the-new-date-queries-in-wordpress-3-7/
It´s really awesome! Thank you so much!
I was wondering if there is a way to query posts like query the last 3 posts of the current day and after “of the current date”.?
Thank you
Yes, you can do things like
'after' => 'yesterday'
if you want. The string is parsed bystrtotime()
which accepts a wide variety of formats: http://php.net/strtotimeThank you so much, keep sharing!
Pingback: WP Magnet | A look into: WordPress Date Query
Pingback: ?????? ??? – ????? ??????????? – ??????? ? ????? ????????? ? ??????? ? ??????? » A look into: WordPress Date Query
Pingback: A look into: WordPress Date Query | RichInfoWorldRichInfoWorld
Pingback: A look into: WordPress Date Query | OGM Français - Une vue terrifiante de l'Asie
Pingback: A look into: WordPress Date Query – Supreme #WordPress Blog | Supreme Factory
Pingback: A look into: WordPress Date Query | DesignNews
Pingback: A look into: WordPress Date Query - California King Sets
Pingback: Entendendo o Date Query no WordPress 3.7 | Super agregador
Pingback: A Look Into: WordPress Date Query : wpimp.com
Hi Alex,
I was wondering if there is a way to query posts like this
show posts only if today is between post_date and post_date+15 days
?
Thanks
I’m confused by what you mean? Are you talking about showing posts from the past 15 days? Or something else?
If so, just do
'after' => '15 days ago'
.Thanks, maybe you saved me from a mental trip 🙂
This is awesome.
By the way, handling events (as cpt) and using ‘start date’ and ‘end date’ as custom fields; can I compare ‘start date’, ‘end date’ and ‘current date’ – so only events which are running or has the last day today or are in future can be shown.
I am using this approach currently (Line 47-48 : https://gist.github.com/vajrasar/8158758) but this is fetching all posts and than filtering posts to display/or not. I know this is wrong, so was wondering if I can do this with WP_Query.
Thanks.
Pingback: A Look Into: WordPress Date Query
Wow, thanks a bunch for the precious contribution, you rock. So much better than the “filter” ugly method.