0

I want to add some table columns to the wp_terms table. Is it safe to use the dbDelta function with a modified CREATE-MySQL query for this? Or will I run into problems in the long turn? Is it update safe?

As a database rookie I hope someone with more experience can help me out here.

Background

For a plugin I'm currently working on, I need to add some metadata to a taxonomy term. The first thing is an ordering number and the second is the type of the term. Both are used to sort the posts in the frontend.

In the WordPress codex I've found the action hooks {taxonomy}_add_form_fields and {taxonomy}_edit_form_fields for this, but unfortunately these saves the metadata in the wp_options table as a serialized array. It's probably possible to use the WHERE REGEXP syntax to query the correct option, but this seems slow and a bit hackish.

Performance matters in the project (A ton of posts assigned to a ton of taxonomy terms) and therefore I'm looking for a way to sort and order the resultset directly on the database layer.

Thanks in advance Roman

  • I'm a db rookie too. However, in this case I've created my own _termmeta table and used Metadata_API + WP_Meta_Query for all needs. Yes, a meta query is slower but see the site break after an update is worse. I'm not saying an update will break your site, I'm saying that I, for me, would be scared of this :) – gmazzap Aug 28 '13 at 2:25
  • @G. M. Thank you for your comment. Isn't the Metadata API only for posts, comments and users? Or did you mean I should store the term metadata in the wp_postmeta table? – rofflox Aug 28 '13 at 7:44
  • 1
    Metadata API can be used for custom tables too. Cite from codex: "Some desired $meta_types do not come with pre-installed WordPress tables, and so they must be created manually" – gmazzap Aug 28 '13 at 8:03
  • Didn't know that, thank you. I will try it out! – rofflox Aug 28 '13 at 8:07
3

dbDelta is squirrelly. Some things it will alter, others it will not. For example, and if I remember correctly, you can add indexes to tables but not change those indexes or delete them. I honestly don't know if it will remove table columns. (My guess it that it won't but I may test that later.)

Nonetheless, I would not do something as critical as add columns of data to a Core table. Even if dbDelta leaves those columns alone, WordPress may (please please please) eventually adopt a more flexible database tool and that might remove your columns. Don't do it.

If you can't use the options table or postmeta or another table as it is intended to be used, create your own meta table for the taxonomies. WordPress, and dbDelta will leave that alone. Of course, you will have to deal with the SQL to retrieve that data but you were looking at having to do that anyway.

  • Thank you for your comment! I've already thought about using my own table, but this would require a lot more work for the CRUD part of the application. I will leave the question open for a while to hear other peoples opinions. – rofflox Aug 28 '13 at 7:38
  • WordPress won't know how to deal with your extra columns so you are already looking at roughly the same work, just different work. – s_ha_dum Aug 28 '13 at 11:51
  • You're completely right. I've misunderstood your last paragraph. I thought you mean I should create my own wp_terms table and not only a wp_termmeta table. Sorry for that. – rofflox Aug 28 '13 at 12:12
  • :) Yes, no need to re-create the entire terms system. – s_ha_dum Aug 28 '13 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.