Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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:

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!

share|improve this question

2 Answers 2

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);
share|improve this answer
    
Thanks for the answer, unfortunately I wasn't able to figure out how to edit the SELECT clause without regex as I have no idea what WordPress may do in the future, so I don't really trust simple regexes for such a complex language. In the end I found out I could just group it by wp_postmeta.meta_value on it's own (without wp_posts.ID) and it worked! –  Hosh Sadiq Aug 12 '12 at 2:17
up vote 1 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.