Meta query in pre_get_posts

[Spidlace]

I don't know if it is already in place, but I want to add in the hook "pre_get_posts" custom field. In the page "search.php", I want to search "example" in post_title, content and custom field in all pages, posts and post-type.

But this is the code I put in the hook :

function recherche_avancee( $query ) {
    if ( !is_admin() && $query->is_search ) {
        $custom_fields = array(
            "introduction",
            "ensavoirplus"
        );
        $meta_query = array('relation' => 'OR');
        foreach($custom_fields as $cf) {
            array_push($meta_query, array(
                'key' => $cf,
                'value' => $_GET['s'],
                'compare' => 'LIKE'
            ));
        }
        $query->set("meta_query", $meta_query);
    }
}
add_action( 'pre_get_posts', 'recherche_avancee');

and this is the SQL code I get :

1.  SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
2.  FROM wp_posts 
3.  INNER JOIN wp_postmeta
4.  ON ( wp_posts.ID = wp_postmeta.post_id )
5.  WHERE 1=1 
6.  AND (((wp_posts.post_title LIKE '%exemple%')
7.  OR (wp_posts.post_content LIKE '%exemple%'))) 
8.  AND wp_posts.post_type IN ('post', 'page', 'attachment')
9.  AND (wp_posts.post_status = 'publish'
10. OR wp_posts.post_status = 'miseenavant'
11. OR wp_posts.post_author = 3)
13. AND ( ( wp_postmeta.meta_key = 'introduction'
14. AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%exemple%' ) 
15. OR ( wp_postmeta.meta_key = 'en_savoir_plus_page'
16. AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%exemple%' ) )
17. GROUP BY wp_posts.ID
18. ORDER BY wp_posts.menu_order ASC
19. LIMIT 0, 10

I want to add the line 13, 14, 15, 16 on the top on the code after the search in the content and the title. And I don't want a AND before the line 13 but a OR.
But here's the SQL I would need :

1.  SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
2.  FROM wp_posts 
3.  INNER JOIN wp_postmeta
4.  ON ( wp_posts.ID = wp_postmeta.post_id )
5.  WHERE 1=1 
6.  AND (((wp_posts.post_title LIKE '%exemple%')
7.  OR (wp_posts.post_content LIKE '%exemple%')
8.  OR  ( wp_postmeta.meta_key = 'introduction'
9.  AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%exemple%' ) 
10. OR ( wp_postmeta.meta_key = 'en_savoir_plus_page'
11. AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%exemple%' ))
12. AND wp_posts.post_type IN ('post', 'page', 'attachment')
13. AND (wp_posts.post_status = 'publish'
14. OR wp_posts.post_status = 'miseenavant'
15. OR wp_posts.post_author = 3)
16. GROUP BY wp_posts.ID
17. ORDER BY wp_posts.menu_order ASC
18. LIMIT 0, 10

Is it possible to do this now? If this is not the case, is it possible to consider it in the "future"?

Hope this is not already proposed ...

Thank you for reading :-)

[boonebgorges]

Short answer: No, you can't do this. It would be nice if you could, but it would take major reworking of WP_Query.

Longer answer: Query vars passed to WP_Query are translated into independent SQL clauses, all of which are joined by AND. The API does not support connecting them with OR. I have a pie-in-the-sky idea that we should adopt the recursive syntax of WP_Meta_Query etc, so that you could nest query conditions and connect them with the relation of your choice, kinda like this:

$q = new WP_Query( array(
    'relation' => 'OR',
    array(
         'relation' => 'AND',
         'post__in' => array( 5,6,7 ),
         's' => 'foo',
    ),
    array(
         'relation' => 'AND',
         'meta_query' => $meta_query,
         'post_parent__in' => array( 8,9,10 ),
    ),
) );

etc etc etc. But this would require an extensive rewrite of WP_Query::get_posts().

For now, I recommend doing a direct SELECT query to get a list of posts that match your 'exemple' search terms (you can use WP_Meta_Query to help build the SQL), and then pass the resulting IDs to post__in in your 'pre_get_posts' filter.

Related: #16910, #15068

[Spidlace]

Thank you for your answer .
I wanted to use a maximum of WP hooks to do that, but I find that allow the developer to choose between an AND or OR could open up another opportunity for an advanced search done ..

Currently we can not find a word in a title in an title OR content OR in a custom field but only AND except between the title and content. I imagine this must be a big update of WP_Query order to implement this, but it would have been great ...

[theMikeD]

I'm trying to do the same things as OP and have run into the same problem. Taking @boonebgorges advice I have a list of post IDs to insert (which I get by creating a new WP_Query() and running it). But there is no post__in in $query as used in pre_get_posts so I'm not sure where to put the IDs.

$query->set( 'post__in', $valid_ids );

isn't doing it.

This all seems redundant to me anyway. If I need to run a query to get the list of post IDs to use, then I already have the posts I want to use. So then I scrapped pre_get_posts and tried hooking to the_posts which I found mentioned in an offhand way after too much googling. the_posts is called after the query is run.

So, after checking to see that I'm in the right spot I run a new WP_Query() to get the results I need. Except that the_posts is run after WP_Query->query so I end up in an endless recursion.

So then I poked at query.php and discovered that the filter for the_posts is wrapped in a suppress_filters check. So by adding 'suppress_filters' => true to the query being called in the function called by the the_posts hook, I get exactly what I want.

I put all this here in case the next guy that comes along find my solution useful.