Order by value in serialized custom field

Quite a pesky problem here. I’ve taken over a site that contains multiple information, serialised, within a custom field. For example:

if ($new_query->have_posts()) : 

            while ($new_query->have_posts()) : $new_query->the_post(); 

                $ids[] = get_post_custom( get_the_ID() );
                print_r($product_custom_fields['item_data']);

            endwhile;

        endif;

Would return something along the lines of:

Read More
{i:0;s:0:"";s:13:"regular_price";s:4:"1000";s:10:"sale_price";s:0:"";s:6:"weight";s:0:"";s:10:"tax_status";s:7:"taxable";s:9:"tax_class";s:0:"";s:12:"stock_status";s:7:"instock";s:12:"manage_stock";s:3:"yes";s:10:"backorders";s:2:"no";} 

I’d like to be able to run a query that orders all of these items by ‘regular_price’ but cannot determine the best way to achieve this.

I currently have decided to run a preliminary query:

$new_query = new WP_Query( $args );

        if ($new_query->have_posts()) : 

            while ($new_query->have_posts()) : $new_query->the_post(); 

                $ids[] =  get_the_ID();

            endwhile;

        endif;

This gets all the correct items into the array $ids. I can then run a function which will sort these IDs into the order I want, by mucking about with the serialized data. I can then run:

 query_posts(array('post__in' => $ids) );

Which will filter my main results by the ids and order I have set up.

The trouble being, this queries the posts twice, and potentially is a bit longwinded.

If anyone can suggest a more succinct method, maybe with direct SQL injection into the query, it would be much appreciated.

Related posts

Leave a Reply

1 comment