logo
Ask your WordPress questions! Pay money and get answers fast! (more info)

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$30
Order by meta_key where value is serialized

I'd order post by meta_key but i've a problem with meta_value because is serialized object.

a:2:{s:5:"count";d:9750;s:7:"timeout";i:1358466733;}


I'd order my post per count value.

I use below code but order correctly until wp version 3.4.2 after update to 3.5 order by date:

if (have_posts()) :
$args=array(
'meta_key' => 'tweets_count',
'orderby' => 'meta_value',
'showposts' => 10,
);
query_posts($args);
while (have_posts()) : the_post();
//loop

This question has been answered.

microtag | 01/19/13 at 12:19pm Edit
Tutorial: How to assign prize money

Previous versions of this question: 01/21/13 at 6:39pm

(14) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    01/19/13
    1:30pm
    Arnav Joy says:

    is your last code was working but not after upgrade?

  • avatar
    Last edited:
    01/21/13
    12:08pm
    plovs says:

    Having your tweets meta info serialized is not a good idea. But you really are going to lose the ability to query your data in any efficient manner when serializing entries into the WP database.

    The overall performance saving and gain you think you are achieving by serialization is not going to be noticeable to any major extent. You might obtain a slightly smaller database size but the cost of SQL transactions is going to be heavy if you ever query those fields and try to compare them in any useful, meaningful manner.

    Instead, save serialization for data that you do not intend to query in that nature, but instead would only access in a passive fashion by the direct WP API call get_post_meta() - from that function you can unpack a serialized entry to access its array properties too.

    I recommend you unserialise your data and modify your save routine. Something similar to this should convert your data to the new format:


    if (have_posts()) :
    function filter_where($where = '') {
    global $wpdb;
    $where .= " AND post_date > '" . date('Y-m-d', strtotime('-1 day')) . "'";
    return $where;
    }
    add_filter('posts_where', 'filter_where');

    //New loop to cleanup the db, this should be added to the code that fetches your tweets
    //it will clean up your db, if here is nothing to clean, it will just continue
    $args = array(
    'meta_key' => 'tweets_count',
    'posts_per_page' => -1
    );

    $query = new WP_Query( $args );
    if($query->have_posts()){
    while($query->have_posts()){
    $query->the_post();
    $oldserial = get_post_meta($post->id,'tweets_count',true);
    add_post_meta($post->ID,'_tweets_count',$oldserial['count']);
    add_post_meta($post->ID,'_timeout',$oldserial['timeout']);
    delete_post_meta($post->ID,'tweets_count',$oldserial);
    }
    }
    //end new loop

    $args=array(
    'meta_key' => '_tweets_count',
    'orderby' => 'meta_value',
    'showposts' => 10,
    );
    query_posts($args);
    while (have_posts()) : the_post();
    include ( TEMPLATEPATH . '/loop.php' );
    endwhile;
    endif;


    Now it will work again as before.

    If you want to speed things up, you might use:

    - codex.wordpress.org/Transients_API
    - www.paulund.co.uk/cache-data-with-wordpress

    Previous versions of this answer: 01/21/13 at 12:08pm

  • avatar
    Last edited:
    01/19/13
    1:36pm
    microtag says:

    Yes, code works fine with 3.4.2 but do not works with 3.5 version

  • avatar
    Last edited:
    01/19/13
    1:39pm
    Arnav Joy says:

    can you share full code of your file?

  • avatar
    Last edited:
    01/19/13
    1:48pm
    microtag says:

     if (have_posts()) : 
    function filter_where($where = '') {
    global $wpdb;
    $where .= " AND post_date > '" . date('Y-m-d', strtotime('-1 day')) . "'";
    return $where;
    }
    add_filter('posts_where', 'filter_where');

    $args=array(
    'meta_key' => 'tweets_count',
    'orderby' => 'meta_value',
    'showposts' => 10,
    );
    query_posts($args);
    while (have_posts()) : the_post();
    include ( TEMPLATEPATH . '/loop.php' );
    endwhile;
    endif;

  • avatar
    Last edited:
    01/21/13
    6:05pm
    microtag says:

    Thanks plovs... but do not works fine.
    When I update page create new row in db and not order correctly.

    If I publish my code to save retweets on db could you change my system data storage?
    I would increase money from 10$ to 30$.
    Sorry for my bad eng.
    Thanks.

    attachment image expert uploaded image

  • avatar
    Last edited:
    01/21/13
    6:18pm
    plovs says:

    sure, just post the code that saves the tweets to the db, the api has probably changed, but we can figure out how to save it correctly to the database

  • avatar
    Last edited:
    01/21/13
    6:38pm
    microtag says:

    I use api 1.1 and works fine

    code (in function.php to store in database)

    require 'tmhOAuth.php';
    require 'tmhUtilities.php';

    //Creating the Custom Columns
    add_filter( 'manage_edit-post_columns', 'my_columns_filter' );
    add_action( 'manage_posts_custom_column', 'my_custom_column' );
    function my_columns_filter( $columns ) {
    $columns['tweet_count'] = 'Tweet Count';
    return $columns;
    }
    function my_custom_column( $column ) {
    global $post;

    if ( $column == 'tweet_count' ) {
    echo get_tweet_count( $post->ID );
    }
    }

    // Grabbing the Tweets Count/Caching the Values
    function get_tweet_count( $post_id ) {
    $meta = get_post_meta( $post_id, 'tweets_count', true );

    if ( isset( $meta['timeout'], $meta['retweet_count'] ) && time() < $meta['timeout'] )
    return $meta['retweet_count'];

    $string = get_post_meta($post_id, 'syndication_permalink', true );
    $sub = explode ( "/status/", $string );
    $retweet_count = 0;


    $tmhOAuth = new tmhOAuth(array(
    'consumer_key' => '**********************',
    'consumer_secret' => '**********************',
    'user_token' => '**********************',
    'user_secret' => '**********************',
    ));


    $data = $tmhOAuth->request('GET', $tmhOAuth->url('/1.1/statuses/show'), array(
    'id' => $sub[1],
    ));

    if ( ! is_wp_error( $data ) ) {
    $data = json_decode($tmhOAuth->response['response'], true);
    $retweet_count = $data['retweet_count'];
    }

    $meta = array(
    'retweet_count' => $retweet_count,
    'timeout' => time() + 3600
    );
    update_post_meta( $post_id, 'tweets_count', $meta );
    return $retweet_count;
    }


    In query_post I use
    get_tweet_count( $post->ID );
    and update retweets if timeout is expired.

  • avatar
    Last edited:
    01/21/13
    7:13pm
    plovs says:

    The problem is the last couple of lines:


    $meta = array(
    'retweet_count' => $retweet_count,
    'timeout' => time() + 3600
    );
    update_post_meta( $post_id, 'tweets_count', $meta );
    return $retweet_count;


    You use $meta, which is an array, but then put it in one $key, to make that fit it is serialized.

    Solution, replace those lines with:

    update_post_meta( $post_id, 'tweets_count', $retweet_count );
    return $retweet_count;


    If you are interested in the timeout, you can save that as well:

    $timeout= time() + 3600;
    update_post_meta( $post_id, 'tweets_count', $retweet_count );
    update_post_meta( $post_id, 'timeout', $timeout );
    return $retweet_count;

  • avatar
    Last edited:
    01/21/13
    8:08pm
    microtag says:

    Thanks.
    Now works, but I've a problem with timeout.
    If timeout is not expired value of retweets is update.

  • avatar
    Last edited:
    01/21/13
    8:33pm
    plovs says:

    Yes, sorry did not read the beginning of the function carefully, new version:


    $meta = array(
    'retweet_count' => $retweet_count,
    'timeout' => time() + 3600
    update_post_meta( $post_id, 'tweets_count', $retweet_count );
    return $retweet_count;
    );


    Now meta is set, and the tweets_count field is not serialized. Does it work as expected?

  • avatar
    Last edited:
    01/21/13
    8:56pm
    microtag says:

    I've correct

     );
    but retweets is continuous update every page refresh.
    Compared to the previous version, do not create meta_key timeout in database

  • avatar
    Last edited:
    01/21/13
    9:22pm
    plovs says:

    Ah, never hurry when answering, this is the complete function, could you test this, please?


    // Grabbing the Tweets Count/Caching the Values
    function get_tweet_count( $post_id ) {

    $retweet_count = get_post_meta( $post_id, 'tweets_count', true );
    $timeout = get_post_meta( $post_id, 'timeout', true );

    // this does not work
    // $meta = get_post_meta( $post_id, 'tweets_count', true );
    // if ( isset( $meta['timeout'], $meta['retweet_count'] ) && time() < $meta['timeout'] )
    // return $meta['retweet_count'];

    if ( isset( $retweet_count, $timeout ) && time() < $timeout )
    return $retweet_count;

    $string = get_post_meta($post_id, 'syndication_permalink', true );
    $sub = explode ( "/status/", $string );
    $retweet_count = 0;

    $tmhOAuth = new tmhOAuth(array(
    'consumer_key' => '**********************',
    'consumer_secret' => '**********************',
    'user_token' => '**********************',
    'user_secret' => '**********************',
    ));

    $data = $tmhOAuth->request('GET', $tmhOAuth->url('/1.1/statuses/show'), array(
    'id' => $sub[1],
    ));

    if ( ! is_wp_error( $data ) ) {
    $data = json_decode($tmhOAuth->response['response'], true);
    $retweet_count = $data['retweet_count'];
    }

    // This does not work
    // $meta = array(
    // 'retweet_count' => $retweet_count,
    // 'timeout' => time() + 3600
    // );
    // update_post_meta( $post_id, 'tweets_count', $meta );
    // return $retweet_count;

    $timeout= time() + 3600;
    update_post_meta( $post_id, 'tweets_count', $retweet_count );
    update_post_meta( $post_id, 'timeout', $timeout );
    return $retweet_count;

    }

  • avatar
    Last edited:
    01/21/13
    11:38pm
    microtag says:

    Works fine. Thanks a lot.

This question has expired.



microtag voted on this question.



Current status of this question: Completed



Please log in to add additional discourse to this page.





Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.