Filter on the day of the week from timestamp

This answer may help someone who really wants to customize their filters. With PhpStorm and breakpoints I could walk through how the meta_query is constructed before posts are fetched. There are no hook points in the flow that let developers accomplish this; I’ve used a SQL-injection trick to achieve the filter.

// Do advanced SQL editing for filtering the days of the week
add_filter( 'posts_where_request', array( &$this, 'processSQLDelMarkers' ), 99, 2 );

/**
 * Manipulate the pre-flight SQL query to filter on the day of the week.
 * Remove any ##DEL## marker as well as the next character after it
 * @param $where
 * @return mixed
 */
public function processSQLDelMarkers($where, \WP_Query &$query)
{
    // Special gate
    if (stripos($where, '##DEL##') === false) {
        return $where;
    }

    // ... security checks omitted for brevity ...

    /**
     *  Sample where clause before adjustment:
     *  ... AND ihs_postmeta.meta_value != '##DEL##\' AND WEEKDAY(FROM_UNIXTIME(ihs_postmeta.meta_value)) = 4##DEL##' ) ...
     *  becomes
     *  ... AND ihs_postmeta.meta_value != '' AND WEEKDAY(FROM_UNIXTIME(ihs_postmeta.meta_value)) = 4 ) ...
     */
    return preg_replace( '(##DEL##.?)', '', $where );
}

Then using a carefully constructed meta value and dummy compare like so (see the code in the original question) I could achieve the SQL injection:

$qv['meta_query'][] = array(
    'key' => '##DEL##\' AND WEEKDAY(FROM_UNIXTIME(ihs_postmeta.meta_value)) = 4##DEL##',
    'value' => $day,
    'compare' => '!='
);

Note: With the right security checks it should not be possible to inject unwanted SQL strings.

Related Posts: