WP_Date_Query across tables

[boonebgorges]

The changes proposed in #29822 will make it possible to think about cross-table date queries. Things like: all posts from 2011 that have comments from 2013 or later.

Using this ticket as a placeholder for ideas regarding syntax (do we allow table names to be passed as a param? or perhaps a 'type' argument like WP_Meta_Query has, which is then translated into table names?) and other discussion.

[boonebgorges]

A quick note that the changes in [29933] make this sorta possible. So to use my example above:

Things like: all posts from 2011 that have comments from 2013 or later.

This translates to:

'date_query' => array(
    'relation' => 'AND',
    array(
        'column' => $wpdb->posts . '.post_date',
        'year' => 2011,
    ),
    array(
        'column' => $wpdb->comments . '.comment_date',
        'after' => '2013-01-01 00:00:00',
    ),
),

This ought to generate the correct WHERE clause. However, it doesn't build the JOIN clauses for you, so you'd have to do some other filtering to make it really work. Unfortunately, WP_Date_Query::get_sql() returns a string rather than an array of 'join' and 'where' strings like the other query classes, and we can't change that for backward compatibility. But maybe in a future version we can introduce another param to get_sql() that will change the return value format, and we can make all core calls to get_sql() use that param, so that this kind of use will be supported in WP_Query and WP_Comment_Query.

[chriscct7]

I like the idea of this