mysql - sql 不喜欢 "%term%"不工作

Question

我有一个查询，这是一个用于自定义搜索的 WordPress 查询，它工作正常，但这里有一些成员(member)订阅，如果论坛仅适用于“黄金”订阅，则用户无法找到该帖子

现在它显示所有用户的所有帖子(免费和黄金)我想将查询修改为显示除 AND (post_forum_meta.meta_value NOT LIKE '%gold%')

之外的所有内容

我通过 id 加入了帖子父论坛的帖子元，然后寻找 meta_value(免费/黄金)

SELECT SQL_CALC_FOUND_ROWS 
DISTINCT wp_posts.ID FROM wp_posts  
LEFT JOIN wp_postmeta as meta_1 ON wp_posts.ID = meta_1.post_id 
LEFT JOIN wp_postmeta as event_venue_meta ON meta_1.meta_value = event_venue_meta.post_id

LEFT JOIN wp_postmeta as post_forum_meta ON meta_1.meta_value = post_forum_meta.post_id AND meta_1.meta_key='_bbp_forum_id'

LEFT JOIN wp_term_relationships as tax_relation ON wp_posts.ID =  tax_relation.object_id
INNER JOIN wp_term_taxonomy as tax_term ON tax_term.term_taxonomy_id = tax_relation.term_taxonomy_id
INNER JOIN wp_terms as term ON term.term_id = tax_term.term_id 
LEFT  JOIN wp_icl_translations t


ON wp_posts.ID = t.element_id
AND t.element_type = CONCAT('post_', wp_posts.post_type)  
WHERE 1=1  
AND (((wp_posts.post_title LIKE '%search%')
AND (post_forum_meta.meta_value NOT LIKE '%gold%')
OR (meta_1.meta_key='_tribe_event_speaker' AND meta_1.meta_value LIKE '%search%')
OR (meta_1.meta_key='_EventStartDate' AND meta_1.meta_value LIKE '%search%')
OR (event_venue_meta.meta_key='_VenueAddress' AND event_venue_meta.meta_value LIKE '%search%')
OR (term.name LIKE '%search%')
OR (wp_posts.post_excerpt LIKE '%search%') 
OR (wp_posts.post_content LIKE '%search%')

))  
AND wp_posts.post_type IN ('post', 'tribe_events', 'topic') 
AND ((wp_posts.post_status = 'publish')) 
AND ( ( t.language_code = 'de' AND wp_posts.post_type  IN ('post','page','forum','product','product_variation','tribe_venue','tribe_organizer','tribe_events','tribe_wooticket' )  ) );

我尝试过使用 NULL LIKE NOT 等，但没有

所以我需要的是获得除“黄金”帖子之外的所有帖子......这是我的表格:

Answer 1

只需将条件移至您的联接中即可:

LEFT JOIN wp_postmeta as post_forum_meta ON meta_1.meta_value = post_forum_meta.post_id AND 
    meta_1.meta_key='_bbp_forum_id' AND
    post_forum_meta.meta_value NOT LIKE '%gold%'

这将从数据集中完全排除“黄金”项目，并将简化底部的条件语句。

或者，您可以使用括号来设置 WHERE 子句中操作的优先顺序。它的工作原理就像基础数学一样 - 还记得“PEMDAS”吗？

WHERE 1=1  
  AND (
    (
      (
        wp_posts.post_title LIKE '%search%'
        AND post_forum_meta.meta_value NOT LIKE '%gold%'
      )
      AND (
        (meta_1.meta_key='_tribe_event_speaker' AND meta_1.meta_value LIKE '%search%')
        OR (meta_1.meta_key='_EventStartDate' AND meta_1.meta_value LIKE '%search%')
        OR (event_venue_meta.meta_key='_VenueAddress' AND event_venue_meta.meta_value LIKE '%search%')
        OR (term.name LIKE '%search%')
        OR (wp_posts.post_excerpt LIKE '%search%') 
        OR (wp_posts.post_content LIKE '%search%')
      )
    )
    AND wp_posts.post_type IN ('post', 'tribe_events', 'topic') 
    AND wp_posts.post_status = 'publish'
    AND (
      t.language_code = 'de' 
      AND wp_posts.post_type  IN ('post','page','forum','product','product_variation','tribe_venue','tribe_organizer','tribe_events','tribe_wooticket')
    )
  );

您只需确保要排除的条件正确嵌套在正确的括号内。现在，您的“NOT LIKE”语句正在被这些“OR”条件之一覆盖。

您可能会发现使用我上面提到的第一个选项性能更高 - 连接较小的数据集可以带来更高性能的查询。