mysql - 如何使用SQL联接postmeta表并获取自定义字段日期之前的帖子

我想结合wp_posts表和wp_postmeta表，并获取自定义字段日期之前的帖子。

下面只有wp_posts表的代码，它可以正常工作

$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

我想将“ postmeta table”添加到“ post table”，并且仅获取自定义字段日期之前的帖子
如果是“ WP_Query”，则元查询将在下面

  'meta_query' => array(
    array(
        'key' => 'end_date',
        'value' => date('Y/m/d'),
        'type' => 'DATE',
        'compare' => '>=',
    )
  )`

我在下面尝试了代码，但返回空数组。

$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta
    ON $wpdb->postmeta.post_id
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    AND $wpdb->postmeta.meta_key = 'end_date'
    AND $wpdb->postmeta.meta_value = date('Y/m/d')
    AND $wpdb->postmeta.meta_type = 'DATE'
    AND $wpdb->postmeta.meta_compare = '>='
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

仅添加meta_key时，返回与“仅wp_posts表的代码”相同的结果（第一个代码）

//same result as "Code for only wp_posts table"
$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta
    ON $wpdb->postmeta.post_id
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    AND $wpdb->postmeta.meta_key = 'end_date'
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

我想知道如何添加条件
“键”，“值”和“比较”。

我是SQL的初学者，希望有人能帮助我。

最佳答案

我在原始查询和新查询之间看到的更改是LEFT JOIN以及WHERE子句的附加条件。在初始查询中添加LEFT JOIN不会导致空数组，除非其中之一为true：

1）您的原始查询返回了0行
2）您的新查询在WHERE子句中具有其他过滤

如果要返回所有初始行（即来自$wpdb->posts的行）并包括来自$wpdb->postmeta的所有匹配行，则只需将多余的AND条件从WHERE子句移到JOIN：

LEFT JOIN $wpdb->postmeta
ON $wpdb->postmeta.post_id
-- join conditions
AND $wpdb->postmeta.meta_key = 'end_date'
AND $wpdb->postmeta.meta_value = date('Y/m/d')
AND $wpdb->postmeta.meta_type = 'DATE'
AND $wpdb->postmeta.meta_compare = '>='
-- filter conditions
WHERE post_status = 'publish'
AND post_date <= now( )
AND $wpdb->posts.post_type = 'event'

关于mysql - 如何使用SQL联接postmeta表并获取自定义字段日期之前的帖子，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59421169/