Can't figure out how to use date_query

Just a note – My goal is to pull attachments that have been uploaded within the past month. Not sure if post_date is what I should be using for that.

Thanks for opening the first topic on the recently-added support for date queries and for including the full source of your shortcode.

Your query is running afoul of two WordPress issues. First, the “invalid mla_gallery tax_query“. For some reason I cannot determine, if the first shortcode on the post/page contains a date query WordPress corrupts it. You can solve this issue by surrounding your shortcode with <code> and </code> tags, which prevents the corruption.

Second, combining a date query with the (deprecated) “simple taxonomy query” attachment_category="media-docs" causes WordPress to generate an invalid SQL query, returning a database error. I hope this can be avoided by substituting the newer tax_query syntax, and I will experiment to see if I can find an example that will work for you. Give me a day for further investigation, and thanks for your patience.

So here is the code I ended up with:

<code>[mla_gallery tax_query="array(array('taxonomy' => 'attachment_category','field' => 'slug', 'terms' => 'media-docs-budget'))" date_query="array(array('after' => '-12 months'))" mla_style=none mla_markup=mla-file-list-ul post_parent=all orderby=date order=desc post_mime_type=all link=file]</code>

However, I still wasn’t getting any results (works without the date_query though). Then, using mla_debug=true (great feature, btw) I got the SQL query and plugged it in manually and saw it was generating an error:

#1052 – Column ‘post_date’ in where clause is ambiguous

Original query:

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_posts AS p2 ON (p2.ID = p2.ID) WHERE 1=1 AND ( ( post_date > '2013-12-18 08:54:21' ) ) AND ( wp_term_relationships.term_taxonomy_id IN (51) ) AND wp_posts.post_type = 'attachment' AND (((wp_posts.post_status = 'inherit') OR (wp_posts.post_status = 'inherit' AND (p2.post_status = 'inherit')))) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

If I put “wp_posts.” in front of “post_date > ‘2013-12-18 08:54:21′” it works. Well, the manual query works anyway. I’m not sure of the output of the final product as I’m not sure how to plug that into MLA or where to modify the code for it.

Thanks so much for following up with this update, and for doing the work required to try out the tax_query.

This looks like a WordPress issue; MLA just passes the tax_query and date_query through to the WordPress WP_Query class, which is where the SQL generation is done. I will confirm that and if it is a WP bug I will turn it in. I know they are doing a lot of work on these queries in the 4.0.x and 4.1 releases.

If I can’t get the WP_Query working, I can give you an example of how to do the native SQL query by hooking one or more of the filters provided by [mla_gallery]. I will post an update here when I have progress to report. Thanks for your good work and for your patience.

I have done more investigation and confirmed the bug in WordPress 4.0.1. Leaving MLA completely out of the loop, this code:

function test_date_query() {
	global $wp_query;

	$args = array(
		'post_status' => 'inherit',
		'tax_query' => array(
			array(
				'taxonomy' => 'attachment_category',
				'field' => 'slug',
				'terms' => 'new-category'
				)
			),
		'date_query' => array(
			array(
				'after' => '-12 months'
				)
			)
	);

	$query = new WP_Query( $args );
}

causes WP_Query to generate this query:

SELECT SQL_CALC_FOUND_ROWS mladev_posts.ID
FROM mladev_posts
INNER JOIN mladev_term_relationships
ON (mladev_posts.ID = mladev_term_relationships.object_id) LEFT JOIN mladev_posts AS p2
ON (mladev_posts.post_parent = p2.ID)
WHERE 1=1
AND ( ( post_date > '2013-12-18 12:03:41' ) )
AND ( mladev_term_relationships.term_taxonomy_id IN (167,184) )
AND mladev_posts.post_type = 'attachment'
AND (((mladev_posts.post_status = 'inherit')
OR (mladev_posts.post_status = 'inherit'
AND (p2.post_status = 'inherit'))))
GROUP BY mladev_posts.ID
ORDER BY mladev_posts.post_date DESC
LIMIT 0, 2

which fails with this message:

Column 'post_date' in where clause is ambiguous

Removing the ‘post_status’ => ‘inherit’ element eliminates the error. Replacing ‘post_status’ => ‘inherit’ with ‘post_type’ => ‘attachment’ also avoids the problem.

I have also confirmed that this bug is fixed in WordPress 4.1, which has been released. If you are in a position to upgrade to the new version, it is the easiest way to solve the problem. If not, let me know and I will work out an interim fix for you.

The <code></code tags are still required around the first shortcode on the page, sadly.

I am marking this topic resolved because it is a WordPress bug and has been fixed. If you need an interim solution, post an update here and I will work it out for you. Thanks again for your patience and for your interest in the plugin.