WordPress.org

Ready to get started?Download WordPress

Forums

Order record by post meta value (13 posts)

  1. microtag
    Member
    Posted 1 year ago #

    //Displaying latest post per author on front page
      function filter_where($where = '') {
        global $wpdb;
        $where .= " AND vipwp_posts.id = (select id from {$wpdb->prefix}posts p2 where p2.post_status = 'publish' and p2.post_author = {$wpdb->prefix}posts.post_author order by p2.post_date desc limit 0,1)";
        return $where;
      }
      add_filter('posts_where', 'filter_where');

    I would like to modify the code above: Is it possible change the order of the record from date by meta_value?

    Example: CHANGE
    order by p2.post_date
    TO
    meta_key='name_key' ORDER BY meta_value

    Thanks

  2. vtxyzzy
    Member
    Posted 1 year ago #

    I do not have a way to test this, but it should be close to what you need. Replace the current $where with this:

    $where .= " AND vipwp_posts.id = (SELECT id FROM {$wpdb->prefix}posts p2, {$wpdb->prefix}postmeta pm
    where pm.object_id = p2.ID
    AND pm.meta_key = 'name_key'
    AND p2.post_status = 'publish'
    AND p2.post_author = {$wpdb->prefix}posts.post_author
    ORDER BY pm.meta_value ASC Limit 0,1)";

    I suggest you use phpMyAdmin or a similar tool to test the query before putting it into your function.

  3. microtag
    Member
    Posted 1 year ago #

    Thanks vtxyzzy,
    i've tried the function, but the posts are not displayed.

  4. vtxyzzy
    Member
    Posted 1 year ago #

    You will need to do some debugging. Just after the query, insert a print_r() call like this:

    global $wp_query;
    print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');

    Then copy the query from the screen and test it in phpMyAdmin to see if it gives any errors, or just no records.

  5. vtxyzzy
    Member
    Posted 1 year ago #

    I found one error: replace 'pm.object_id' with 'pm.post_id' in this line: 'where pm.object_id = p2.ID'

  6. microtag
    Member
    Posted 1 year ago #

    just no records:
    REQUEST:

  7. vtxyzzy
    Member
    Posted 1 year ago #

    Did you try replacing pm.object_id with pm.post_id?

    I am puzzled why no request showed up. Can you put the code for the file containing the query_posts() into a pastebin and post a link to it here?

  8. microtag
    Member
    Posted 1 year ago #

    Query:

    <?php
      //Displaying latest post per author on front page
      function filter_where($where = '') {
        global $wp_query;
    print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');
    
        $where .= " AND vipwp_posts.id = (SELECT id FROM {$wpdb->prefix}posts p2,
    {$wpdb->prefix}postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count'
    AND p2.post_status = 'publish'
    AND p2.post_author = {$wpdb->prefix}posts.post_author
    ORDER BY pm.meta_value ASC Limit 0,1)";
        return $where;
      }
      add_filter('posts_where', 'filter_where');
      ?>
    
    <?php
    $week = date('W');
    $year = date('Y');
    ?>
    <?php if (have_posts()) { ?>
    <?php
    	$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    	$args = array(
    	'posts_per_page' => 12,
    	'year' => $year,
    	'w' => $week,
    	'meta_key' => 'key_count',
    	'orderby' => 'meta_value',
    	'paged'=>$paged
    	);
    	query_posts($args);
    	while (have_posts()) { the_post();
    ?>
  9. vtxyzzy
    Member
    Posted 1 year ago #

    These statements go just after the call to query_posts(), not inside the function:

    global $wp_query;
    print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');
  10. microtag
    Member
    Posted 1 year ago #

    Sorry.

    REQUEST:SELECT SQL_CALC_FOUND_ROWS vipwp_posts.ID FROM vipwp_posts INNER JOIN vipwp_postmeta ON (vipwp_posts.ID = vipwp_postmeta.post_id) WHERE 1=1 AND YEAR(vipwp_posts.post_date)='2012' AND WEEK(vipwp_posts.post_date, 1 ) = '42' AND vipwp_posts.post_type = 'post' AND (vipwp_posts.post_status = 'publish' OR vipwp_posts.post_status = 'private') AND (vipwp_postmeta.meta_key = 'key_count' ) AND vipwp_posts.id = (SELECT id FROM posts p2, postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count' AND p2.post_status = 'publish' AND p2.post_author = posts.post_author ORDER BY pm.meta_value ASC Limit 0,1) GROUP BY vipwp_posts.ID ORDER BY vipwp_postmeta.meta_value DESC LIMIT 0, 12

  11. vtxyzzy
    Member
    Posted 1 year ago #

    If you look at this section of the request:

    vipwp_posts.id = (SELECT id FROM posts p2, postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count' AND p2.post_status = 'publish' AND p2.post_author = posts.post_author ORDER BY pm.meta_value ASC Limit 0,1)

    You can see that {$wpdb->prefix}posts is getting translated to posts. In other words, $wpdb->prefix is empty.

    I think you need to declare $wpdb global in your function.

  12. microtag
    Member
    Posted 1 year ago #

    Hi,
    now $wpdb global is declared, but shows only one post instead of 12 and the only post showing is wrong.

    REQUEST:SELECT SQL_CALC_FOUND_ROWS vipwp_posts.ID FROM vipwp_posts INNER JOIN vipwp_postmeta ON (vipwp_posts.ID = vipwp_postmeta.post_id) WHERE 1=1 AND YEAR(vipwp_posts.post_date)='2012' AND WEEK( vipwp_posts.post_date, 1 ) = '43' AND vipwp_posts.post_type = 'post' AND (vipwp_posts.post_status = 'publish' OR vipwp_posts.post_status = 'private') AND (vipwp_postmeta.meta_key = 'tweets_count' ) AND vipwp_posts.id = (SELECT id FROM vipwp_posts p2, vipwp_postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'tweets_count' AND p2.post_status = 'publish' AND p2.post_author = vipwp_posts.post_author ORDER BY pm.meta_value ASC Limit 0,1) GROUP BY vipwp_posts.ID ORDER BY vipwp_postmeta.meta_value DESC LIMIT 0, 12

  13. vtxyzzy
    Member
    Posted 1 year ago #

    As I said, I have no way to test this, but I think that you may need to change the way you match post_id. Try this:

    vipwp_posts.id IN (SELECT id FROM vipwp_posts p2, vipwp_postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'tweets_count' AND p2.post_status = 'publish' AND p2.post_author = vipwp_posts.post_author ORDER BY pm.meta_value ASC )

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.