Support

Account

Home Forums General Issues Mixed query post title + custom field

Helping

Mixed query post title + custom field

    • matam

    • May 4, 2021 at 6:13 am

    Hello everyone, I am facing some issues with a custom query I would like to set up.
    It’s a mixing between POST TITLE and ACF. that’s why I cannot use standard WP_QUery.

    Below you will find a complex query which is keeping not giving expected results:
    All the Posts which have either the given %multi%word%string in the title, or in operations_short_bio or operations_long_bio BUT both of operations field must be retrieved.

    
    $searchStr = preg_replace('/\s+/', '%', $_GET['s']);
    $searchStr = "%".$searchStr."%";
    
    SELECT
                           posts.post_title AS name, posts.ID as ID,
                           MAX(CASE WHEN (postmeta.meta_key='operations_short_bio') THEN postmeta.meta_value ELSE NULL END) AS short_description,
                           MAX(CASE WHEN (postmeta.meta_key='operations_long_bio') THEN postmeta.meta_value ELSE NULL END) AS long_description
                        FROM {$wpdb->base_prefix}posts AS posts
                        LEFT JOIN {$wpdb->base_prefix}postmeta AS postmeta
                            ON posts.ID=postmeta.post_id
                        WHERE
                              posts.post_type = 'post'
                              AND
                                posts.post_status = 'publish'
                              AND
                              (
                                    (
                                        postmeta.meta_key
                                         IN('operations_long_bio', 'operations_short_bio')
                                         AND postmeta.meta_value LIKE '$searchStr'
                                    )
                                  OR ( posts.post_title LIKE '$searchStr' )
                             )
                        GROUP BY posts.ID,posts.post_title
                        ORDER BY posts.post_title
    

    the output is a $res array with just one of the acf fields, and the other simply is null 🙁

    • matam

    • May 4, 2021 at 5:28 pm

    hello again,

    I think we can simplify the question…

    how can I say

    SELECT that specific meta_value='some value' 
       WHEN it's parent meta_key = 'something' 
          AND some other meta_value='some other value' 
       WHEN it's parent meta_key = 'something else' 
          AND maybe it's meta_value also contains 'some%words'

    sorry for this silly pseudo code, but I can’t find an easier way to ask you how to select and RETURN only specific ACF whose value is what I query…

    ty!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.