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.

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:

{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.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

I would write a converter and separate all of these data once.

There is just no way to build a really fast query against serialized data.

share|improve this answer
    
What would be great to avoid is having to have to pull ALL the posts from the DB to sort, rather than just the x amount that are needed to display on that particular page (paginated). I imagine if there are many posts then it will become clunky to sort. But I don't think there's a way around that. Do you? –  mjjwatson Feb 3 '12 at 21:25
    
I would clean up this mess completely, not just a part of it. Once you have done this, you can use the API for sorting and all your data are consistent. You may need one hour to write the converter, another five minutes to run it – and then you will never have think about it again. –  toscho Feb 3 '12 at 21:33
    
Okay, I see your point. "The mess" is from the jigoshop plugin, which is a bit poor, imho. Not being able to be sort by price on a commerce platform? Bizarre. I'll be getting in the and sorting the mess out. Good call. –  mjjwatson Feb 3 '12 at 23:03
add comment

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.