How to restructure the categories of articles on MP-WP

First off,

SELECT p.ID, t.term_id FROM MPWP_postsⁱ p LEFT JOIN MPWP_term_relationshipsⁱⁱ rel ON rel.object_id = p.ID LEFT JOIN MPWP_term_taxonomyⁱⁱⁱ tax ON tax.term_taxonomy_id = rel.term_taxonomy_id LEFT JOIN MPWP_terms^iv t ON t.term_id = tax.term_id WHERE p.post_status="Publish" and p.post_title LIKE "%notated%";
> 66 rows in set (0.17 sec)

Thereby we select all articles which have "notated" in the title, because that's what I want to do -- get all the articles I adnotated moved into the newly-formed "Adnotations" category.

UPDATE MPWP_posts p LEFT JOIN MPWP_term_relationships rel ON rel.object_id = p.ID LEFT JOIN MPWP_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id LEFT JOIN MPWP_terms t ON t.term_id = tax.term_id SET rel.term_taxonomy_id = 43^v WHERE p.post_status="Publish" and p.post_title LIKE "%notated%";
> Query OK, 63 rows affected (0.16 sec)
> Rows matched: 66 Changed: 63 Warnings: 0

The reason it finds 66 but only changes 63 is that the past three articles I had put into the proper category directly, at the time of writing.

At this juncture we might notice that the post count for the category in question still stands at 3, so we have to hack that off^vi :

update MPWP_term_taxonomy set count = 66 where term_taxonomy_id=43;

And that'd be it. The same obviously applies if you want to similarily update any specific list of articles, for instance if you want to move all articles containing the string "cunt" you'd replace

WHERE p.post_status="Publish" and p.post_title LIKE "%notated%"

from above with a more adequate

WHERE p.post_status="Publish" and p.post_content LIKE "% cunt%^vii"

Enjoy! And if you find the time to fix the database destructure and submit a patch...

1. This is the main article table, it holds things such as the article content and title, the urls it has pinged and has yet to ping, date, author, etcetera.

   It also has a post_category record, which is not set by MP WP and doesn't do anything if you set it directly (ie, via your cmdline mysql link). Don't fucking ask me why, this thing is on the to-fix list since two thousand fucking eight. insufficient-hands.jpg [↩]

2. This is a long and thin table consisting of one entry for each MP-WP post (note that articles are only the Published posts ; the table also contains drafts, undergirthing the MP-WP compare/restore functionality) and its associated "term_taxonomy_id" (there's also a "term_order" but atm I dunno it's used for anything). Obviously enough this table is entirely spurious as such, "the term_taxonomy_id" could have just as well been the MPWP_posts.post_category directly. [↩]
3. This is a table holding its own id, then references to "term_id" (which, in a decade of Trilema, still exactly equals select term_taxonomy_id on all 43 records), "taxonomy" (which simply reads "category" throughout), "description", which is simply empty, "parent", which is null throughout, and "count", which holds the article count for the respective category. [↩]
4. This is another entirely spurious table, consisting of its own id (like in the case of MPWP_term_taxonomy, there's as many records as categories, ie here 43), a "name" (which is displayed by the blog), a "slug" (which is used for urls) and a "term_group", which is null. Obviously this is a mechanism to have categorical hierarchies, but in my opinion this is fucking stupid. Mind that I never used it ; Ballas used it in the sense of "tried it out", he has Clinical ; Clinical.Antipsychotics ; Clinical.Suicide nominally but he practically never used them either. I submit this then makes for perfect illustration as to the wisdom of NOT designing by design, and of designing by usage instead.

   Self-obviously these three tables could have been a single table, holding the name, the slug, no fucking description -- if you want to describe a category, put a fucking article in it, it's a very stupid idea to have ad-hoc and undocumented hierarchies of automatically pasted text (understood as "descriptions" rather than labels) -- then the article count associated and that's just about it. MPWP_posts.post_category could then link into it by id and you'd be done with the whole bloody mess. Aaanyways. [↩]

5. For the purpose of this exercise we magically know our new category has id 43, hurr. [↩]
6. Which yes, means that all other counts are now fucked, but whatever.

   If you actually give a shit, you can always count the records from MPWP_term_relationships by the term_taxonomy_id = magic number criterion, and then update the MPWP_term_taxonomy record accordingly. [↩]

7. Note the leading space -- it's true there don't exist any other English words ending in cunt, but maybe you misspelled account or something, I dunno, it's your blog. [↩]