Order post type by category and taxonomy term

  • This can not be done with wp_query, so I’m looking for alternatives. A query like this is what I guess I need:

    SELECT
      wp_posts.ID, wp_term_taxonomy.term_taxonomy_id, wp_term_taxonomy.taxonomy, wp_terms.name, wp_term_taxonomy_1.taxonomy, wp_terms_1.name
FROM
    (((((wp_term_relationships INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id)
    INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID)
    INNER JOIN wp_term_relationships AS wp_term_relationships_1 ON wp_posts.ID = wp_term_relationships_1.object_id)
    INNER JOIN wp_term_taxonomy AS wp_term_taxonomy_1 ON wp_term_relationships_1.term_taxonomy_id = wp_term_taxonomy_1.term_taxonomy_id)
    INNER JOIN wp_terms AS wp_terms_1 ON wp_term_taxonomy_1.term_id = wp_terms_1.term_id
WHERE
     (((wp_term_taxonomy.taxonomy)="category")
     AND ((wp_term_taxonomy_1.taxonomy)="tipo")
     AND ((wp_posts.post_status)="publish")
     AND ((wp_posts.post_type)="alojamiento"))
ORDER BY
      wp_terms.name, wp_terms_1.name
;

    I tried to do with a hook on posts_join, like this….

    add_filter('posts_join', 'mi_join' );

function mi_join( $join ){
	global $wpdb;

	$join .= " (((((wp_term_relationships INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id)
    INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID)
    INNER JOIN wp_term_relationships AS wp_term_relationships_1 ON wp_posts.ID = wp_term_relationships_1.object_id)
    INNER JOIN wp_term_taxonomy AS wp_term_taxonomy_1 ON wp_term_relationships_1.term_taxonomy_id = wp_term_taxonomy_1.term_taxonomy_id)
    INNER JOIN wp_terms AS wp_terms_1 ON wp_term_taxonomy_1.term_id = wp_terms_1.term_id";

	return str_replace('FROM wp_posts', 'FROM', $join);
}

    … but I can not get rid of the original ‘FROM’ clause (the str_replace part of de return in function).

    Any clues? Thanks

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter alzuwaga

    (@alzuwaga)

    Well, I just used $wpdb to run a query like the one above. Thanks anyway.

    And while we’re here… why no one answers my posts? I do not ‘bumps’, I’m good and yet unanswered. I feel I can write anything, I’m like invisible here.

    ??

    Jenny Beaumont

    (@jennybeaumont)

    Hi alzuwaga,

    Sorry you’re feeling so alone in here, I think it’s really hit or miss depending on what you’re writing about and who’s available at the time ??

    In any case, I could really benefit from this bit of code you’ve worked out. Do you think you could share the final work?

    thnx!
    -jennyb

    Thread Starter alzuwaga

    (@alzuwaga)

    Hi jenny, at last I can talk to someone here!
    The “final work”… hmmm… is still in progress. But the idea is to make a custom query using $wpdb like this:

    <?php
my_custom_get_terms( 'tipo', 'global', true );
$tipo = $_GET['tipo'] == '' ? '' : $_GET['tipo'];

//comienzan los alojamientos no destacados GLOBAL.
$datos_SQL = "
		SELECT
			  wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.name AS localidad, wp_terms.slug AS localidad_slug, wp_term_taxonomy_1.taxonomy, wp_terms_1.name AS tipo
		FROM
			(((((wp_term_relationships INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
			INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id)
			INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID)
			INNER JOIN wp_term_relationships AS wp_term_relationships_1 ON wp_posts.ID = wp_term_relationships_1.object_id)
			INNER JOIN wp_term_taxonomy AS wp_term_taxonomy_1 ON wp_term_relationships_1.term_taxonomy_id = wp_term_taxonomy_1.term_taxonomy_id)
			INNER JOIN wp_terms AS wp_terms_1 ON wp_term_taxonomy_1.term_id = wp_terms_1.term_id
		WHERE
			 wp_term_taxonomy.taxonomy='category'
			 AND wp_term_taxonomy_1.taxonomy='tipo'
			 AND wp_posts.post_status='publish'
			 AND wp_posts.post_type='alojamiento'";

	if($tipo != ''){
		$datos_SQL .= " AND wp_terms_1.name = '" . $tipo . "'";
	}

$datos_SQL .= " ORDER BY localidad, tipo";

$datos = $wpdb->get_results($datos_SQL);
//var_dump($post);
//print_r($wpdb->queries);

if ( $datos ) :

	foreach ( $datos as $dato ) :
		// do whatever you need
	endforeach;

else:
	echo "Nothing found";
endif;
?>

    Look at this line: $datos_SQL .= ” ORDER BY localidad, tipo”;

    localidad is an alias of wp_terms.name (the category)
    tipo is an alias of wp_terms_1.name (the taxonomy term)

    Here you have an image with the table relationships: https://i.imgur.com/V1Pj7.jpg

    I hope you understand me because my inglish is not too good.

    Jenny Beaumont

    (@jennybeaumont)

    Your ingles is just fine ??
    And thanks for sharing this…I’ve actually taken off in a different direction, and am now trying to accomplish what I need through RSS feed parsing. Not easy either – seems like every solution I find has some set back or another.
    But I persevere….
    good luck!
    -jennyb

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Order post type by category and taxonomy term’ is closed to new replies.