WP_Query should unserialize serialized data before matching

[Doggie52]

When storing an array (from a custom field, for example) in the database, it gets serialized. After having been serialized, there is to my knowledge no way to perform a meta_query with another (either serialized or unserialized) array and match the two. This can become a major hassle, as arrays are the preferred form of storing data for a host of different types of custom fields.

WP_Query should, preferably, check for serialized data and unserialize it before attempting to match against an array, should it not?

[scribu]

First of all: arrays are not the prefered way of storing data in custom fields; you can have multiple custom field values with the same key, for the same post. All the more reason if you plan on querying against those values.

Secondly, getting all the custom fields, unserializing them, and then looking for a match would be a really inefficient way to do it.

Instead, you can call maybe_serialize() on the value you are trying to match against before passing it to WP_Query.

[Doggie52]

Replying to scribu:

First of all: arrays are not the prefered way of storing data in custom fields; you can have multiple custom field values with the same key, for the same post. All the more reason if you plan on querying against custom field values.

Say I have a list of checkboxes - would I store their values all under one key? Comma separated? That sounds like much more of a hassle than simply storing them in an array. You mention storing them under one key - that would still make it an array (albeit one with only one entry) and would still cause it to become serialized, right?

Secondly getting all the custom fields, unserializing them and then looking for a match would be a really inefficient way to go about it.

I was rather thinking of checking if the input is an array, if the DB entry is serialized and only if both are, unserializing the DB entry for matching.

Instead, you can call maybe_serialize() on the value you are trying to match against.

If by "the value you are trying to match against" you mean the input array, I did call maybe_serialize() on that, but to no avail. The DB entry is being treated as a string and will only match if there is an exact match with the input array. I'd rather like to have the flexibility of choosing either an exact or partial match.

[dd32]

ou mention storing them under one key - that would still make it an array (albeit one with only one entry) and would still cause it to become serialized, right?

Almost, Metakeys are not unique. For example, You can store multiple meta items, eg:

Post ID MetaName  MetaValue
  1       test     value1
  1       test     value2
  1       test     value3

calling get_post_meta(1, 'test') would then return array(value1, value2, value3).

I was rather thinking of checking if the input is an array, if the DB entry is serialized and only if both are, unserializing the DB entry for matching.

Take a step back, The querying/matching is done within the Database, ie. within the SQL. SQL doesnt understand PHP's serialized format, therefor, all SQL see's is a long string..
In order to do what you're suggesting, It would require loading ALL meta keys into memory, unserializing them (well, some of them), and then implementing a Database-style comparison function in-WordPresss.. It's reinventing the wheel.

It may be that you're using the wrong storage mechanism, You might for example, be better off storing it in a Taxonomy to allow easier querying..

[Doggie52]

Replying to dd32:

ou mention storing them under one key - that would still make it an array (albeit one with only one entry) and would still cause it to become serialized, right?

Almost, Metakeys are not unique. For example, You can store multiple meta items, eg:

Post ID MetaName  MetaValue
  1       test     value1
  1       test     value2
  1       test     value3

calling get_post_meta(1, 'test') would then return array(value1, value2, value3).

Calling get_post_meta() for my above example works - it is the querying with meta_query that doesn't work. Would it work for your example? I assume it would, since the MetaValues would be stored as strings and not arrays, right?

I was rather thinking of checking if the input is an array, if the DB entry is serialized and only if both are, unserializing the DB entry for matching.

Take a step back, The querying/matching is done within the Database, ie. within the SQL. SQL doesnt understand PHP's serialized format, therefor, all SQL see's is a long string..
In order to do what you're suggesting, It would require loading ALL meta keys into memory, unserializing them (well, some of them), and then implementing a Database-style comparison function in-WordPresss.. It's reinventing the wheel.

Maybe I'm not understanding it correctly, but I really do not see how calling is_serialized() and if so, maybe_unserialize() on one value in the database is ineffective. I'm not saying the SQL should do this check, but rather PHP. When inputting an array as value in the meta_query array, WP_Query could simply check if the value in the database is serialized and if so, unserialize it to enable matching.

It may be that you're using the wrong storage mechanism, You might for example, be better off storing it in a Taxonomy to allow easier querying..

I will definitely look into that, having run into this wall of opposition :P .