Custom search SQL Query to add custom field in result

Question

I have to write my own MySQL query since I haven't found a good way to do it within wordpress functionality.

I want the search query to search trough post_title / post_content & meta_key description in pages & CPT transit_routes it also need to get those results in a specific language (copied filter from WPML) then finally order the result by posts_type and posts_date. So here is my function :

function search_query_with_trips( ) {
    global $wpdb;
    $search_string = esc_sql($_GET['s']);
    $sql = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->base_prefix}posts.ID FROM {$wpdb->base_prefix}posts
            LEFT JOIN {$wpdb->base_prefix}icl_translations t ON {$wpdb->base_prefix}posts.ID = t.element_id AND t.element_type LIKE 'post\_%'
            LEFT JOIN {$wpdb->base_prefix}postmeta m ON m.post_id={$wpdb->base_prefix}posts.ID
            LEFT JOIN {$wpdb->base_prefix}icl_languages l ON t.language_code=l.code AND l.active=1 WHERE 1=1
            AND ( ( {$wpdb->base_prefix}posts.post_title LIKE '%".$search_string."%') OR ({$wpdb->base_prefix}posts.post_content LIKE '%".$search_string."%') OR ((m.meta_key = 'description') AND (m.meta_value LIKE '%".$search_string."%')) )
            AND {$wpdb->base_prefix}posts.post_type IN ('page','transit_routes')
            AND ({$wpdb->base_prefix}posts.post_status = 'publish')
            AND (t.language_code='".ICL_LANGUAGE_CODE."' OR t.language_code IS NULL )
            ORDER BY {$wpdb->base_prefix}posts.post_type DESC, {$wpdb->base_prefix}posts.post_date DESC";

    $query=$wpdb->get_results($sql);
    return $query;
}

The first part work well since i've tested it and it return me ids of posts that match my criteria. The problem is when it come for the loop i've tried in search.php to add before my while (have_posts()) { ... }

$search_result = search_query_with_trips();
/*
$search_result =
    array (
        0 => 
            stdClass::__set_state(array(
                'ID' => '2224',
        )),
        1 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        2 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        3 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        4 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        5 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        6 => 
            stdClass::__set_state(array(
            'ID' => '2224',
        )),
        7 => 
           stdClass::__set_state(array(
           'ID' => '2224',
        )),
        8 => 
           stdClass::__set_state(array(
           'ID' => '2224',
        )),
        9 => 
           stdClass::__set_state(array(
           'ID' => '2224',
        )),
        ...
        26 => 
            stdClass::__set_state(array(
            'ID' => '2223',
        )),
        27 => 
            stdClass::__set_state(array(
            'ID' => '217',
        )),
        28 => 
            stdClass::__set_state(array(
            'ID' => '217',
        )),
        29 => 
            stdClass::__set_state(array(
            'ID' => '217',
        )),
    ) 
*/
setup_postdata($search_result);

But it's not working like that as I can see but I can't figure out how to do it...

Answer 1

I got it ! Here is my final code for people who will search for similar problem :)

in functions.php :

function se190254_custom_sql_for_search( ) {
    global $wpdb;
    //For security escape the search parameter 
    $search_string = esc_sql($_GET['s']);

    //Build the SQL request with $wpdb->base_prefix for "portability" 
    $sql = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->base_prefix}posts.ID FROM {$wpdb->base_prefix}posts
                LEFT JOIN {$wpdb->base_prefix}icl_translations t ON {$wpdb->base_prefix}posts.ID = t.element_id AND t.element_type LIKE 'post\_%'
                LEFT JOIN {$wpdb->base_prefix}postmeta m ON m.post_id={$wpdb->base_prefix}posts.ID
                LEFT JOIN {$wpdb->base_prefix}icl_languages l ON t.language_code=l.code AND l.active=1 WHERE 1=1
                AND ( ( {$wpdb->base_prefix}posts.post_title LIKE '%".$search_string."%') OR ({$wpdb->base_prefix}posts.post_content LIKE '%".$search_string."%') OR ((m.meta_key = 'description') AND (m.meta_value LIKE '%".$search_string."%')) )
                AND {$wpdb->base_prefix}posts.post_type IN ('page','transit_routes')
                AND ({$wpdb->base_prefix}posts.post_status = 'publish')
                AND (t.language_code='".ICL_LANGUAGE_CODE."' OR t.language_code IS NULL )
                ORDER BY {$wpdb->base_prefix}posts.post_type DESC, {$wpdb->base_prefix}posts.post_date DESC";

    //Get results of the SQL request (save in object by default, array_a or array_n is not needed here)
    $query=$wpdb->get_results($sql);

    //Loop trough each result and create a array with unique value and change it from array of object too simple array
    foreach ($query as $post) {
        $result[$post->ID] = $post->ID;
    }

    return $result;
}

in your template file :

        $search_result = se190254_custom_sql_for_search();
        if ( count($search_result) > 0 ) { ?>
            <header class="page-header">
                <h1 class="page-title"><?php printf(__('Search Results for: %s', 'se190254'), '<span>' . get_search_query() . '</span>'); ?></h1>
            </header><!-- .page-header -->
            <ul><?php
            foreach( $search_result as $post ) {
               $post = get_post($post->ID);
               setup_postdata($post); ?>
               <li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></li><?php
           } ?>
           </ul><?php
       } else { ?>
           <p>Sorry nothing match your search query</p><?php
        } ?>

In hope that it will help someone out there !

Answer 2

I believe you need to setup the post data for each post. Try:

$search_result = search_query_with_trips();

foreach( $search_result as $post ) :
  setup_postdata($post); ?>
    <li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></li>
<?php endforeach; 

You may need global $post before the foreach loop.

https://codex.wordpress.org/Function_Reference/setup_postdata