Distinction on meta value on pre_get_posts

1 Views

I’ve got a special “position” meta field for posts which can be between 0 and 37. 0 Is ignored, and everything else can have obviously any number between and including 1 and 37. But this of course means that multiple posts can have the same number.

Now I’m trying to select only the latest entry from these duplicates, however, I’m not having any luck. These are the hooks I’ve applied to achieve what I get at the moment:

Read More
add_action( 'pre_get_posts', 'foo_pre_get_posts' );
function foo_pre_get_posts( $query ) {
    if (!is_admin() && is_home() && is_a( $query, 'WP_Query' ) && $query->is_main_query() ) {
        $query->set( 'meta_key', '_position' );
        $query->set( 'posts_per_page', 37 );
        $query->set( 'meta_value', '0' );
        $query->set( 'meta_compare', '>' );
        $query->set( 'paged', 1 );
    }
}

// order the stuff on meta value and then post date since pre_get_posts can't do it.
add_filter("posts_orderby", "foo_posts_orderby");
function foo_posts_orderby( $orderby) {
    if (!is_admin() && is_home()) {
        return "wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC";
    }
    return $orderby;
}

add_filter("posts_groupby", "foo_posts_groupby");
function foo_posts_groupby( $orderby) {
    if (!is_admin() && is_home()) {
        return "wp_posts.ID, wp_postmeta.meta_value";
    }
    return $orderby;
}

The SQL generated: 

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 = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
        wp_postmeta.meta_key = '_position' AND
        CAST(wp_postmeta.meta_value AS CHAR) > '0'
    ) )
GROUP BY wp_posts.ID, wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

I tried a groupby, to no avail, tried playing around with DISTINCT directly in phpMyAdmin (copying the generated SQL of course), again, to no avail.

The results I get: 

[ ID ] [ position ] [ date ]
  1         1         12/08/2012
  4         1         07/08/2012
  5         1         06/08/2012
  6         2         08/08/2012
  9         2         07/08/2012
  8         5         09/08/2012

Note: this is just test data

How can I make it select only the first option based on position so something like this: 

[ ID ] [ position ] [ date ]
  1         1         12/08/2012
  6         2         08/08/2012
  8         5         09/08/2012

Thanks!

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. DISCLAIMER : Not a WordPress Expert, Just an old MySQL DBA

    Here is your original generated SQL 

    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 = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
        wp_postmeta.meta_key = '_position' AND
        CAST(wp_postmeta.meta_value AS CHAR) > '0'
    ) )
GROUP BY wp_posts.ID, wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

    What you are looking for is the minimum ID for any given position, perhaps something like:

    SELECT SQL_CALC_FOUND_ROWS  MIN(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 = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
        wp_postmeta.meta_key = '_position' AND
        CAST(wp_postmeta.meta_value AS CHAR) > '0'
    ) )
GROUP BY (wp_postmeta.meta_value+0);

  2. I found out I could just group it by wp_postmeta.meta_value on it’s own (without wp_posts.ID) and it worked! So it ended up becoming as follows.
    The PHP:

    add_filter("posts_groupby", "foo_posts_groupby");
function foo_posts_groupby( $groupby) {
    if (!is_admin() && is_home()) {
        return "wp_postmeta.meta_value";
    }
    return $groupby;
}

    And the SQL become:

    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 = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
        wp_postmeta.meta_key = '_position' AND
        CAST(wp_postmeta.meta_value AS CHAR) > '0'
    ) )
GROUP BY wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

    Worked like a charm!