WordPress.org

1. alzuwaga
   Member
   Posted 7 months ago #

   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

2. alzuwaga
   Member
   Posted 7 months ago #

   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.

   :D

3. jennybeaumont
   Member
   Posted 2 months ago #

   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

4. alzuwaga
   Member
   Posted 2 months ago #

   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: http://i.imgur.com/V1Pj7.jpg

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

5. jennybeaumont
   Member
   Posted 2 months ago #

   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

Reply

You must log in to post.