Author: Ipstenu (Mika Epstein)

Custom Columns Search: Not unique table/alias

After making Custom Sortable Columns everything was awesome, right?

Well… Mostly.

When I tried to sort a search, I got this:

WordPress database error: [Not unique table/alias: 'wp_postmeta']
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID 
FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 
AND (((wp_posts.post_title LIKE '%kate%') OR ( (wp_postmeta.meta_key 
IN ( 'lezchars_actor', 'lezshows_worthit_details', 'lezshows_plots', 
'lezshows_episodes', 'lezshows_realness_details', 'lezshows_quality_details', 
'lezshows_screentime_details' ) ) AND (wp_postmeta.meta_value LIKE '%kate%') ) 
OR (wp_posts.post_excerpt LIKE '%kate%') OR (wp_posts.post_content LIKE '%kate%'))) 
AND ( wp_postmeta.meta_key = 'lezchars_type' ) AND wp_posts.post_type = 'post_type_characters' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR 
wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR 
wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY 
wp_postmeta.meta_value DESC LIMIT 0, 20

Not good.

And not actually (really) related to the sortability! No no, I did this to myself by adding in optimized post-meta search. You see, that runs on the front and the back end of WordPress.

I knew it had to be the search tweaks I’d made when I saw this:

OR ( (wp_postmeta.meta_key IN ( 'lezchars_actor', 'lezshows_worthit_details', 
'lezshows_plots', 'lezshows_episodes', 'lezshows_realness_details', 
'lezshows_quality_details', 'lezshows_screentime_details' ) )

Right away I realized it had to be my custom queries both calling from wp_postmeta and, in doing so, stemming all over each other. Not good at all.

The fix was relatively simple. All I had to do was move the filters to run only if we’re not in wp-admin.

/**
 * Only run if we're NOT in the admin screen!
 */

if ( ! is_admin() ) {
	add_filter( 'posts_join', 'lezwatch_search_join' );
	add_filter( 'posts_where', 'lezwatch_search_where' );
	add_filter( 'posts_distinct', 'lezwatch_search_distinct' );
}

This meant that the internal post search wouldn’t query those fields, but in the grand scheme of things, that’s okay. Generally when I’m looking for a post in the search, I’m looking for it by title. If I wanted to be more particular, I could have it not use my custom joins when I’m on a post listing page, but allow it if I’m editing a post (so adding a new link would search all that content too).

For now, this is what I need.

21 September, 2016

Sortable Custom Columns
I have a lot of custom columns going on over at LezWatch TV. And there are a million tutorials on how to make your columns sortable. So here’s another one.

Backstory

I have a custom post type for TV characters, and the characters have extra meta data. The kind I want to show is the TV show (or shows) associated with the character and the role type.

I should note that ‘role type’ can be weird, since there are characters who will be guests on one show and regulars on the other, and really I should go back and change all of that but when you get to 1000 characters, you really don’t want to… Anyway. Let’s just do this. Keep in mind the following things.
1. The custom post type was registered as post_type_characters
2. The data I wish to list is ‘tv shows’ and ‘role type’
3. TV Shows is an array of IDs related to another post type (shows), stored in a meta key called lezchars_show
4. Role Type is a plain text value stored in lezchars_type
Now we’re ready to go

New Column Headers

We want to make a new column for your custom post types. For this to work, you need to know the name of your custom post type – post_type_characters remember – and the format for the column code – manage_{POSTTYPE}_posts_columns … See how that works?
```
// Add Custom Column Headers
add_filter( 'manage_post_type_characters_posts_columns', 'set_custom_edit_post_type_characters_columns' );
function set_custom_edit_post_type_characters_columns($columns) {
	$columns['cpt-shows']		= 'TV Show(s)';
	$columns['postmeta-roletype']	= 'Role Type';
	return $columns;
}
```
The name of the columns (cpt-shows and postmeta-roletype) are semi-arbitrary. I picked cpt-TYPE and postmeta-META because they were logical to me. I always use those formats.

Column Content

Giving the column content is a little more difficult because I’m listing TV shows associated with a character. Plural. And some characters are on three shows. Usually people are only on one, thankfully.

This time the function name is going to be based on manage_{POSTTYPE}_posts_custom_column
```
// Add Custom Column Content
add_action( 'manage_post_type_characters_posts_custom_column' , 'custom_post_type_characters_column', 10, 2 );
function custom_post_type_characters_column( $column, $post_id ) {
	// Since SOME characters have multiple shows, we force this to be an array
	if ( !is_array( get_post_meta( $post_id, 'lezchars_show', true ) ) ) {
		$character_show_IDs = array( get_post_meta( $post_id, 'lezchars_show', true ) );
	} else {
		$character_show_IDs = get_post_meta( $post_id, 'lezchars_show', true );
	}

	// Show Title is an array to handle commas
	$show_title = array();

	foreach ( $character_show_IDs as $character_show_ID ) {
		array_push( $show_title, get_post( $character_show_ID )->post_title );
	}

	switch ( $column ) {
		case 'cpt-shows':
			echo implode(", ", $show_title );
			break;
		case 'postmeta-roletype':
			echo ucfirst(get_post_meta( $post_id, 'lezchars_type', true ));
			break;
	}
}
```
And this results in this:

Making it Sortable

But as you’ve noticed, it’s not actually sortable. That is, if I have one character with no ‘role’, I have to surf through 47 pages until I find her. So the magic here is to make these columns sortable.

This time the filter is named manage_edit-{POSTTYPE}_sortable_columns and there’s some extra mess going on.
```
// Make columns sortable
add_filter( 'manage_edit-post_type_characters_sortable_columns', 'sortable_post_type_characters_column' );
function sortable_post_type_characters_column( $columns ) {
	unset( $columns['cpt-shows'] ); 	  // Don't allow sort by shows
	$columns['postmeta-roletype']	= 'role'; // Allow sort by role
    return $columns;
}
```
The column names are required from the previous steps, but the sort by being ‘role’ is something I made up. It doesn’t have to match anything from before however it has to be remembered, as we’re going to re-use it in a moment.

You see, I have to tell it what ‘sortable’ means by filtering pre_get_posts. I’m grabbing the meta value for lezchars_type and I’m going to tell it to order by the value. This happens to be alphabetical. If I was using a number, it would have to be meta_value_num instead of meta_value.
```
// Create Role Sortability
function post_type_characters_role_orderby( $query ) {
	if( ! is_admin() ) return;

	if ( $query->is_main_query() && ( $orderby = $query->get( 'orderby' ) ) ) {
    	switch( $orderby ) {
			case 'role':
				$query->set( 'meta_key', 'lezchars_type' );
				$query->set( 'orderby', 'meta_value' );
				break;
		}
	}
}
```
Remember how I said to remember we called the order-by ‘role’? Well this was why. The case check is on ‘role’ but also the meta_key is our key name.

And yes it works:
19 September, 2016
Review: Ninja Forms 3

I wanted to have this out last week, in time for their release of Ninja Forms 3, but with WordPress 4.6.1 dropping the day before and me breaking the site I use Ninja Forms on, well let’s just say my week ran away with me. See the previous post about how lesbians eat data for more.

Anyway. Ninja Forms 3. It took these guys a year to get it all right, and from where I sit, they did an amazing job. They managed to upgrade with minimal loss of data. And since we’re talking about forms, that’s a huge deal. You see, when you do a major overhaul of how forms are built, you’re changing how the data is stored. You’re also (possibly) changing the shortcakes in the posts where the forms are located.

Making matters ‘worse’ for Ninja Forms, they have add-ons. Now they have to make sure these hundreds of add ons will work with the new version of Ninja Forms, and you can bet that not every developer will be responsive. I know that one for a fact.

What they did was nothing short of phenomenal. You see, they ended up making two plugins and a migrator, wrapped it all up into one plugin, and released it. I cannot stress how incredibly hard that is. And they did it right and well.

Here’s how it worked. I had version 2.9.x and I pressed that happy upgrade button to go to version 3. Nothing changed. I had an alert telling me that “3 is coming!!” which I knew, so I clicked on the link and was taken to a page asking me if I wanted to upgrade. Hold on a second. I was asked to use the new version.

Right then and there I realized they’d not just included some 2.9.x stuff in the plugin as a fall back, no no. They had two plugins. That’s twice the work, but more to the point, they had an upgrader and a downgrade in there. I clicked upgrade, migrated my forms, and that was it. From the visitor’s end, nothing had changed, and that’s how we like it.

For me though, using it was a mind trip. The very first thing I noticed was that it took up my whole screen. Now I’m not a fan of that in general. I want WordPress to look like WordPress. I want access to all my things so I can right click and open a link in a new tab and multitask. And worse, most full screen WordPress tools don’t look like WordPress. The disparity of those kinds of changes bothers me a lot.

Shockingly, Ninja Forms felt like WordPress. It looked and felt like everything else in my dashboard. I’ve only seen this a few times, like with WooCommerce, where the ‘non’ WordPressy pages were still WordPress. This is hugely important to me. The more we make a consistent user environment for WordPress, the better we make the entire WordPress experience for our users.

I hadn’t messed much with Ninja Forms since I first set it up but now I had all sorts of fun things to play with. The drag and drop interface was as slick as it looked in the videos. This has reinvigorated my interest in things and I’ll be adding some more custom forms to the site soon.

Okay, so what don’t I like?

The colors don’t match my site. This is so random, I know, but I like to use different WP Admin themes for different sites. This site is purple, another is blue, and the one using Ninja Forms is 80s Kids – bright blue and pink. I wish that Ninja Forms picked up my admin colors and used that to make their interface.

I’m disappointed about tabbing as well. I can’t press tab and go between fields when I’m editing a form. I try to use my mouse as little as possible when I enter data. If I’m typing in information, like ‘term name’ and ‘value,’ then I want to press tab to jump to the next field.

Finally, and this is going to sound weird, I don’t like that the form editor makes the rest of their settings pages look plain. They did such a kick ass job, I look at the ‘regular’ settings and think they missed something. I know, it’s petty.

16 September, 2016
Lesbians Eat Data
The original title was “Lesbians Broke Jetpack” but it turned out to be even more complicated than all that. And thankfully more rare.

This concerns three things.

1) The website lezwatchtv.com
2) Jetpack for WordPress
3) ElasticSearch

On Sept 6th, Jetpack released a new version – 4.3 – and I promptly upgraded. When I did, I started getting weird emails from my server of a “Suspicious process running under user lezwatchtv” and the content looked like this:
```
Executable:

/usr/bin/php-cgi

Command Line (often faked in exploits):

/usr/bin/php-cgi /home/lezwatchtv/public_html/wp-cron.php


Network connections by the process (if any):

tcp: MYIP:39734 -> JETPACKIP:443
```
Being a proper code-nerd, I backed out a few things and tried again. Same error. I went into my process watcher and saw five processes calling wp-cron.php for that domain, but no others on the server. I killed the processes and turned off WordPress cron. Everything was fine. Then I installed WP Crontrol and manually kicked off cron jobs until it happened again.

The culprit was a ‘runs everyone one minute’ job by Jetpack, which struck me as bewildering.
```
		if ( ! wp_next_scheduled( 'jetpack_sync_cron' ) ) {
			// Schedule a job to send pending queue items once a minute
			wp_schedule_event( time(), '1min', 'jetpack_sync_cron' );
		}
```
The sync job is meant to update your data on Jetpack’s servers, which makes sense, and running every minute will copy up everything that changed in each minute. It seemed a little heavy to me, and disabling it stopped my run-away cron jobs. That meant the sync was failing. I reached out to a Jetpack tech and explained the situation. He re-ran the sync manually and it stalled.

We determined the likely issue was that the job was, for some reason, hanging and unable to finish, so it would just stay active forever. And ever. And since it would see that the sync had never done, it would start up all over again until, finally, my server killed the five (yes, five) processes and sent me an angry text about it. Yes, my server texts me.

At this point I emailed support with full details and got a very insightful reply from Brandon Kraft:

I’m interested in if there’s an issue with the server connecting with WP.com (seems unlikely given your other sites sound fine), if there’s a large amount of postmeta or something like that that is throwing a wrench into the system, or something to that effect. We’ve isolated some odd cases where when there is either a lot of postmeta or something yet undetermined in postmeta breaks things in a way similar to what you saw.

DING!

See there are 40 posts, 22 pages and then 1246 Custom Post posts on LezWatchTV.

906 posts are ‘characters’ and all characters have three separate taxonomies, two plain text post-meta values, and two serialized. 340 posts are ‘shows’ with two taxonomies, three plain text post-meta values, three integer (plain text) post-meta values, one true/false, six HTML, and one serialized data.

So if I was going to point at “a site with lot of weird post meta” I would pick this site.

I spent a few hours on the 7th (the day after the release) beta testing their 4.3.1 version. We tried a patch for the bug where full sync wasn’t giving up on wp error. That helped a little, but the error kept happening, limiting itself to two or three processes. I pointed to a special API, I ran some weird wp shell commands, and all we came up with was that at 190 or so ‘chunks’ out of 443, my server would stop sending messages to Jetpack’s servers.

Eventually I zipped up a copy of the theme and plugins and a sanitized DB (all secret information removed) and sent it over for them to play with. And they reproduced it! That was good. It meant it wasn’t my server, but it was my setup and the way Jetpack’s sync worked.

Like everything that has to sync, Jetpack plays the game between ‘sync it all super fast’ and ‘don’t kill the server.’ The way they sync the posts, they apply filters to render the content, including embeds. Because it does that with embeds, it triggers update_post_meta to update the _oembed_time_{long_base64_string} value, so it can know when to update the embed code for best caching.

Wasn’t I just talking about post meta the other day? Why yes! I was talking about optimizing post meta for search! The interesting thing about that is, since I’m using ElasticPress, it scans all my post meta for updates so it knows what to save as searchable data. That means when Jetpack triggers the update, it triggers ElasticPress, and all hell breaks loose.

But why did this happen now? Because I turned on “Sitemaps” for Jetpack. And when you enable (or disable) a Jetpack Module, it triggers a full sync. This happened to be the first time I’d done that since installing ElasticPress.

I did what any responsible person would do, and wrote this all up and submitted a bug report with ElasticPress. Sadly for now I’ve disabled ElasticPress until this can be resolved. I can probably turn it back on safely, since I won’t be triggering a full sync any time soon, but since I don’t want to accidentally crash things, I’ve left it off.

And how was your week?
14 September, 2016
Debugging cPanel’s Default Webpage
It started with a weird email from someone complaining that a 5 year old link was broken. They were trying to go to tech.ipstenu.org. I don’t, and haven’t used that since maybe 2011 or so. That was when I bought halfelf.org you see. I knew the domain should be forwarding, I set that up a million years ago, but for some reason it wasn’t. I told him the right URL and went back to puttering around.

But it bugged me, you know?

And later that day, half my domains started spazzing. It turned out they were still pointing to the ‘temporary’ name servers, ns3 and ns4. I cleaned up my DNS zones and rebuilt them (thank you Dan E. from Liquidweb) but for some reason it was still derping.

Now… as you know, I set up AutoSSL and Let’s Encrypt, like a good internet monkey.

In the middle of all this shit, I thought to myself ‘Self, I should fix having a subdomain as an add-on which I don’t need anymore now that we have this set up!’ I deleted store.halfelf.org as an add-on and put it back properly as a named subdomain.

Then I went and properly re-ran the AutoSSL check…

Errors:
```
3:43:30 AM WARN The domain “store.halfelf.org” has failed domain control validation (The system failed to fetch the <abbr title="Domain Control Validation">DCV</abbr> file at “<a href="http://store.halfelf.org/3712.BIN_AUTOSSL_CHECK_PL__.MREaLFbJJfusZuQX.tmp">http://store.halfelf.org/3712.BIN_AUTOSSL_CHECK_PL__.MREaLFbJJfusZuQX.tmp</a>” because of an error: The system failed to send an <abbr title="Hypertext Transfer Protocol">HTTP</abbr> “GET” request to “http://store.halfelf.org/3712.BIN_AUTOSSL_CHECK_PL__.MREaLFbJJfusZuQX.tmp” because of an error: SSL connection failed for store.halfelf.org: hostname verification failed .). at bin/autossl_check.pl line 449.
```
I read down and saw I had this error for ALL the bad domains. Coincidence? I think not. And neither do you, right? Right.

I did what you do and Googled and Googled and came across people saying that it was Sucuri (nope) or some other CloudFlare type firewall (nope), and then I thought about the crux of the error. “SSL connection failed” is a pretty distinct error, I felt. And of course the SSL connection failed, there wasn’t a certificate yet! So why was it trying to get to SSL right away?

And then I remembered … I have this in my .htaccess
```
# Force non WWW and SSL for everyone.
<IfModule mod_rewrite.c>
        RewriteEngine On

        RewriteCond %{HTTP_HOST} ^www\.(.*)$ [NC]
        RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

        RewriteCond %{HTTPS} off
        RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]
<IfModule mod_rewrite.c>
```
Which MEANS when it goes to http://store.halfelf.org, and doesn’t get the proper reply, it redirects to https which is the bad page that cPanel always does.

Oh yes.

Deleted those lines, re-ran AutoSSL, and it works.

Picard, Riker, and Worf facepalm.

Okay, smarty, what’s the real fix? Because as much as I want to leave this in place, I’ll have to remember to turn it off every time I add a new domain or subdomain to the system, and while that’s rare, it’s the rare cases that cause the most problems (thank you Herbert Hecht).

I looked back at the error and recognized the pattern being repeated: .BIN_AUTOSSL_CHECK_PL__. I saw it all over the place. I also knew that the folder AutoSSL puts down for LE is .well-known/acme-challenge (it’s in your web root). And I also knew this extra thing… I knew .htaccess

My new rule:
```
# Force non WWW and SSL for everyone.
<IfModule mod_rewrite.c>
	RewriteEngine On

	RewriteCond %{HTTP_HOST} ^www\.(.*)$ [NC]
	RewriteCond %{REQUEST_URI} !^/\d+\.BIN_AUTOSSL_CHECK_PL__\.\w+\.tmp$
	RewriteCond %{REQUEST_URI} !^/\.well-known/acme-challenge/
	RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

	RewriteCond %{HTTPS} off
	RewriteCond %{REQUEST_URI} !^/\d+\.BIN_AUTOSSL_CHECK_PL__\.\w+\.tmp$
	RewriteCond %{REQUEST_URI} !^/\.well-known/acme-challenge/
	RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]
<IfModule mod_rewrite.c>
```
Ironically, once I sorted all that out and understood I needed to whitelist things for AutoSSL and LE, I was able to Google and find an answer. cPanel knows about the issue and has a case open to fix it for everyone.

Still, I’m leaving that code in place for the one account that tends to add subdomains often enough that I would need this, and not-often enough that I’d remember.
12 September, 2016
What They Don’t Tell You

I wear a lot of hats in the Open Source World. I help teams. I represent and direct others. I herd the cats of software. I allow my name to be known. People talk about how we’re doing a good job, working hard, working together trying to make things better. They talk to you about the wonderful feeling of success that comes with releasing a product. They tell you about the joy, the friendships, and the community.

Well. Here’s what they don’t tell you.

They don’t tell you about the bad days.

They don’t tell you about the week you will spend being blamed and slandered and lied about in blog posts and on Social Media because people know half of thing.

They don’t tell you about the fact that you can’t speak up and defend your actions because it’ll make things worse.

They don’t tell you about the subtle misogyny that makes you wonder if it’s there at all.

They don’t tell you about the gut churning nausea you’ll feel about turning on your email and watching wave upon wave of hate-mails come in.

They don’t tell you about the dick pics and come ons.

They don’t tell you that even when you can explain yourself to your friends, you’ll have to make sure they know not to speak up on your behalf because it won’t help.

They don’t tell you that you can make it worse by being outspoken.

They don’t tell you that crying will make people feel they’re right.

They don’t tell you that people won’t even consider that their words cut you to your very bone.

They don’t tell you that even if a great many people respect you, it doesn’t make you feel any better.

They don’t tell you that someone will say ‘it’s all in your head.’

They don’t tell you that you will have to wait it out.

They don’t tell you that you will have to suffer.

They don’t tell you that the phrase “Just joking!” doesn’t ease the wounds.

They don’t tell you that even with all the support in the world, there are days you will feel absolutely, 100%, alone in your community.

All those good and wonderful things? They’re true. And I wouldn’t change the past if I could. Contributing to open source has enriched my life in many ways. It’s taught me more about myself that I could have imagined. It’s taught me how much I can stand and take though. It’s taught me that sometimes, somedays I will stand with my name and my work being spoken ill of, with my actions being second guessed and criticized, and I will have no succor or recourse.

I will have to stand there and take it and wait and say nothing and do nothing except the best I can do.

What’s the point of this? There isn’t one. This post isn’t a cry for help or a request for my friends to come to my defense. It’s a reminder for all of us that these things happen, and there will be days we feel worthless. Where we feel beaten down and angry and that we want to cry or do something and we just can’t because we know in our hearts it will make things worse.

But maybe the point is this.

I feel that way too. Everyone does.

So you’re not alone at all.

Comments on this post have been disabled.

9 September, 2016