WP_Query returns result when matching taxonomy list OR usermeta key/value pair

  • Resolved Thomas S

    (@eighty20results)


    5 years, 10 months ago

    Hi,

    I’m working on a WP_Query that will only return custom posts that belong to a list of taxonomy terms _and_ match either of a list of postmeta key/value pairs.

    I expect a list of 2 post IDs, but am getting all Posts that have one of the terms OR any of the postmeta key/value pairs.

    Basically, the CPT is a user directory and I’m searching for first name + 4 taxonomy IDs.

    What I’m looking for is a returned record if the CPT matches both any of the postmeta key/value pairs (postmeta is OR’ed), AND those OR’ed postmeta key/value pair post IDs also belongs to one of the 4 taxonomy IDs I want (610, 611, 612, 613).

    Instead the returned result includes any record containing the postmeta key/value options OR the term_taxonomy_ids.

    The query args:

    
(
    [posts_per_page] => 50
    [paged] => 1
    [post_status] => publish
    [post_type] => cm-expert
    [orderby] => exists_clause
    [suppress_filters] =>
    [meta_query] => Array
        (
            [relation] => AND
            [cm_search_clause] => Array
                (
                    [relation] => OR
                    [4] => Array
                        (
                            [key] => cmed_bemail
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [9] => Array
                        (
                            [key] => cmed_cityTown
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [11] => Array
                        (
                            [key] => cmed_stateCounty
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [12] => Array
                        (
                            [key] => cmed_country
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [13] => Array
                        (
                            [key] => cmed_postalcode
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [24] => Array
                        (
                            [key] => cmed_company_name
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [25] => Array
                        (
                            [key] => cmed_cityTown
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [26] => Array
                        (
                            [key] => cmed_firstname
                            [value] => Thomas
                            [compare] => LIKE
                        )

                    [27] => Array
                        (
                            [key] => cmed_lastname
                            [value] => Thomas
                            [compare] => LIKE
                        )

                )

            [28] => Array
                (
                    [relation] => OR
                    [not_exists_clause] => Array
                        (
                            [key] => cmed_lastname
                            [compare] => NOT EXISTS
                        )

                    [exists_clause] => Array
                        (
                            [key] => cmed_lastname
                            [compare] => EXISTS
                        )

                )

        )

    [tax_query] => Array
        (
            [relation] => AND
            [0] => Array
                (
                    [taxonomy] => membership
                    [terms] => Array
                        (
                            [0] => attorneys-1-5
                            [1] => attorneys-6
                            [2] => attorneys-govt
                            [3] => attorneys-new
                        )

                    [operator] => IN
                    [include_children] => 1
                    [field] => slug
                )

        )

    [s] => Thomas
    [order] => ASC

    The resulting SQL:

    
SELECT SQL_CALC_FOUND_ROWS wpwj_posts.ID
FROM wpwj_posts
       LEFT JOIN wpwj_term_relationships ON (wpwj_posts.ID = wpwj_term_relationships.object_id)
       LEFT JOIN wpwj_postmeta ON (wpwj_posts.ID = wpwj_postmeta.post_id)
       LEFT JOIN wpwj_postmeta AS mt1 ON (wpwj_posts.ID = mt1.post_id AND mt1.meta_key = 'cmed_lastname')
       LEFT JOIN wpwj_postmeta AS mt2 ON (wpwj_posts.ID = mt2.post_id)
       INNER JOIN wpwj_postmeta AS pm1 ON 1 = 1
                                            AND pm1.post_id = wpwj_posts.ID
                                            AND pm1.meta_key = 'cmed_promoted'
                                            AND wpwj_posts.ID = pm1.post_id
                                            AND wpwj_posts.post_status = 'publish'
WHERE 1 = 1 AND wpwj_posts.ID NOT IN(24, 25, 26, 27, 28, 29, 7144) AND (
    wpwj_term_relationships.term_taxonomy_id IN(610, 611, 612, 613)
    ) AND (((wpwj_posts.post_title LIKE '%Thomas%'))) AND (wpwj_posts.post_password = '') OR (
                                                                                                 (
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_bemail' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_cityTown' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_stateCounty' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_country' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_postalcode' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_company_name' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_cityTown' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_firstname' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                       OR
                                                                                                     (wpwj_postmeta.meta_key = 'cmed_lastname' AND
                                                                                                      wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                     )
                                                                                                   AND
                                                                                                 (
                                                                                                     mt1.post_id IS NULL
                                                                                                       OR
                                                                                                     mt2.meta_key = 'cmed_lastname'
                                                                                                     )
                                                                                                 ) AND
                                                                                             wpwj_posts.post_type = 'cm-expert' AND
                                                                                             ((wpwj_posts.post_status = 'publish'))
GROUP BY wpwj_posts.ID
ORDER BY pm1.meta_value ASC, CAST(mt2.meta_value AS CHAR) ASC
LIMIT 0, 50

    The SQL that would actually return what I expect to see returned:

    
SELECT SQL_CALC_FOUND_ROWS wpwj_posts.ID
FROM wpwj_posts
       LEFT JOIN wpwj_term_relationships ON (wpwj_posts.ID = wpwj_term_relationships.object_id)
       LEFT JOIN wpwj_postmeta ON (wpwj_posts.ID = wpwj_postmeta.post_id)
       LEFT JOIN wpwj_postmeta AS mt1 ON (wpwj_posts.ID = mt1.post_id AND mt1.meta_key = 'cmed_lastname')
       LEFT JOIN wpwj_postmeta AS mt2 ON (wpwj_posts.ID = mt2.post_id)
       INNER JOIN wpwj_postmeta AS pm1 ON 1 = 1
                                            AND pm1.post_id = wpwj_posts.ID
                                            AND pm1.meta_key = 'cmed_promoted'
                                            AND wpwj_posts.ID = pm1.post_id
                                            AND wpwj_posts.post_status = 'publish'
WHERE 1 = 1 AND wpwj_posts.ID NOT IN(24, 25, 26, 27, 28, 29, 7144) AND (
    wpwj_term_relationships.term_taxonomy_id IN(610, 611, 612, 613)
    ) AND (((wpwj_posts.post_title LIKE '%Thomas%')) AND (wpwj_posts.post_password = '') OR (
                                                                                                (
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_bemail' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_cityTown' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_stateCounty' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_country' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_postalcode' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_company_name' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_cityTown' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_firstname' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                      OR
                                                                                                    (wpwj_postmeta.meta_key = 'cmed_lastname' AND
                                                                                                     wpwj_postmeta.meta_value LIKE '%Thomas%')
                                                                                                    )
                                                                                                )
                                                                                              AND
                                                                                            (
                                                                                                mt1.post_id IS NULL
                                                                                                  OR
                                                                                                mt2.meta_key = 'cmed_lastname'
                                                                                                )
    ) AND wpwj_posts.post_type = 'cm-expert' AND ((wpwj_posts.post_status = 'publish'))
GROUP BY wpwj_posts.ID
ORDER BY pm1.meta_value ASC, CAST(mt2.meta_value AS CHAR) ASC
LIMIT 0, 50
Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    What sort of meta_query arg is “cm_search_clause”? I assume some sort of enhanced search plugin. While the (((wpwj_posts.post_title LIKE '%Thomas%'))) clause is valid SQL, it looks like a mistake in hierarchy. The “.post_title LIKE” clause would normally come from the 's'=>'Thomas', query arg, but it appears the plugin is altering that aspect since the usual “.post_content LIKE” clause is missing. I’m inclined to believe the hierarchy error is due to this plugin. Thus I suggest you seek assistance through the plugin’s support channel.

    If you have trouble getting help that way, you could try building the requisite WP_Query object using only standard WP_Query args without the “cm_search_clause” arg. Another choice would be to try to patch up the faulty SQL as required through the “posts_request” filter. I think this approach would be inherently fragile even though it looks tempting.

    One other possibility would be to completely build your own SQL query yourself and execute with $wpdb methods. You would lose a lot of the flexibility offered by WP_Query, but if the one SQL structure meets your needs, perhaps the lack of flexibility doesn’t matter.

    Thread Starter Thomas S

    (@eighty20results)

    Thank you for the input.

    The ['cm_search_clause'] clause is added by a 3rd party plugin to support both ordering by metadata _and_ searching for a list of meta key/values.

    The missing .post_title search may have been me being a little aggressive in editing the copy/paste (don’t recall whether i did or not, but the clause is included in every test I’ve done for the past 2-3 weeks, so…)

    Wound up using the posts_request filter to move the parenthesis.

    Agreed it’s potentially flakey, but I can’t get the query parser to change behavior… As long as I need both clause in the meta_query section to orderby a configurable/custom meta value _and_ I need to search in multiple meta_keys for a value _and_ I need to include the taxonomies as a limiter, this I’ve been unable to change the placement of the parenthesis.

    • This reply was modified 5 years, 10 months ago by Thomas S. Reason: Marking as solved
    Moderator bcworkz

    (@bcworkz)

    You’re welcome. Working, though potentially flakey, is better than not working ??

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘WP_Query returns result when matching taxonomy list OR usermeta key/value pair’ is closed to new replies.

Tags