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