WordPress.org

Ready to get started?Download WordPress

Forums

Problem with the WP meta_query SQL (2 posts)

  1. greendemiurge
    Member
    Posted 11 months ago #

    The background of what I am trying to accomplish:
    I have configured a custom post type (listing) with custom meta fields (_shortdescription, _longdescription). I tried adding a pre_get_posts filter to alter the search query to include these fields. Here's the code I used:

    function custom_search_query( $query ) {
    	if ( !is_admin() && $query->is_search ) {
    		$query->set('post_type', 'listing');
    
    		$metaquery = array(
    			array(
    				'key' => '_shortdescription',
    				'value' => $query->query_vars['s'],
    				'compare' => 'LIKE'
    			),
    			array(
    				'key' => '_longdescription',
    				'value' => $query->query_vars['s'],
    				'compare' => 'LIKE'
    			));
    
    		$query->set('meta_query', $metaquery);
    
    	}
         }
         add_filter( 'pre_get_posts', 'custom_search_query');

    The result is that when this filter is in place the only way for an item to come up in the search results is if the keyword is found not just in one of the title, short or long description, but if and only if it is found in all three locations.

    I confirmed this by loading the project into eclipse with xDebug and pulled the raw SQL query. The query that WP generates is this:

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 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 1=1
    AND (((wp_posts.post_title LIKE '%val%')
    OR (wp_posts.post_content LIKE '%val%')))
    AND (wp_posts.post_password = '')
    AND wp_posts.post_type = 'listing'
    AND (wp_posts.post_status = 'publish')
    AND ((wp_postmeta.meta_key = '_shortdescription' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
    AND (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR) LIKE '%val%') )
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date
    DESC LIMIT 0, 10

    The query can be fixed by converting the final two ANDS to ORs like so:

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 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 1=1
    AND (((wp_posts.post_title LIKE '%val%')
    OR (wp_posts.post_content LIKE '%val%')))
    AND (wp_posts.post_password = '')
    AND wp_posts.post_type = 'listing'
    AND (wp_posts.post_status = 'publish')
    OR ((wp_postmeta.meta_key = '_shortdescription' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
    OR (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR) LIKE '%val%') )
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date
    DESC LIMIT 0, 10

    This returns the expected result set.

    I spent hours poring over this on the forums before posting, but please forgive me if I have breeched etiquette as this is my first time posting to the WP support forum.

  2. greendemiurge
    Member
    Posted 11 months ago #

    Just to clarify, I am looking for input as to whether this issue is due to a problem in my code or if it constitutes an actual bug that needs to be reported.

    Thank you.

Reply

You must log in to post.

About this Topic