GoogleCodeExporter
commented
8 years ago Storing data structures as serialized strings or JSON-encoded strings (as I'm
doing) is fairly common. Note that this happens ONLY when you are storing data
from a "repeatable" field (e.g. a multi-select storing multiple values). In
version 0.9.5 (almost ready for public release), this same approach is used to
store repeatable image, text, or relation fields. It's used for all fields that
store an array of data (relational data or not).
I thought about the approach a long time, and maybe what I did isn't the best
way, but I kept running into problems with WP's architecture here, so I
consciously chose to avoid it. The reason that I specifically avoided WP's
approach is that it makes it nearly impossible to do certain database queries
-- my approach makes some queries more difficult (as you outlined), but it
becomes much easier to select an *entire post* including all of its custom
fields in one fell swoop. It's really hard to write MySQL queries that join
tables on an indeterminate number of rows. So if you have an array of custom
field values stored as multiple rows in the wp_postmeta table, you checkmate
yourself into requiring a separate database query for EACH custom field
involved, whereas if you have a single row with JSON data, then you can grab
all your custom field data via a single query that grabs the entire post data
and all custom field data via a single query. The approach became solidified
as I developed the Summarize Posts plugin, which offers a more thorough
database querying API than I could find in native WP functions.
The JSON format is actually queriable (whereas PHP serialized data is not).
E.g.
SELECT * FROM wp_postmeta WHERE post_id=123 AND meta_key='my_custom_field' AND
meta_value LIKE '%"7"%';
Note the quotes are included in the LIKE query. Since the wp_postmeta table is
not indexed, MySQL will be forced to do a table scan to find the rows (i.e.
it's slower query) -- so in other words, there's no disadvantage to doing a
"LIKE" query because the table needs to get scanned either way. I'm building
more functionality into the Summarize Posts plugin:
http://code.google.com/p/wordpress-summarize-posts/wiki/get_posts so you
should be able to handle most queries you can think of.
So the other thing that made me go for the JSON approach was that WP offers no
way of sorting the values. When you use the get_post_meta() function to
retrieve an array of values from the wp_postmeta table, you don't have any
control over the order that they come back out of the array, whereas with
approach, you can simply shuffle the input fields as they are stored, save the
form, and back in your templates, the order of the values updates (see this in
action in version 0.9.5).
So yeah... I don't know. I think I made the decision that was the lesser of 2
evils. Maybe it would have been better to stick to WP's approach and just suck
up the fact that I'd need to make multiple database queries, but I've had to do
that for some other projects (unrelated to WP), and the searching, pagination,
etc. just got to be completely untenable if you couldn't grab all your data in
a single query. I'm not the only guy who's used this solution: MODX uses this
for its custom fields, and I've spotted the same approach in parts of Drupal
and Expression Engine, so *somebody* thinks it's viable. haha
If you're really concerned about architecture, you probably wouldn't even be
using WordPress at all. One huge limitation (to me) is that WP offers no
built-in way of standardizing the custom fields, so those relationships are all
stored in the CCTM configuration, and that makes it impossible to know which
custom fields are available for any post or page, and this just makes the
frustrating scenario of requiring one additional database query for each custom
field that much worse.Original comment by fireproofsocks on 6 Dec 2011 at 5:19
- Changed state: InReview
- Added labels: Type-Review
- Removed labels: Type-Defect
Original issue reported on code.google.com by
b...@webcultured.comon 6 Dec 2011 at 4:55