Support » Plugin: Query Wrangler » Filter by subquery?

  • Resolved diekunstderfuge

    (@diekunstderfuge)



    Hi,

    As a Drupal user I was so grateful to find this plugin, and I especially love being able to just drop a shortcode in a page and have my query all ready to go. I’m a bit stuck now, though, because I can’t find an obvious solution to what I need to do in my query.

    I have a query that returns my custom post type of “concert.” Each concert post has a “concert_date” date field created through ACF. The query is supposed to return all the concerts that occur within the current concert season. Now, I suppose I could hardcode dates within the filter (i.e., a WHERE concert_date BETWEEN 'some date' AND 'some other date' type clause, though I’m not sure what the syntax would be for Query Wrangler), but I don’t want to do that. I’ve written a custom WP Dashboard plugin and widget that allows the user to define the start and end dates of a concert season easily. It uses its own table (say, myTable) for entries of concert seasons, e.g.

    id start_date end_date
    4 2015-01-25 2015-12-01

    What I would like to do in the QW query is to filter by the ACF custom field “concert_date” in my “concert” post type where concert_date is between the start_date and end_date returned by a subquery such as: SELECT start_date, end_date FROM myTable ORDER BY id DESC LIMIT 1.

    Any thoughts on how I could achieve this?

    Thanks!!

    https://wordpress.org/plugins/query-wrangler/

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Jonathan Daggerhart

    (@daggerhart)

    Hi diekunstderfuge,

    Thanks for the great use-case. I don’t have a perfect solution for you, but in the version 1.5.31 I added something that can help. A new callback filter.

    So you can add a new filter and use a callback to alter the query arguments before the query is built. Couple that with the codex on a date_query ( http://codex.wordpress.org/Class_Reference/WP_Query#Date_Parameters ) and it shouldn’t be too hard to achieve.

    Something like this should theoretically work.

    function my_custom_filter_callback($args, $filter){
    	$after = explode('-', '2015-01-25');
    	$before = explode('-', '2015-12-01');
    
    	$args['date_query'] = array(
    		'after' => array(
    			'year' => $after[0],
    			'month' => $after[1],
    			'day' => $after[2],
    		),
    		'before' => array(
    			'year' => $before[0],
    			'month' => $before[1],
    			'day' => $before[2],
    		)
    	);
    
      return $args;
    }

    Let me know how this goes. I’ll look into adding a date_query field in the future.

    Thanks,
    Jonathan

    Plugin Author Jonathan Daggerhart

    (@daggerhart)

    Quick follow up:

    I also added functionality to the “Post IDs” filter. It can now accept an array of post ids returned from a callback.

    Again, while this is not ideal, it should drastically increase your options for getting the results you want.

    Hi Jonathan,

    Thanks for your reply! Your code certainly works, but I’m a little unclear on where in the filter/callback to specify that it is a specific ACF field, i.e., meta_keyname, to which the callback should apply. In other words, how do I make sure it is the value of my custom field that falls between $before and $after, and not another WP date field, like post creation date or publish date? Do I need to do something with the $filter argument? Forgive me, I’m still getting used to WP development!

    Thanks again!

    Plugin Author Jonathan Daggerhart

    (@daggerhart)

    Hi diekunstderfuge,

    Ah, sorry, I wasn’t thinking right. You need a meta_query: http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

    function my_custom_meta_query_filter_callback($args, $filter){
    
    	$args['meta_query'] = array(
    		array(
    			'key' => '_my_custom_field',
    			'type' => 'DATE',
    			'compare' => 'BETWEEN',
    			'value' => array('2015-01-25', '2015-12-01')
    		),
    	);
    
    	return $args;
    }

    That should work for you, but note that the codex says: “The ‘type’ DATE works with the ‘compare’ value BETWEEN only if the date is stored at the format YYYY-MM-DD and tested with this format.”

    Let me know how it goes!

    Thanks,
    Jonathan

    Yes, that works, and thank you for pointing out that disclaimer in the codex; I immediately saw that ACF was storing my dates as YYYYMMDD instead of YYYY-MM-DD and I probably would have spent much longer hunting for the source of the problem.

    Thanks again for your help!

    Cheers!

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Filter by subquery?’ is closed to new replies.