Revisions to How do i append UNION SQL Query with default search query

edited body

Source Link

edited Sep 20, 2014 at 2:18

67.8k
7
119
251

Reuse the native search query:

/**
 * class WPSE_Add_Search_Data
 */
class WPSE_Add_Search_Data
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;

        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );

        // Extra query to add externalFetch data tofrom the native searchexternal querytable.
        // The fields much match the fields from the main search query.
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";

        // Append the external data with custom order:
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

Reuse the search query:

/**
 * class WPSE_Add_Search_Data
 */
class WPSE_Add_Search_Data
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;

        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );

        // Extra query to add external data to the native search query.
        // The fields much match the fields from the main search query.
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";

        // Append the external data with custom order:
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

Reuse the native search query:

/**
 * class WPSE_Add_Search_Data
 */
class WPSE_Add_Search_Data
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;

        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );

        // Fetch data from the external table.
        // The fields much match the fields from the main search query.
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";

        // Append the external data with custom order:
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

edited body

Source Link

edited Sep 20, 2014 at 2:13

birgire

67.8k
7
119
251

/**
 * AppendAdd data to the native WordPress search
 *
 * @see http://wordpress.stackexchange.com/a/161949/26350
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_Search;WPSE_Add_Search_Data;
        $c->init();
    }
});

where the WPSE_SearchWPSE_Add_Search_Data class isdefined as:

/**
 * class WPSE_SearchWPSE_Add_Search_Data
 */
class WPSE_SearchWPSE_Add_Search_Data
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb; 

        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );

        // Extra query to add external data to the native search query.
        // The fields much match the fields from the main search query.
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";

        // Append the external data with custom order:
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

/**
 * Append data to the native search
 *
 * @see http://wordpress.stackexchange.com/a/161949/26350
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_Search;
        $c->init();
    }
});

where the WPSE_Search class is:

/**
 * class WPSE_Search
 */
class WPSE_Search
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;
        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

/**
 * Add data to the native WordPress search
 *
 * @see http://wordpress.stackexchange.com/a/161949/26350
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_Add_Search_Data;
        $c->init();
    }
});

where the WPSE_Add_Search_Data class defined as:

/**
 * class WPSE_Add_Search_Data
 */
class WPSE_Add_Search_Data
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb; 

        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );

        // Extra query to add external data to the native search query.
        // The fields much match the fields from the main search query.
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";

        // Append the external data with custom order:
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

edited body

Source Link

edited Sep 20, 2014 at 2:07

birgire

67.8k
7
119
251

Appending external searchAppend data to the native search:

ReusingReuse the search query:

Well, if I understand your comment correctly you want to reuse the main search query in your extra SQL query. Here's one hacky versionidea:

add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        add_filter( 'posts_orderby', '__return_null' );
        add_filter( 'posts_request', function( $request ) {
            global $wpdb;
            $sql = stristr( $request, "AND {$wpdb->posts}.post_type IN", true );
            $sql = str_ireplace( "FROM {$wpdb->posts}", "FROM {$wpdb->prefix}software_and_hardware_post", $sql );
            $sql = str_ireplace( $wpdb->posts . '.*', 'ID, title as post_title, content as post_content, status as post_status ', $sql );
            $sql = str_ireplace( $wpdb->posts . '.post_title', 'title', $sql );/**
            $sql = str_ireplace(* $wpdb->postsAppend .data '.post_content',to 'content',the $sqlnative );search
            $sql .= ' AND status = 1';*
            $request = str_ireplace(* $wpdb->posts@see http://wordpress. 'stackexchange.*', 'ID, post_title, post_content, post_status ', $request );
            return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
        });
    }
});

Here's another version that's more flexible:

com/**
 * Search query UNIONa/161949/26350
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_SearchUnion;WPSE_Search;
        $c->init();
    }
});

where the WPSE_SearchUnionWPSE_Search class is:

/**
 * class WPSE_SearchUnionWPSE_Search
 */
class WPSE_SearchUnionWPSE_Search
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;
        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

Appending external search data:

Reusing the search query:

Well, if I understand your comment correctly you want to reuse the main search query in your extra SQL query. Here's one hacky version:

add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        add_filter( 'posts_orderby', '__return_null' );
        add_filter( 'posts_request', function( $request ) {
            global $wpdb;
            $sql = stristr( $request, "AND {$wpdb->posts}.post_type IN", true );
            $sql = str_ireplace( "FROM {$wpdb->posts}", "FROM {$wpdb->prefix}software_and_hardware_post", $sql );
            $sql = str_ireplace( $wpdb->posts . '.*', 'ID, title as post_title, content as post_content, status as post_status ', $sql );
            $sql = str_ireplace( $wpdb->posts . '.post_title', 'title', $sql );
            $sql = str_ireplace( $wpdb->posts . '.post_content', 'content', $sql );
            $sql .= ' AND status = 1';
            $request = str_ireplace( $wpdb->posts . '.*', 'ID, post_title, post_content, post_status ', $request );
            return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
        });
    }
});

Here's another version that's more flexible:

/**
 * Search query UNION
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_SearchUnion;
        $c->init();
    }
});

where the WPSE_SearchUnion class is:

/**
 * class WPSE_SearchUnion
 */
class WPSE_SearchUnion
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;
        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

Append data to the native search:

Reuse the search query:

Well, if I understand your comment correctly you want to reuse the main search query in your extra SQL query. Here's one idea:

/**
 * Append data to the native search
 *
 * @see http://wordpress.stackexchange.com/a/161949/26350
 */
add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        $c = new WPSE_Search;
        $c->init();
    }
});

where the WPSE_Search class is:

/**
 * class WPSE_Search
 */
class WPSE_Search
{
    protected $search_where = '';

    public function init()
    {
        add_filter( 'posts_search',  array( $this, 'posts_search'  ) );
        add_filter( 'posts_fields',  array( $this, 'posts_fields'  ) );
        add_filter( 'posts_request', array( $this, 'posts_request' ) );
        add_filter( 'posts_orderby', '__return_null' );
    }

    public function posts_search( $search )
    {
        $this->search_where = $search;
        return $search;
    }

    public function posts_fields( $fields )
    {
        return 'ID, post_title, post_content, post_status';
    }
           
    public function posts_request( $request )
    {
        global $wpdb;
        $search_where = str_ireplace( 
            array( $wpdb->posts . ".post_", "AND (password = '')" ), 
            "", 
            $this->search_where 
        );
        $sql = "SELECT ID, title as post_title, 
                       content as post_content, status as post_status
                FROM {$wpdb->prefix}software_and_hardware_post
                WHERE 1=1 {$search_where} AND status=1";
        return  "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
    }

} // end class

added 1747 characters in body

Source Link

edited Sep 20, 2014 at 2:00

birgire

67.8k
7
119
251

Loading

added 1747 characters in body

Source Link

edited Sep 20, 2014 at 1:53

birgire

67.8k
7
119
251

Loading

added 1 character in body

Source Link

edited Sep 19, 2014 at 16:25

birgire

67.8k
7
119
251

Loading

added 1229 characters in body

Source Link

edited Sep 19, 2014 at 16:14

birgire

67.8k
7
119
251

Loading

Source Link

created Sep 19, 2014 at 12:49

birgire

67.8k
7
119
251

Loading

Stack Exchange Network

Return to Answer

Reuse the native search query:

Reuse the search query:

Reuse the native search query:

Appending external searchAppend data to the native search:

ReusingReuse the search query:

Appending external search data:

Reusing the search query:

Append data to the native search:

Reuse the search query: