WordPress.org

Ready to get started?Download WordPress

Forums

AND and OR query in WordPress wpdb (7 posts)

  1. sixfootjames
    Member
    Posted 5 months ago #

    I have read through the entire wpdb and ezSQL so I have a very good understanding of what the wpdb class can do...essentially wpdb is to SQL what jQuery is to Javascript!

    I am not an SQL developer but my understanding is that WP_Query cannot do AND and OR queries which is what I need.

    Here's my requirements. The final outcome should be that if a user selects from the ProductType dropdown and clicks the search button, the page should return the results based on that term from the select menu. If they select an option from ProductGroup and hit the search button then the results need to return based on the selection.

    If both dropdown menus have options selected, both ProductType and ProductGroup needs to be queried and the results returned. So it needs to be an AND or OR query that returns the results and not just one or the other.

    This would be my pseudo code attempt.

    If ProductType selected
       Return all ProductTypes
    
    If ProductGroup selected
       Return all ProductGroups
    
    If ProductType AND ProductGroup selected
       Return filtered ProductTypes and ProductGroups

    I'd appreciate some help with this as SQL is no my "first" language :) Thank you.

  2. bcworkz
    Member
    Posted 5 months ago #

    WP_Query does both ANDs and ORs, but often you have no choice, it does whatever makes the most sense for normal queries. OR is rather rare, the default is typically AND.

    However, it appears your dropdowns are either categories or some sort of taxonomy. The 'tax_query' arguments for WP_Query support user selectable logic, so there is no problem doing what you are doing.

    FWIW, in case you do come across something WP_Query cannot handle, there are several filters for various SQL clauses that make up the query. You can hook these filters and alter what WP_Query came up with, so you should be able to compose almost any SQL query imaginable.

  3. sixfootjames
    Member
    Posted 5 months ago #

    Hi bcworkz, thanks for getting back to me. Here is a snippet of what I have.

    <?php //the_content();
    
    	if ($_GET["productType"] || $_GET["productGroup"] || $_GET["productCountry"]) {
    		$productType 		= $_GET["productType"];
    		$productGroup 	= $_GET["productGroup"];
    		$productCountry 	= $_GET["productCountry"];
    	} else {
    		$productType 		= ' ';
    		$productGroup 	= ' ';
    	}
    
    $args = array(
    		'post_type' => 'product',
    		'posts_per_page' => -1,
    		'orderby' => 'title',
    		'order' => 'ASC',
    		'relation'=>'AND',
    		'meta_query' => array(
    		       array(
    		           'key' 		=>	'product_type',
    		           'value' 		=> 	$productType,
    		           'compare' 	=> 	'LIKE'
    		       ),
    		       array(
    				    'key'		=>	'crop_group',
    				    'value'		=>	$productGroup,
    				    'compare' 	=> 	'LIKE'
    				),
    		       array(
    				    'key'		=>	'country',
    				    'value'		=>	$productCountry,
    				    'compare' 	=> 	'LIKE'
    				)
    		    )
    		);
    ?>
    
    <form name="x" action="" method="GET" class="search-results-form" >
      <div>
      	<select name="solutionCountry" class="search-product-type">
    		<option value="South Africa">South Africa</option>
    		<option value="Angola">Angola</option>
    		<option value="Botswana">Botswana</option>
    		<option value="Malawi">Malawi</option>
    		<option value="Mozambique">Mozambique</option>
    		<option value="Namibia">Namibia</option>
    		<option value="Zambia">Zambia</option>
    		<option value="Zimbabwe">Zimbabwe</option>
      	</select>
      </div>
      or
      <div>
      	<select name="solutionType" class="search-product-type">
    		<option value="Product1">Product 1</option>
    		<option value="Product2">Product 2</option>
    		<option value="Product3">Product 3</option>
    		<option value="Product4">Product 4</option>
    		<option value="Product5">Product 5</option>
      	</select>
      </div>
      or
      <div>
    	  <select name="solutionGroup" class="search-product-group">
    			<option value="Group1">Group1</option>
    			<option value="Group2">Group2</option>
    			<option value="Group3">Group3</option>
    			<option value="Group4">Group4</option>
    			<option value="Group5">Group5</option>
    	  </select>
      </div> 
    
      <input type="submit" value="SEARCH" class="submit-button btn" />
    </form>
    
    <?php
    	// return the results
    	$loop = new WP_Query( $args ); ?>
    	<ul>
    		<?php while ( $loop->have_posts() ) : $loop->the_post(); ?>
    			<li>
    				<strong><a href="<?php echo get_permalink(); ?>"><?php the_title(); ?></a> - <?php the_field('solution_type'); ?></strong><br>
    				<?php the_field('solution_description'); ?><br><br>
    			</li>
    		<?php endwhile; ?>
    		<?php wp_reset_query(); ?>
    	</ul>
    <?php } ?>

    If it is possible to use WP_Query with AND and OR, I'd appreciate some help.

  4. bcworkz
    Member
    Posted 5 months ago #

    Looks good for the most part! The only thing I see is the 'relation' argument needs to be inside the 'meta_query' array:

    //...
    'orderby' => 'title',
    'order' => 'ASC',
    'meta_query' => array(
    	'relation' => 'AND',
    	array(
    		'key' 		=>	'product_type',
    		'value' 	=> 	$productType,
    		'compare' 	=> 	'LIKE'
    	),
    //...

    of course the relation can be 'OR' if you like.

  5. sixfootjames
    Member
    Posted 5 months ago #

    Thanks bcworkz. I will give that a shot.
    Perhaps it is the logic that I cannot seem to wrap my head around and the fact that I have not used WP_Query much like this, but will this ensure that if I select one dropdown and search or both dropdowns, it will do the query across both selected options?

    Do I not need both AND and OR to do the comparison?

  6. bcworkz
    Member
    Posted 5 months ago #

    As it stands with AND, all 3 selections must match a post for it to be returned. If the OR relation is specified, any one or more of the selections must match a post for it to be returned. You cannot provide both AND and OR relations in the same query. More complex logic is not possible with meta_query arguments, but is possible by filtering 'posts_where_paged'.

    If not all selections are required, you'll need to restructure your program flow a bit. You must never try to assign non-existent values. For starters, you are assigning all 3 values if any one of them exist. If one of the others do not exist, the assignment statement will throw an error. The safest way to allow any possible combination of selection or non-selection is something like this for each possible value: if(array_key_exists("productType", $_GET)) $productType = $_GET["productType"];.

    You will then also need to selectively build the meta_query array depending on which selections were provided. Initialize the array with $meta_args = array('relation' => 'AND'); (or OR if that is what you want), then add in each argument array if the argument was selected. This statement could go into the same array key exists conditional:

    $meta_args[] = array(
       'key' => 'product_type',
       'value' => $productType,
       'compare' => 'LIKE',
    );

    Then of course the full query $args array would include 'meta_query' => $meta_args,

    One minor improvement since the array assignment is in the same conditional as the $productType assignment is to not even bother with $productType and define the array directly with $_GET["productType"], that is unless you need $productType for other reasons.

  7. sixfootjames
    Member
    Posted 5 months ago #

    Thanks bcworkz, for the lengthy explanation, much appreciated! I will give your instructions a bash now and see how it goes.

Reply

You must log in to post.

About this Topic