WordPress.org

Ready to get started?Download WordPress

Forums

Query Wrangler
Sorting by custom field and filtering by date (11 posts)

  1. studentrob
    Member
    Posted 2 years ago #

    Plugin looks great, thanks!

    I have a post type, 'event', that has a custom date field. I would like to be able to sort by this field but I do not see it in the sort menu. I would also like to be able to filter by date and custom fields.

    I used the plugin Types + this to create an event type and filter on it. Any other suggestions for implementing + querying on a simple event type are welcome. I come from Drupal so am used to that.

    Thanks,

    Rob

    http://wordpress.org/extend/plugins/query-wrangler/

  2. Jonathan Daggerhart
    Member
    Plugin Author

    Posted 2 years ago #

    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'
    http://wordpress.org/extend/plugins/query-wrangler/developers/

  3. studentrob
    Member
    Posted 2 years ago #

    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.

  4. Jonathan Daggerhart
    Member
    Plugin Author

    Posted 2 years ago #

    Here are the orderby parameters for WP_QUery:

    http://codex.wordpress.org/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.

  5. studentrob
    Member
    Posted 2 years ago #

    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!

  6. Jonathan Daggerhart
    Member
    Plugin Author

    Posted 2 years ago #

    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.

  7. studentrob
    Member
    Posted 2 years ago #

    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!

  8. Jonathan Daggerhart
    Member
    Plugin Author

    Posted 2 years ago #

    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.

  9. studentrob
    Member
    Posted 2 years ago #

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

  10. Daniel King
    Member
    Posted 1 year ago #

    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:

    http://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

  11. studentrob
    Member
    Posted 1 year ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags

No tags yet.