WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Display terms from taxonomy B only if has relationship with a taxonomy term A (6 posts)

  1. dsemblano
    Member
    Posted 1 year ago #

    Hi all,

    I've googling for a solution for this issue and found nothing: I have some posts from a Custom Post Type (Restaurants) and its taxonomies ('city' and 'cuisine'). Whenever I create a restaurant post, I can set many 'cuisine' terms as I want, but 'city' is single selected. I've also made a custom search using a html dropdown which I can search from 'restaurants' posts by filtering from its taxonomy terms.

    The search works well but what I really want is populate the second taxonomy (cuisine) only with terms that has some relationship based on the first taxonomy chosen (city). In the first dropdown, if someone choose the city 'New York' the second dropdown must display only cuisine terms that has relationship with 'New York' city term on restaurant posts. If there's not a post with 'brazilian' cuisine and 'New York' city registered, the 'brazilian' term must not appear on second dropdown. Of course, the cuisine dropdown doesn't have to display duplicated terms.

    Is that possible? Thanks in advance!

  2. vtxyzzy
    Member
    Posted 1 year ago #

    It should be possible by using the proper joins in the query that populates the second dropdown. Can you post the query you are currently using?

  3. dsemblano
    Member
    Posted 1 year ago #

    Hi vtxyzzy, thank you for replying, any help is appreciate. I'm not yet using a raw query, I'm just using a custom function in my functions.php. This function creates two dropdowns (city and cuisine terms) and populate (via ajax) the second dropdown with cuisine terms. The terms I didn't translate, so 'cidade' means 'city and 'cozinha' means 'cuisine':

    function restaurant_search( $taxonomy ) {
    	?>
    	<script type="text/javascript">
    		jQuery(document).ready(function( $ ) {
    			$('#cidade').change(function(){
    				$.ajax({
    					url:"<?php bloginfo('wpurl'); ?>/wp-admin/admin-ajax.php",
    					type:'POST',
    					data:'action=category_select_action&name=' + name,
    					success:function(results)
    					{
    					$("#cozinha").html(results);
    					}
    				 });
    			});
    		});
    	</script>
    
    <form action="<?php echo home_url('/'); ?>" role="search" method="get">
    	<?php // First dropdown
    		$term_selected_cidade = get_terms('cidade');
    		echo '<select id="cidade" name="cidade">';
    		echo '<option disabled="disabled" selected="selected">Cidade</option>';
    		foreach ($term_selected_cidade as $term) {
    			echo '<option value=' . $term->slug . '>' . $term->name . '</option>';
    		}
    		echo '</select>';
    	?>
    
    	<!-- Second Dropdown -->
    	<select name="cozinha" id="cozinha" ><option>Escolha uma cidade</option></select>
    	<input type="submit" value="buscar" />
    </form>
    <?php }
    
    function implement_ajax() {
    	// Populates the second dropdown with cuisine terms
    	$term_selected_cozinha = get_terms('cozinha');
    	echo '<select id="cozinha">';
    	echo '<option disabled="disabled" selected="selected">Cozinha</option>';
    	foreach ($term_selected_cozinha as $term) {
    		echo '<option value=' . $term->slug . '>' . $term->name . '</option>';
    	}
    	echo '</select>';
    }
    
    add_action('wp_ajax_category_select_action', 'implement_ajax');
    add_action('wp_ajax_nopriv_category_select_action', 'implement_ajax');
  4. dsemblano
    Member
    Posted 1 year ago #

    I'm trying to figure out the query, so I've got only half the way:

    SELECT * FROM wp_terms AS wt
    INNER JOIN wp_term_taxonomy AS wtt ON wt.term_id=wtt.term_id /* Juntando tabela de taxonomias */
    INNER JOIN wp_term_relationships AS wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id /* Juntando tabela de relationships */
    LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
    WHERE taxonomy='cidade' AND slug='recife'
    ORDER BY name
  5. vtxyzzy
    Member
    Posted 1 year ago #

    I think I have set this up the way you need it, but I am not entirely sure.

    The query is constructed of two parts. The 'inner' part selects the ID of all posts with the taxonomy of 'cidade' and the slug of the current city.

    The 'outer' part then selects all of the terms in 'cozinha' which have a post whose ID is in the set selected by the 'inner' part.

    SELECT DISTINCT wt.name
    FROM wp_terms AS wt
    INNER JOIN wp_term_taxonomy AS wtt ON wt.term_id=wtt.term_id /* Juntando tabela de taxonomias */
    INNER JOIN wp_term_relationships AS wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id /* Juntando tabela de relationships */
    LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
    WHERE taxonomy='cozinha'
    AND wp.ID IN (
       SELECT wp.ID FROM wp_terms AS wt
       INNER JOIN wp_term_taxonomy AS wtt ON wt.term_id=wtt.term_id /* Juntando tabela de taxonomias */
       INNER JOIN wp_term_relationships AS wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id /* Juntando tabela de relationships */
       LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
       WHERE taxonomy='cidade' AND slug='new-york'
    )
    ORDER BY name
  6. dsemblano
    Member
    Posted 1 year ago #

    Sorry for the delay in replying. Thank you very much indeed vtxyzzy! This is exactly what I was looking for, the query works like a charm!

    Here is how the final code became in functions.php, if any kind of help for further reference:

    function restaurant_search( $taxonomy ) {
    	?>
    	<script type="text/javascript">
    		jQuery(document).ready(function() {
    			$('#cidade').change(function(){
    			var cidade=jQuery('#cidade').val();
    				$.ajax({
    					url:"<?php bloginfo('wpurl'); ?>/wp-admin/admin-ajax.php",
    					type:'POST',
    					data:'action=category_select_action&name=' + cidade,
    					success:function(results)
    					{
    					$("#cozinha").html(results);
    					}
    				 });
    			});
    		});
    	</script>
    
    <form action="<?php echo home_url('/'); ?>" role="search" method="get">
    	<?php // First dropdown
    		$term_selected_cidade = get_terms('cidade');
    		echo '<select id="cidade" name="cidade">';
    		echo '<option disabled="disabled" selected="selected">Cidade</option>';
    		foreach ($term_selected_cidade as $term) {
    			echo '<option id=' . $term->term_id . ' value=' . $term->slug . '>' . $term->name . '</option>';
    		}
    		echo '</select>';
    
    	?>
    
    	<!-- Second Dropdown -->
    	<select name="cozinha" id="cozinha" >
    		<option>Escolha uma cidade</option>
    	</select>
    	<input type="submit" value="buscar" />
    </form>
    
    <?php }
    
    function implement_ajax() {
    	// Populates the second dropdown with cuisine terms
    	global $wpdb;
    	$cozinha_term = $_POST['name'];
    	$query = "
    		SELECT DISTINCT $wpdb->terms.name, $wpdb->terms.slug
    		FROM $wpdb->terms
    		INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id
    		INNER JOIN $wpdb->term_relationships ON $wpdb->term_relationships.term_taxonomy_id=$wpdb->term_taxonomy.term_taxonomy_id
    		LEFT JOIN $wpdb->posts ON $wpdb->posts.ID=$wpdb->term_relationships.object_id
    		WHERE $wpdb->term_taxonomy.taxonomy='cozinha' AND $wpdb->posts.ID IN (
    		SELECT $wpdb->posts.ID
    		FROM $wpdb->terms
    		INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id
    		INNER JOIN $wpdb->term_relationships ON $wpdb->term_relationships.term_taxonomy_id=$wpdb->term_taxonomy.term_taxonomy_id
    		LEFT JOIN $wpdb->posts ON $wpdb->posts.ID=$wpdb->term_relationships.object_id
    		WHERE $wpdb->term_taxonomy.taxonomy='cidade' AND $wpdb->terms.slug='$cozinha_term')";
    
    	$object = $wpdb->get_results($query);
    	$cuisine_terms = array();
    	for ($i = 0; $i < count($object); $i++) {
    		$cuisine_terms[] = get_object_vars($object[$i]);
    	}
    
    	echo '<select id="cozinha">' . '<option disabled="disabled" selected="selected">Cozinha</option>';
    	foreach ($cuisine_terms as $term) {
    		echo '<option value=' . $term['slug'] . '>' . $term['name'] . '</option>';
    	}
    	echo '</select>';
    }
    
    add_action('wp_ajax_category_select_action', 'implement_ajax');
    add_action('wp_ajax_nopriv_category_select_action', 'implement_ajax');

Topic Closed

This topic has been closed to new replies.

About this Topic