[resolved] Left join slows down mysql query (3 posts)

  1. Chris
    Posted 3 years ago #

    Hi everyone.

    I am using an ecommerce plugin with my WordPress installation to sell products. By default this plugin orders my product post type just like WordPress orders the default post type--newest to oldest.

    In my online catalog I have products that are in stock, out of stock (which can be re-ordered), and sold (which cannot be re-ordered). I need to order these products like so: in stock > out of stock > sold. To do this, I have created a meta key called "product_stock" whose value is numeric--3 for in stock, 2 for out of stock, 1 for sold. I order them by this value using the following two functions:

    // Join for ordering products and search pages by product quantity
    add_filter('posts_join', 'meta_join');
    function meta_join($join) {
    	if ( is_store_page() || is_search() ) :
    		global $wpdb;
    		//join stock
    		return $join . " LEFT JOIN (
    		SELECT *
    		FROM $wpdb->postmeta
    		WHERE meta_key = 'product_stock') AS stock
    		ON $wpdb->posts.ID = stock.post_id";
    	return $join;
    //Orderby for ordering products and search pages by quantity
    add_filter('posts_orderby', 'product_meta_orderby');
    function product_meta_orderby($orderby) {
    	if ( is_store_page() || is_search() ) :
    		global $wpdb;
    		return " wp_posts.post_type DESC, stock.meta_value DESC, wp_posts.post_date DESC ";
    	return $orderby;

    (As a side note, the is_store_page() function is one that I have created to detect whether my users are looking at one of my store pages or at a normal page or blog page.)

    So basically I am wondering if there might be a faster way to implement the ordering that I need, because currently queries on my store pages run very, very slowly.

  2. ianhaycox
    Posted 3 years ago #

    Rather than the inner SELECT why not,

        $wpdb->postmeta stock
        $wpdb->posts.ID = stock.post_id AND
        stock.meta_key = 'product_stock'


  3. Chris
    Posted 3 years ago #

    Of course! That does it, thank you very much. :)

Topic Closed

This topic has been closed to new replies.

About this Topic