You are very close. I had the same problem a while ago, and blogged about it. Your join is missing one more join table, wp_terms, which has the term you want to sort on.
Here is the code I used:
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();