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

Question

我想合并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 表”添加到“post 表”并仅获取自定义字段日期之前的帖子

如果是“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时，返回结果与“Code for only wp_posts table”(第一个代码)相同

//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 的初学者，希望有人帮助我。

Answer 1

我看到您的原始查询和新查询之间的变化是 LEFT JOIN 以及在 WHERE 子句中添加的条件。向初始查询添加 LEFT JOIN 不会产生空数组，除非满足以下条件之一:

1) 您的原始查询返回 0 行
2) 您的新查询在 WHERE 子句中进行了额外的过滤

如果您想返回所有初始行(即来自 $wpdb->posts)并包含来自 $wpdb->postmeta 的任何匹配行，则只需移动从 WHERE 子句到 JOIN 的额外 AND 条件:

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'