WP_Query with "post_title LIKE 'something%'"?

  • I need to do a WP_Query with a LIKE on the post_title.

    I started with this regular WP_Query:

    $wp_query = new WP_Query( 
        array (
            'post_type'        => 'wp_exposants',
            'posts_per_page'   => '1',
            'post_status'      => 'publish',
            'orderby'          => 'title', 
            'order'            => 'ASC',
            'paged'            => $paged
        )
    ); 
    

    But what I actually want to do looks like this in SQL:

    $query = "
            SELECT      *
            FROM        $wpdb->posts
            WHERE       $wpdb->posts.post_title LIKE '$param2%'
            AND         $wpdb->posts.post_type = 'wp_exposants'
            ORDER BY    $wpdb->posts.post_title
    ";
    $wpdb->get_results($query);
    

    The output prints the results i'm excpecting, but I use the regular <?php while ( $wp_query->have_posts() ) : $wp_query->the_post(); ?> to display the results.
    And that is not working with $wpdb->get_results().

    How can I achieve what I described here?

  • Jan Fabry

    Jan Fabry Correct answer

    9 years ago

    I would solve this with a filter on WP_Query. One that detects an extra query variable and uses that as the prefix of the title.

    add_filter( 'posts_where', 'wpse18703_posts_where', 10, 2 );
    function wpse18703_posts_where( $where, &$wp_query )
    {
        global $wpdb;
        if ( $wpse18703_title = $wp_query->get( 'wpse18703_title' ) ) {
            $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \'' . esc_sql( $wpdb->esc_like( $wpse18703_title ) ) . '%\'';
        }
        return $where;
    }
    

    This way you can still call WP_Query, you just pass the title as the wpse18703_title argument (or change the name to something shorter).

    This one is somehow missing the `$wpdb->prepare()`.

    @kaiser: It's been a long time, but I think this was not possible with `prepare()`. `$wpdb->prepare('LIKE "%s%%"', 'banana')` would return `"LIKE ''banana'%'"`, so we have to construct the query ourselves, and do the escaping too.

    @JanFabry Happy to see you agaaaaaaaain! :) Drop by in chat some time, hm? StopPress would be happy to see you. About that `prepare()`. Yeah, that's tricky and I had to try that several times, before I got around it. From something I just made: `$wpdb->prepare( ' AND {$wpdb->posts}.post_title LIKE %s ', esc_sql( '%'.like_escape( trim( $term ) ).'%' ) )`. And I'm pretty sure the `esc_sql()` is unnecessary and just paranoid.

    It seems that you can't search a string with `'` (apostrophe) inside. I guess it's because of escaping ? I didn't find the solution yet

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM