Half-Elf on Tech

Thoughts From a Professional Lesbian

Tag: sql

  • Stopwords and Sort Queries

    Stopwords and Sort Queries

    The code I use is part and parcel from a comment Pascal Birchler made in 2015 and Birgir E. riffed on in 2016. I made one small change.

    The Problem

    People like to name TV shows with ‘The’ or ‘A’ or ‘An’ as the first word. “The Fall” and “The Good Wife” for example. However, when we order such things in a human sensible way, “A Touch of Cloth” should be listed behind both of those.

    • Frankenstein
    • The Fall
    • Grey’s Anatomy
    • The Good Wife
    • A Touch of Cloth

    WordPress, though, sees them as absolutes and you get this:

    • A Touch of Cloth
    • Frankenstein
    • Grey’s Anatomy
    • The Fall
    • The Good Wife

    Not quite right, is it?

    The Fix

    This code does two things.

    The first part is the filter on the posts_orderby function. That checks if the post type is the one I want to filter (in my case, only shows), and if so, use regex to filter out my stop words of ‘the ‘, ‘an ‘, and ‘a ‘. The extra space in each word is important. I want to reorder “The Fall” and not “Then They Fall” after all!

    The second part is the actual filter on the title, to mess with it only for the ordering of posts.

    add_filter( 'posts_orderby', function( $orderby, \WP_Query $q ) {
    if( 'post_type_shows' !== $q->get( 'post_type' ) )
        return $orderby;

    global $wpdb;

    // Adjust this to your needs:
    $matches = [ 'the ', 'an ', 'a ' ];

    return sprintf(
        " %s %s ",
        MYSITE_shows_posts_orderby_sql( $matches, " LOWER( {$wpdb->posts}.post_title) " ),
        'ASC' === strtoupper( $q->get( 'order' ) ) ? 'ASC' : 'DESC'
    );

}, 10, 2 );

function MYSITE_shows_posts_orderby_sql( &$matches, $sql )
{
    if( empty( $matches ) || ! is_array( $matches ) )
        return $sql;

    $sql = sprintf( " TRIM( LEADING '%s' FROM ( %s ) ) ", $matches[0], $sql );
    array_shift( $matches );
    return MYSITE_shows_posts_orderby_sql( $matches, $sql );
}

    If you’re using MariaDB, this can be even easier, but I have to test on my dev site, which uses MySQL.

  • Sortable Custom Columns: Taxonomies

    Sortable Custom Columns: Taxonomies

    You may have noticed that when I was making Sortable Custom Columns I didn’t make it so I could sort some of the fields.

    Showing sorted columns

    That’s because unlike my lovely post meta, those other fields are custom taxonomies. And by their very nature, they’re not sortable. In order to do this however you need some SQL.

    Make the Columns Sortable

    First we have to add our new columns (lez_gender and lez_sexuality) into our sortable function.

    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
	$columns['taxonomy-lez_gender']		= 'gender';	// Allow sort by gender identity
	$columns['taxonomy-lez_sexuality']	= 'sex';	// Allow sort by gender identity
    return $columns;
}

    Last time it only had the unset and the postmeta-roletype columns. Now we’re adding in our taxonomies as taxonomy-{TAXONOMYNAME} to that.

    Actually Sort the Content

    Now here’s the sneaky SQL stuff.

    function lez_gender_clauses( $clauses, $wp_query ) {
	global $wpdb;

	if ( isset( $wp_query->query['orderby'] ) && 'gender' == $wp_query->query['orderby'] ) {

		$clauses['join'] .= <<<SQL
LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
LEFT OUTER JOIN {$wpdb->term_taxonomy} USING (term_taxonomy_id)
LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
SQL;

		$clauses['where'] .= " AND (taxonomy = 'lez_gender' OR taxonomy IS NULL)";
		$clauses['groupby'] = "object_id";
		$clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
		$clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
	}

	return $clauses;
}
add_filter( 'posts_clauses', 'lez_gender_clauses', 10, 2 );

    I repeat this with ‘gender’ changed to ‘sex’ and ‘lez_gender’ changed to ‘lez_sexuality’ for the other taxonomy.

    I tested this on 921 posts in a custom post type and it didn’t make my server cry. Which is a bonus.