WordPress.org

Ready to get started?Download WordPress

Forums

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

  1. Comrade
    Member
    Posted 1 year 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";
    	endif;
    	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 ";
    	endif;
    	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
    Member
    Posted 1 year ago #

    Rather than the inner SELECT why not,

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

    Ian

  3. Comrade
    Member
    Posted 1 year ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic