order by meta_value serialized array

The question:

Hi I’m trying to pull posts using the wp query. Just one problem–I am trying to sort the posts alphabetically by a certain meta_value. Thing is that this meta_value is a serialized array. Is there an easy way to do this without having to unserialize it? Thanks!

a:1:{i:0;a:1:{s:7:"people";s:16:"Tanya Garca";}}

Btw, I cannot change the way the meta_value is being saved because I am using this plugin which automatically saves it serialized.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Is there an easy way to do this without having to unserialize it?

Not in any reliable way. You can certainly ORDER BY that value but the sorting will use the whole serialized string, which will give you technically accurate results but not the results you want. You can’t extract part of the string for sorting within the query itself. Even if you wrote raw SQL, which would give you access to database functions like SUBSTRING, I can’t think of a dependable way to do it. You’d need a MySQL function that would unserialize the value– you’d have to write it yourself.

Basically, if you need to sort on a meta_value you can’t store it serialized. Sorry.

If you can’t change how the value is stored you will need to pull your posts, loop through them to sort them, then loop through them again to display the sorted results.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment