Sort wp-e-commerce products by category and product title

This post is more than 12 years old.

The wp-e-commerce shopping cart plugin lets you sort your products by a few different things: name (product title), price, date/time created… but it doesn’t let you sort by category name and then product title. Here’s what I just came up with for one client; it isn’t generic, but other developers should be able to adapt it for their circumstances.

What I mean there, is that I haven’t looked at how to make this a generic fit for any wp-e-commerce site, just for the situation I have at hand. The site I’m working on has two ways to navigate products: by category, or by a custom taxonomy I created for wine styles (red, white, sparkling, etc.) This means that I can look for either the wp-e-commerce product category or my custom wine styles taxonomy to see whether or not to sort by product category. If you want to use this code, you’ll need to work out your own conditions.

There are three hooks needed to accomplish this task:

  1. hook parse_query to check for conditions that mark a posts query as a wp-e-commerce product query
  2. hook posts_join so that we can add the category name to the SQL query, and sort by it
  3. hook posts_orderby so that we can change the SQL order by clause

For step 2, we need to add three tables to the SQL query so that we can link the products to their categories using the WordPress term relationships: wp_terms, wp_term_relationships and wp_term_taxonomy. In case the SQL query is already using any of those tables (e.g. a category or tag query), we’ll give them specific aliases that should be unique to us.

class OrderWpscProducts {

    // flag for when a shop products post query is being created
    private $isProductQuery = false;

    public function __construct() {
        // hooks for altering order of product pages
        add_filter('parse_query', array($this, 'filterProductQueryMark'), 20);
        add_filter('posts_join', array($this, 'filterProductQueryJoins'), 20);
        add_filter('posts_orderby', array($this, 'filterProductQueryOrderBy'), 20);
    }

    /**
    * detect a posts query that should return a list of shop products,
    * and mark it for later filters
    * @param WP_Query $query
    * @return WP_Query
    */
    public function filterProductQueryMark($query) {
        $this->isProductQuery = false;

        if (!is_admin() && (wpsc_is_in_category() || !empty($query->query_vars['wpsc_wine_style']))) {
            $this->isProductQuery = true;
        }
    }

    /**
    * if the posts query is for a list of shop products,
    * then add some tables to the join so that we can sort by category name
    * @param string $joins
    * @return string
    */
    public function filterProductQueryJoins($joins) {
        global $wpdb;

        if ($this->isProductQuery) {
            $joins .= "
 INNER JOIN $wpdb->term_relationships wpsc_cat_rel ON wp_posts.ID = wpsc_cat_rel.object_id
 INNER JOIN $wpdb->term_taxonomy wpsc_cat_tax ON wpsc_cat_tax.term_taxonomy_id = wpsc_cat_rel.term_taxonomy_id
   and wpsc_cat_tax.taxonomy = 'wpsc_product_category'
 INNER JOIN $wpdb->terms wpsc_cat ON wpsc_cat.term_id = wpsc_cat_tax.term_id
";
        }

        return $joins;
    }

    /**
    * if the posts query is for a list of shop products, then sort by category name and product title
    * @param string $orderby
    * @return string
    */
    public function filterProductQueryOrderBy($orderby) {
        if ($this->isProductQuery) {
            $orderby = 'wpsc_cat.name ASC, wp_posts.post_title ASC';
        }

        return $orderby;
    }
}

// create instance, will be kept alive by hooks
new OrderWpscProducts();

Job is done, in the required order, for me at least. You might need to pick your own conditions for step 1.