Support » Plugin: Pods - Custom Content Types and Fields » Get related pods using taxonomies

  • Resolved isoto69

    (@isoto69)


    Hello, we tried to create a single page Post for a POD in a single-{post_type}.php in our WordPress Template. In there we need to show related content using taxonomies. We have the following PODS:

    • artist
    • music
    • videos

    and the following Taxonomies in common:

    • album-artist
    • genres
    • hashtag

    Then, we want to retrieve the related music and video PODS with the same taxonomies values of the artist. Bellow, you find attached the code:

    <?php /* Initiate the Pods Object */
        
    	// get the current slug
        global $post;
      	// get pods object 
      	$mypod = pods( $post->post_type, $post->ID );
        $artistID = $mypod->id();
        $title = $mypod->display('title');
      	$permalink = $mypod->display('permalink');
        $imagehtml = get_the_post_thumbnail( $artistID, 'thumb', array( 'class' => 'thumbnail' ));
        $name= $mypod->field('name');
    ?>
    
    <?php get_header(); ?>
    	<div>
    		<h1> <?php echo $name; ?></h1>
    		 <div class="entry-thumb"><?php echo $imagehtml; ?></div>
    		 <?php
    			/* Add Taxonomy */
    			$hashtag = get_the_term_list($post->ID,'hashtag',"Hashtag: ", " / ","");
    			$genre = get_the_term_list($post->ID,'genre',"Generos: ", " / ","");
    			$artists = get_the_term_list($post->ID,'album-artist',"Artistas: ", " / ","");
    		?>
    		<p class="categories"><?php echo $hashtag; ?></p>
    		<p class="categories"><?php echo $genre; ?></p>
    		<p class="categories"><?php echo $artists; ?></p>
    	</div>
    
    	<div class="entry-content">
    	<?php
    		$params = array( 
    			'orderby' => 't.post_date DESC',     
    			'limit' => 5, 
    			'where' => 'album-artist.name IN ('.get_the_term_list($post->ID,'album-artist',"\"", "\", \"", "\")")
    			); 
    		$pods_musica = pods( 'music', $params ); 
    	?>
    	
    	<?php if (! empty( $pods_musica )) { ?>
    		<h3>Music Related:</h3>
    		<ul id="list">
    		<?php
    		  foreach ( $pods_musica as $music ) {
    			$id = $music[ 'ID' ];
    			$title = $music[ 'post_title' ];
    			echo '<li><a href="' .esc_url(get_permalink( $id )). '">' .get_the_title( $id ). '</a></li>';
    		  }
    		  echo '</ul>';
    		
    		} 
    		?>
    	</div>	
    <?php get_footer();  ?>

    After that, we get the folowing error: Database Error; SQL

    Then we test the query in phpmyAdmin and its works. Here the SQL:

    SELECT DISTINCT t.* FROM wp_posts AS t LEFT JOIN wp_term_relationships AS rel_artist ON rel_artist.object_id = t.ID LEFT JOIN wp_term_taxonomy AS rel_tt_artist ON rel_tt_artist.taxonomy = 'artist' AND rel_tt_artist.term_taxonomy_id = rel_artist.term_taxonomy_id LEFT JOIN wp_terms AS artist ON artist.term_id = rel_tt_artist.term_id WHERE ( (artist.name IN ("Bad Bunny") ) AND (t.post_type = "music") AND (t.post_status IN ( "publish" ) ) ) ORDER BY t.post_date DESC, t.menu_order, t.post_title, t.post_date LIMIT 0, 5

    The error is in the line of $pods_musica = pods( ‘music’, $params );

    Could you help us understand what the error is if the SQL works well?

    Thanks in advance.

    Best Regards

    • This topic was modified 2 months, 1 week ago by  isoto69.
    • This topic was modified 2 months, 1 week ago by  isoto69. Reason: Fixing the format in the code examples
Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Jim True

    (@jimtrue)

    You didn’t actually give us the SQL Error, just your SQL. Can you provide that?

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    Also the relationship field name is it really album-artist? Or did you mean to write it as album_artist in the $params?

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    The get_the_term_list() response the way you’re using it is NOT sanitized properly before getting put into the database query. Be sure to use get_the_terms() https://developer.wordpress.org/reference/functions/get_the_terms/ and build the output that way. You can use pods_sanitize( $term_name ) for each term name you want to insert into the WHERE query.

    Hi All, now it works!! Scott you right the problems was the function get_the_term_list(). I changed the code to:

    /*************************************/
    /*       Music Related Content       */
    /*************************************/
    
    //Params used in WHERE
    $taxonomy_list = get_the_terms( $post->ID, 'album-artist' );
    $taxonomy_string = "'".join("', '", wp_list_pluck($taxonomy_list, 'name'))."'";
    
    $params = array( 
    	'orderby' => 't.post_date DESC',     
    		'limit' => 5, 
    		'where' => "album-artist.name IN (".$taxonomy_string.")"
    	); 
    
    $MySubPOD = pods( 'music', $params ); 

    Thanks for your support.

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    Be sure to use pods_sanitize before it goes into the quotes:

    $taxonomy_string = "'" . join( "', '", pods_sanitize( wp_list_pluck( $taxonomy_list, 'name' ) ) . "'";

    It’s still not the *best* way to generate query strings, this would be the correct way: https://developer.wordpress.org/reference/classes/wpdb/prepare/

    Where you could build the query string for 'where' using $wpdb->prepare( $sql, $term_names ) and have $sql contain the list of %s placeholders. But pods_sanitize() does a pretty close representation of that (does not support everything prepare supports, it treats everything like a string and prepares them as such)

    Scott, I tried this like you sugested:
    $taxonomy_string = "'" . join( "', '", pods_sanitize( wp_list_pluck( $taxonomy_list, 'name' ) ) . "'";

    But doesn’t work, returns the following error:

    This page isn’t working elgenerotv.ivansoto.com.ve is currently unable to handle this request.
    HTTP ERROR 500

Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.