Very custom WordPress SELECT

magnifying-glass-copy

The following scripts allow a very custom selection of WordPress posts. This is not a ‘how-to’ type of post. It is just a code dump of a solution for a very specific problem. Hopefully it can inspire people that happen to end up here in search of solutions for their own specific WordPress custom select queries. I don’t think I can help you any further than this, so asking for help probably doesn’t bring you any further (sorry). You’ll have to figure out how to apply this for yourself.

Keep an eye onĀ http://core.trac.wordpress.org/ticket/15031 for the progress of the implementation of ordering based on multiple meta keys in the WP core.

In this example, a selection of posts with a custom post type is ordered by multiple meta keys, then filtered based on multiple taxonomies and paginated. Subsequently I add some details about getting the taxonomies that the selected posts are linked with and getting the total post count in the template.

I’ve tried to split up the different thoughts that lead to this exact code, hoping that it helps you to decide which parts you can use.

Screenshots of the end product (filters on the left, listing on the right).


You see:
Pic 1: Posts are sorted by the meta key that holds the price.
Pic 2: Posts are sorted by the meta key that holds the status (‘verhuurd’ (= rented out)). This ordering is secondary, which is why posts with a different status don’t show up untill page 7 in the picture. Posts with this status are then again ordered by price. (For the keen eyed: sadly, the post with price 960 actually gets ordered after posts with prices like 1.200, because of the dot that’s in that notation)
Pic 3: Posts are filtered by taxonomy filters
Throughout the pictures you see the pagination in action.

Select a custom post type

When WordPress selects a custom post type you’ll get something like the following:

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.post_type = 'woningen'
 AND (wp_posts.post_status = 'publish')
 AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
 AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')

Order by multiple meta keys

To order by multiple meta keys a custom SQL statement was implemented that includes extra SELECTs on two INNER JOINs (one for each meta key that is used to ORDER BY). The specific code for these joins:

SELECT wp_posts.*, wp_postmeta.*, mt1.meta_value as m_price
FROM wp_posts
       INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
       INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE wp_posts.post_type = 'woningen'
        AND (wp_posts.post_status = 'publish')
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
        AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')
ORDER BY meta_value ASC, m_price, post_date DESC

Filter by taxonomy

The filters are linked with WP’s taxonomies. You’ll have to build some way to get the filter selection in PHP to be able to add them to the custom query. Although I did it slightly different, errkk’s snippet on http://snipplr.com/view/42555/ was of great help. My PHP that leads up to the insertion of extra SQL is as follows:

$cats = get_filter_url();

$tax_args = array();
if(!empty($cats)){
    foreach($cats as $key => $val){
        $tax_args[$key] = $val;
    }
}

$sort_conditions = '';
$required_matches = -1;

if( count( $tax_args ) ){
    $sort_conditions.= " WHERE 0=1 "; // add this so other stuff can just repeat ORs
    foreach( $tax_args as $key => $term_id ){
       $term_id = get_id_from_slug($term_id);
       $sort_conditions.= " OR taxonomy = '$key' AND term_id = '$term_id' ";
       $required_matches ++;
    }
}

Next, the query is modified quite a bit as per errkk’s example. The SELECT discussed earlier is extended to include taxonomy data. For the same reason extra INNER JOINs are made. This SELECT is aliased as t1 and then wrapped in another SELECT that selects all posts that match the combination of taxonomies (by ways of counting them) (note: the ORDER BY is moved to the outer SELECT):

SELECT count(1) AS matches,t1.* FROM(
   SELECT wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.term_id, wp_postmeta.*, mt1.meta_value as m_price
       FROM wp_posts
           LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
           LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
           LEFT JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id)
           INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
           INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
       WHERE wp_posts.post_type = 'woningen'
           AND (wp_posts.post_status = 'publish')
           AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
           AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')
       LIMIT 0, 999) AS t1
       ". $sort_conditions . "
  GROUP BY ID HAVING matches > $required_matches
  ORDER BY meta_value ASC, m_price, post_date DESC

Paginating these results

Jennifer’s work on http://www.scriptygoddess.com/archives/2011/02/23/wordpress-pagination-woes-solved-i-hope/ helped a lot for the next bit. To add pagination the last SELECT is put in a variable ($total in this example) which then is used to calculate that what is required to make a second SELECT that is narrowed by page. There’s a bit of PHP first:

$totalposts = $wpdb->get_results($total, OBJECT);
$woning_result_count = sizeof($totalposts); //stored in global for later use in updating the result count
$ppp = intval(get_query_var('posts_per_page'));
$wp_query->found_posts = count($totalposts);
$wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$offset = ($paged-1) * $ppp;

Then the narrowed SELECT:

SELECT count(1) AS matches,t1.* FROM(
   SELECT wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.term_id, wp_postmeta.*, mt1.meta_value as m_price
      FROM wp_posts
         LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
         LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
         LEFT JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id)
         INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
         INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
      WHERE wp_posts.post_type = 'woningen'
         AND (wp_posts.post_status = 'publish')
         AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
         AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')
      LIMIT 0, 999) AS t1
      ". $sort_conditions . "
   GROUP BY ID HAVING matches > $required_matches
   ORDER BY meta_value ASC, m_price, post_date DESC
   LIMIT ". $ppp . " OFFSET " . $offset

Output in template with custom loop

The results of the last SQL are the output of the function (in this example: get_woningen()). This function is called in the template where a custom loop is required (thanks again to Jennifer):

<?php
$woningen = get_woningen();
if ($woningen):
   global $post;
   foreach ($woningen as $post):
      setup_postdata($post); ?>
      //output post HMTL
   <?php endforeach;
else:
   echo 'Geen woningen gevonden'; //nothing found notification
endif; ?>

Update filters

To work with the entire list of posts that come through the taxonomy selection before they are paginated I added an optional partial return. When the optional $partial parameter for function get_woningen($partial = false) is true, the first SELECT (before pagination) is returned. For each post then, the post terms are retreived via wp_get_post_terms():

function get_valid_cats($taxonomies){
    $woningen = get_woningen(true);
    $allowedCatIDs = array();
    $args = array('fields' => 'ids');

    global $post;

    foreach($woningen as $post){
        setup_postdata($post);
        $allowedCatIDs[] = wp_get_post_terms( $post->ID, $taxonomies, $args);
    }

    $allowedCats = call_user_func_array('array_merge', $allowedCatIDs);
    $allowedCats = array_unique($allowedCats);

    return $allowedCats;
}

With this data the filters on the front-end are updated (the exact working of this process is so specific that I didn’t see value in detailing it here).

Post count

The post count is saved in a global variable right after the first SELECT in get_woningen(). The following function just spews this out (if you’ve gotten this far, it should be easy enough to grasp):

function get_result_count(){
global $woning_result_count; // global $wp_query;
$total = $woning_result_count; // $wp_query->post_count;
$html = '<span>Resultaat</span>';

if($total < 1){
	$html .= 'geen woningen gevonden';
}else{
	$html .= $total;
	$html .= ($total == 1) ?  ' woning' : ' woningen';
}
echo $html;
}

The full scripts

functions.php

$woning_result_count; //global to store result count

function get_woningen($partial = false){
	 global $wpdb, $wp_query, $woning_result_count;

    $cats = get_filter_url();

    $tax_args = array();
    if(!empty($cats)){
        foreach($cats as $key => $val){
            $tax_args[$key] = $val;
        }
    }

    $sort_conditions = '';
    $required_matches = -1;

    if( count( $tax_args ) ){
        $sort_conditions.= " WHERE 0=1 "; // add this so other stuff can just repeat ORs
        foreach( $tax_args as $key => $term_id ){
            $term_id = get_id_from_slug($term_id);
            $sort_conditions.= " OR taxonomy = '$key' AND term_id = '$term_id' ";
            $required_matches ++;
        }
    }

    $total = "SELECT count(1) AS matches,t1.* FROM(
                SELECT wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.term_id, wp_postmeta.*, mt1.meta_value as m_price
                    FROM wp_posts
                        LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
                        LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
                        LEFT JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id)
                        INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
                        INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
                WHERE wp_posts.post_type = 'woningen'
                    AND (wp_posts.post_status = 'publish')
                    AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
                    AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')
                LIMIT 0, 999) AS t1
                ". $sort_conditions . "
            GROUP BY ID HAVING matches > $required_matches
            ORDER BY meta_value ASC, m_price, post_date DESC
            ";

    $totalposts = $wpdb->get_results($total, OBJECT);
    $woning_result_count = sizeof($totalposts);
    $ppp = intval(get_query_var('posts_per_page'));
    $wp_query->found_posts = count($totalposts);
    $wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);

    if ($partial) {
        return $wpdb->get_results($total, OBJECT);
    }

    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $offset = ($paged-1) * $ppp;
    $wp_query->request = "SELECT count(1) AS matches,t1.* FROM(
                            SELECT wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.term_id, wp_postmeta.*, mt1.meta_value as m_price
                                FROM wp_posts
                                    LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
                                    LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
                                    LEFT JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id)
                                    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
                                    INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
                            WHERE wp_posts.post_type = 'woningen'
                                AND (wp_posts.post_status = 'publish')
                                AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
                                AND (wp_postmeta.meta_key = 'm_status' AND mt1.meta_key = 'm_price')
                            LIMIT 0, 999) AS t1
                            ". $sort_conditions . "
                        GROUP BY ID HAVING matches > $required_matches
                        ORDER BY meta_value ASC, m_price, post_date DESC
                        LIMIT ". $ppp . " OFFSET " . $offset;
    $pageposts = $wpdb->get_results($wp_query->request, OBJECT);

	 return $pageposts;
}

function get_valid_cats($taxonomies){

    $woningen = get_woningen(true);
    $allowedCatIDs = array();
    $args = array('fields' => 'ids');

    global $post;

	 foreach($woningen as $post){
        setup_postdata($post);

		$allowedCatIDs[] = wp_get_post_terms( $post->ID, $taxonomies, $args);
	 }

	 $allowedCats = call_user_func_array('array_merge', $allowedCatIDs);
	 $allowedCats = array_unique($allowedCats);

	 return $allowedCats;
}

/**
* Not detailed in the post, but added FYI (this makes use of the explained get_valid_cats()
**/
function get_filters($postType = 'post', $echo = TRUE) {

	 $taxonomies = array(
		  'Prijsklasse'	=> 'prijsklasse',
		  'Oppervlakte'	=> 'oppervlakte',
		  'Type woning'	=> 'woning_type',
		  'Verdieping'		=> 'verdieping',
		  'Aantal kamers' => 'kamers_aantal'
		  //'Gevel'			=> 'gevel'
	 );

	 $args = array(
		  'type'                     => $postType,
		  'child_of'                 => 0,
		  'parent'                   => '',
		  'orderby'                  => 'tax_order',
		  'order'                    => 'ASC',
		  'hide_empty'               => 0,
		  'hierarchical'             => 0,
		  'exclude'                  => '',
		  'include'                  => '',
		  'number'                   => '',
		  'taxonomy'                 => $taxonomies,
		  'pad_counts'               => false
		);

	 // exlude cats with no woningen
	 $allowedCatIDs =  get_valid_cats($taxonomies);
	 if(!empty($allowedCatIDs)){
		 $args['include'] = $allowedCatIDs;
	 }

	 // get cats to show
	 $allCategories = get_categories( $args );
	 //debug($allCategories);
	 $categories = array();

	 // group
	 $categories = organize_cats($allCategories);

	 // make html
	 $taxNiceName = str_replace('_',' ',key($categories));
	 $taxNiceName = ucfirst($taxNiceName);

	 $urlArray = get_filter_url();
	 $urlVars = implode($urlArray, '&');

	 $current = '';
	 $first = true;
	 $active = false;
	 $glue = '&';

	 $html = '
<ul>';

		  foreach($categories as $key => $cats){

					 foreach($cats as $cat){

						  if($cat->taxonomy != $current){ // filter categories

								// $taxNiceName = str_replace('_',' ',$cat->taxonomy);
								 $taxNiceName = array_search($cat->taxonomy, $taxonomies);
								 if(!$first){
									  $html .= '</ul>
';
								 }

								 $html .= '
	<li><strong>'.ucfirst($taxNiceName).'</strong>
<ul>';
								 $active = false;
						  }

						  if(!array_key_exists($key, $urlArray)){//non active filters

								$taxUrlName = strtolower($cat->taxonomy);
								$html .= '
	<li><a href="/woningen?'.$urlVars.$glue.$taxUrlName.'='.$cat->slug.'-'.$cat->cat_ID.'">'.$cat->name.'</a></li>
';
								$active = false;

						  }elseif(!$active){// active filters

								// set clear filter value
								$minusUrlArray = $urlArray;
								unset($minusUrlArray[$key]);
								$minusUrlVars = implode($minusUrlArray, '&');

								// get active cat name
								$id = get_id_from_slug($urlArray[$key]);
								$activeCat = get_term_by('id', $id, $key);

								$html .= '
	<li>'.$activeCat->name.'
<a class="clear" href="/woningen?'.$minusUrlVars.'">x wis filter</a></li>
';
								$active = true;
						  }

						  $current = $key;
					 }

				$first = false;
		  }

	 $html .= '</ul>
</li>
';

    // Print or return
    if ($echo) {
        echo $html;
    } else {
        return $categories;
    }
}

archive-woningen.php (template)

<!--?php get_header(); ?-->
<div id="middle">
<h1 class="home-h1">...en ik huur in Deo Neo</h1>
		<!--?php  		$urlVarsArray = get_filter_url(); 		update_filter_session($urlVarsArray); 		echo get_active_filters(true);?-->
<div id="container">
<div id="content">
<div class="woning-list">
						  <!--?php $woningen = get_woningen(); ?-->
                          <!--?php if ($woningen): ?-->
                          <!--?php global $post; ?-->
                          <!--?php foreach ($woningen as $post): ?-->
                          <!--?php setup_postdata($post);<br /-->										  $img = get_featured_media($post_id = get_the_ID(), $width = 220, $height = 170, $crop = true);
										  $img = (empty($img)) ? '<img title="'.get_the_title().'" src="'.get_bloginfo('template_url').'/library/images/dummy-220x170.gif" alt="Foto '.get_the_title().'" />' : $img;
										  $status = get_status($post_id);
										  ?>
<div class="woning">
													 <a href="<?php the_permalink() ?>"><!--?php echo $img ?--></a>
<div class="woning-excerpt">
<h2><a href="<?php the_permalink() ?>"><!--?php echo ucfirst(get_the_title()); ?--></a></h2>
														  <a href="<?php the_permalink() ?>">
																<!--?php get_overview_terms(get_the_ID()) ?-->
																<!--?php if($status == 'verhuurd'){ ?-->
																	 <span class="red"><!--?php echo ucfirst($status) ?--></span>
																<!--?php } ?-->
														  </a>

														  <span class="price"><!--?php echo get_price(get_the_ID()) ?--></span>
														  <!--?php the_excerpt(); ?--></div>
</div>
								<!--?php endforeach;<br /-->
								woningen_page_navi('<span class="pag-title">Pagina:</span>'); // use the page navi function
						  else:

								echo 'Geen woningen gevonden';

						  endif;?></div>
</div>
<!-- #content--></div>
<!-- #container-->

		<!--?php get_sidebar('left'); // sidebar left ?-->
		<!--?php get_sidebar('right'); // sidebar right ?-->
		<a id="brandlabel" href="http://www.bouwinvest.nl">Bouwinvest.nl</a></div>
<!-- #middle-->
<!--?php get_footer(); ?-->

sidebar-left.php (template)

<div id="sideLeft" class="sidebar sidebar-overview">
<div class="sidebar-overview-head">
		  Selecteer uw wensen</div>
	 <!--?php get_filters('woningen', true) ?-->
<div class="sidebar-overview-footer">
		  <!--?php get_result_count(); ?--></div>
</div>
<!-- .sidebar#sideLeft -->

Hope this helps! Let me know if it did.

Leave a Reply

Jouw e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *

De volgende HTML tags en attributen zijn toegestaan: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>