WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

Last modified 4 years ago

#29822 closed enhancement (fixed)

Support complex queries in WP_Date_Query

Reported by: boonebgorges Owned by: boonebgorges
Milestone: 4.1 Priority: normal
Severity: normal Version:
Component: Query Keywords: has-patch
Focuses: Cc:

Description

Sister tickets: #29642 (WP_Meta_Query) and #29738 (WP_Tax_Query).

WP_Date_Query currently supports one level of relation between subqueries, so you can ask for something like 'items from 2011 OR items from 2010' or 'items published in 2010 AND on a Thursday'. But, excepting some clever manipulation of IN clauses, it's not possible to get any more complex than this.

I propose to update WP_Date_Query to support arbitrarily nested queries, which will support mixed relations.

Using the WP_Recursive_Query structure in #29642 will automatically mean another added feature: the ability to construct a date query across multiple columns. So you'll be able to make a request like 'items with post_date between 2010 and 2012 AND post_modified > 2013'. Cross-table support will be the next logical step but is a bit complex to include in this ticket; I'll open a separate one for that feature.

See #29781 for the unit tests that will support this refactoring.

I'll have an initial patch for review within a couple days.

Attachments (2)

29822.patch (26.1 KB) - added by boonebgorges 5 years ago.
29822.2.patch (20.9 KB) - added by boonebgorges 5 years ago.

Download all attachments as: .zip

Change History (8)

This ticket was mentioned in IRC in #wordpress-dev by boonebgorges. View the logs.


5 years ago

@boonebgorges
5 years ago

#2 @boonebgorges
5 years ago

  • Keywords has-patch added
  • Owner set to boonebgorges
  • Status changed from new to accepted

29822.patch implements the proposed WP_Recursive_Query described here: https://core.trac.wordpress.org/ticket/29642#comment:6

#3 @boonebgorges
5 years ago

  • Component changed from Date/Time to Query

29822.2.patch brings WP_Date_Query up to snuff with WP_Meta_Query [29887] and WP_Tax_Query [29891]. Documentation, method naming and order, etc is all aligned. Posting here for review.

My original description for this ticket is a bit lacking, so I want to take a moment to spell out what these changes will (and won't) mean in terms of WP_Date_Query syntax. Because posts generally only have a single date, it's not immediately obvious why you'd want to do this sort of query in real life. Here's an example:

// All items from 2012 that were created outside of work hours 
// (either on a weekend or between 5pm and midnight)
'date_query' => array(
    'relation' => 'AND',
     array(
        'column' => 'post_date',
        'year' => 2012,
     ),
     array(
        'relation' => 'OR',
        'column' => 'post_date',
        array(
            'dayofweek' => array( 1, 7 ),
        ),
        array(
            'hour' => 17,
            'compare' => '>=',
        ),
    ),
),

This will get significantly more powerful once it's possible to build date queries across tables. See #29823, #25775.

This ticket was mentioned in IRC in #wordpress-dev by boonebgorges. View the logs.


5 years ago

#5 @boonebgorges
5 years ago

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

In 29923:

Introduce nested query support to WP_Date_Query.

This enhancement makes it possible to filter post, comment, and other queries
by date in ways that are arbitrarily complex, using mixed AND and OR relations.

Includes unit tests for the new syntax. In a few places, the existing unit
tests were slightly too strict (such as when checking the exact syntax of a SQL
string); these existing tests have been narrowed.

Props boonebgorges.
Fixes #29822.

This ticket was mentioned in Slack in #core by dlh. View the logs.


4 years ago

Note: See TracTickets for help on using tickets.