[Plugin: Query Wrangler] Sorting by custom field and filtering by date

Hi studentrob,

I’ve added ‘sort by meta value’ to my features list for 1.6.

Filter by custom fields is already in 1.6dev.
You can get it here under ‘other releases’
https://www.ads-software.com/extend/plugins/query-wrangler/developers/

Cool. I wrote my own widget this time and used WP_Query, along with filters posts_where and posts_orderby to solve my problem.

I checked out 1.6 anyway because I was curious how you handled the filtering. I see the filters for meta_key and meta_value. I was expecting to see a filter for the specific field but this works just fine.

How does the meta_value filter work? In the box, is it expecting a particular value to equate? In my case I actually wanted to filter by a custom field that is a date, and use comparisons like > or <. I believe this would require a more advanced filter due to the way WP stores custom fields. So I would leave “filtering by custom field that is a date” as an open request for this plugin.

Since custom fields are all stored in the same table and column, the database does not know its type. Unless I am missing something like the type is stored in another table, it seems as if the database only knows them as varchars. I wonder why WP chose to store all custom fields in one table rather than creating new tables & columns like in Drupal. In the future I could see this causing problems with scalability too.

Anyway, in my case, since the custom date field I created is stored as unix time (seconds since epoch), I need to explicitly convert it in the filters.

Here are the orderby parameters for WP_QUery:

https://codex.www.ads-software.com/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

I used meta_value and meta_value_num because they are listed there on that page. Above you say you used post_orderby ? I’m not familiar with that parameter, could you give me a copy of your wp_query arguments so I can see what you did?

It sounds like in your case meta_value_num will work.

How to:
Create query
Add sort Meta Value (number).
Edit that new sort and in the text field type in the custom field’s key for your date (something like ‘custom_date’ or whatever you named it).
Voila, now your query is sorted by your timestamps ASC or DESC.

Since your dates are store as timestamps, they will naturally sort by meta_value_num correctly. You don’t have to convert them to a date to sort them, if I’m understanding you correctly.

Thanks for offering to take a look at my code! (FYI for anyone reading- this is not really relevant to the use of Query Wrangler)

Regarding posts_orderby — it is a filter. It allows you to append to or replace the order by expression in the executed query.

Before resorting to using the filters, I tried this,

$query = new WP_Query( array(
      'post_type' => 'event',
      'posts_per_page' => 3,
      'post_status' => 'publish',
      'meta_key' => 'wpcf-event-date',
      'meta_value_num' => time(),
      'meta_compare' => '>=',
      'orderby' => 'meta_value_num',
    ));

The SQL that is spit out is,

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'event' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'wpcf-event-date' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 3

In the above SQL, the comparison is not included and I do not see any PHP error indicating why. If I change the meta_value_num parameter above to be meta_value,

'meta_value' => time(),

then I get the below SQL which includes the comparison, however the meta_value comparison is done as a string which is no good,

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'event' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'wpcf-event-date' AND CAST(wp_postmeta.meta_value AS CHAR) >= '1345912223') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 3

Any ideas? Also, in query wrangler, is it possible to filter by a custom date field using > and < ? Thanks again!

In your first example, I don’t think you need to have a meta_value argument at all.

If i understand correctly, you’re wanting something more like:

$query = new WP_Query( array(
      'post_type' => 'event',
      'posts_per_page' => 3,
      'post_status' => 'publish',
      'meta_key' => 'wpcf-event-date',
      'orderby' => 'meta_value_num',
      'meta_query' => array(
        'key' => 'wpcf-event-date',
        'value' => time(),
        'compare' => '>=',
        'type' => 'NUMBER', // not positive about this, maybe TIME?
      ),
    ));

This *should get 3 event posts where the Custom Field ‘wpcf-event-date’ is >= time(), and sort those posts by ‘wpcf-event-date’ DESC (default, latest event first).

In QW there is filter for meta_compare, but you specifically need a meta_query filter, so I’ll create something like that.

I tried putting these for type: NUMERIC, DATE, DATETIME, and TIME

None worked. The SQL that is produced does not contain any comparison of the date field.

I don’t really understand the documentation for WP_Query with respect to filtering on custom fields. I do not see a distinction being made between using meta_value_num along with meta_compare vs. putting everything in meta_query.

Also, in the examples for date comparison (search for the text “Return posts 30 to 60 days old”), they use filter overrides and it seems sort of hackish but that is the only way I could get this to work.

Let me know if you get meta_query to work!

You’re right, meta_query doesn’t work… *sigh, I secretly hate WP.

If I were you I’d just use that working sql query directly and ignore the WP_Query class for what you need.

Sorry that didn’t work out better.

Well I am glad we are on the same page anyway =). Perhaps it is a bug or oversight that we can report somewhere.

Hi guys, I stumbled upon this thread looking for some answer on the same topic. I ended up adding a filter, wich is working ok so far. I took the idea from here:

https://wordpress.stackexchange.com/questions/23700/how-to-query-posts-using-meta-query-to-orderby-meta-key-and-have-a-secondary-sor

This is what I did:

add_filter(‘posts_orderby’, ‘dkis_sort_by_meta_value_asc’ );
function dkis_sort_by_meta_value_asc ( ) {
global $wpdb;
return ” $wpdb->postmeta.meta_value ASC”;
}

It’s quick and easy. You could implement a dkis_sort_by_meta_value_desc as well, and remember to remove the filter if it’s causing problems with other loops:

remove_filter(‘posts_orderby’, ‘dkis_sort_by_meta_value_asc’ );

hope it helps

Pretty sure that is the same thing I came up with in my second comment. The rest of this thread was just discussion with the author on the feasibility of adding sorting by a custom date field to the plugin GUI.