Pay money and get answers for your WordPress questions (more info)

Order by meta_key where value is serialized

  • SOLVED

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

Answers (2)

2013-01-19

plovs answers:

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:

- [[LINK href="http://codex.wordpress.org/Transients_API"]]codex.wordpress.org/Transients_API[[/LINK]]
- [[LINK href="http://www.paulund.co.uk/cache-data-with-wordpress"]]www.paulund.co.uk/cache-data-with-wordpress[[/LINK]]


microtag comments:

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.