0

Example:

(SELECT ID, post_status, post_title, post_excerpt, post_content 
FROM wp_posts 
WHERE ((`post_title` LIKE '%2020%') OR (`post_excerpt` LIKE '%2020%')
OR (`post_content` LIKE '%2020%')) 
AND (`post_status` = 'publish' )
)
UNION (SELECT ID, '', title, '', content 
FROM wp_software_and_hardware_post 
WHERE (`title` LIKE '%2020%') OR (`content` LIKE '%2020%')
and status=1)
ORDER BY `post_status` DESC;

like which filter method i can use.

1 Answer 1

2

Append data to the native search:

You can try for example:

add_action( 'pre_get_posts', function( $q ) {
    if( $q->is_main_query() && $q->is_search() )
    {
        $q->set( 'nopaging', true );
        add_filter( 'posts_request', function( $request ) {
            $sql = 'YOUR EXTRA SQL QUERY';
            return "({$request}) UNION ({$sql}) ORDER BY post_status DESC";
        });  
    }
});

where we use the no paging version to match your SQL query.

Reuse the native 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:

/**
 * 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 
        );

        // 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
6
  • birgire thanks so much. Actually while using UNION both $request and $sql should select same no of fields. But in this example the $result have (select *) and $sql select 5 fields. So mysql return Error Code 1222. So i want change my approach, i want to append the $sql result to main $request query.
    – Mahesh
    Sep 19, 2014 at 13:50
  • @Mahesh I updated the answer.
    – birgire
    Sep 19, 2014 at 16:19
  • Thanks @Birgire, This Solves my problem. Exactly i am looking for this approach/method. I think this method is the key role player in this class public function posts_fields( $fields ){}
    – Mahesh
    Sep 22, 2014 at 5:29
  • @Mahesh Glad to hear it worked for you.
    – birgire
    Sep 22, 2014 at 11:51
  • could you please explain about post_fields method if you have time or do you have any blog which is explianed about this code
    – Mahesh
    Sep 22, 2014 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.