mysql - ORDER BY 子句受另一个表中的子选择约束

Question

我在处理 WordPress 项目时遇到了某种边缘情况。我使用高级自定义字段来存储有关帖子的元数据，该元数据存储在“postmeta”表中(而帖子存储在“post”表中；此处前缀为“otca_”)。

这里的帖子是事件，具有强制性的 evt_date 和 可能 具有 evt_date_fin (“fin”在法语中代表“结束”，对于蹩脚的命名约定表示抱歉)。

我的目标是选择一系列事件，仅保留那些具有 future 设置的 evt_date 或 future 设置的 evt_date_fin 的事件(在后一种情况下，无论 evt_date 如何)，同时按 evt_date 排序，以便以分页、排序的方式显示它们。

这是我想到的:

$today = date('Ymd');
$perPage = 12;
$offset = $perPage * ($paged-1); // $paged equals 1, 2, … n (the current page)

$querystr = "
SELECT SQL_CALC_FOUND_ROWS * FROM otca_posts AS post
INNER JOIN otca_postmeta AS meta ON (post.ID = meta.post_id)
WHERE post.post_type = 'agenda'
AND post.post_status = 'publish'
AND (
     (meta.meta_key = 'evt_date'     AND CAST(meta.meta_value AS CHAR) >= '". $today ."')
  OR (meta.meta_key = 'evt_date_fin' AND CAST(meta.meta_value AS CHAR) >= '". $today ."')
)
ORDER BY (meta.meta_key = 'evt_date' AND CAST(meta.meta_value AS CHAR)) ASC
LIMIT ". $perPage ." OFFSET ". $offset;

$evts = $wpdb->get_results($querystr);
$total = $wpdb->get_var('SELECT FOUND_ROWS()');

// then looping over the $evts and using $perPage / $total to build the pagination links

因此，对于存储在 otca_post 中的给定帖子，otca_postmeta 表中有几条引用该帖子的记录(使用 post_id)，这些记录的元键/元值对不同(一对用于 evt_date，另一对用于 evt_date_fin)。我用它们过滤掉查询中的帖子，效果很好。

ORDER BY 子句实际上不起作用。

我想知道如何才能使帖子按 evt_date 排序，evt_date 是存储在 otca_postmeta 中的一条信息； evt_date 不是一行，而是存储在“meta_key”行中的值，这意味着我需要以某种方式执行子选择以便......顺序。

谢谢。

Answer 1

我在 WordPress 插件中经常这样做 - 您需要将每个 meta_key 的 posts 表连接到 postmeta 表。是您关心的，在本例中是“evt_date”和“evt_date_fin”。加入它们后，您可以在 WHERE 子句中使用它们来与 DATE(CURRENT_TIMESTAMP) 进行比较。使用MySql函数STR_TO_DATE()会将存储在 meta_value 中的字符串转换为 DATE 数据类型 - 此示例假设您使用 Y-m-d 格式。

$perPage = 12;
$offset = $perPage * ($paged-1);

// create the SQL query by joining posts to postmeta
$querystr = <<<SQL
    SELECT SQL_CALC_FOUND_ROWS 
        p.*, 
        STR_TO_DATE(s.meta_value, '%Y-%m-%d') AS start_date, 
        STR_TO_DATE(e.meta_value, '%Y-%m-%d') AS end_date
    FROM otca_posts AS p
    -- join postmeta for the start
    JOIN otca_postmeta AS s ON p.ID = s.post_id AND s.meta_key = 'evt_date'
    -- join postmeta for the end
    JOIN otca_postmeta AS e ON p.ID = e.post_id AND e.meta_key = 'evt_date_fin'
    WHERE 
        -- is the start greater than or equal to today?
        STR_TO_DATE(s.meta_value, '%Y-%m-%d') >= DATE(CURRENT_TIMESTAMP) OR
        -- or is the end greater than or equal to today?
        STR_TO_DATE(e.meta_value, '%Y-%m-%d') >= DATE(CURRENT_TIMESTAMP)
    -- order the results by the start, then the end, then the post title
    ORDER BY start_date, end_date, p.post_title
    -- paginate the results
    LIMIT $perPage OFFSET $offset
SQL;

// get posts that match
$evts = $wpdb->get_results( $querystr );

// get the total number of results
$total = $wpdb->get_var( 'SELECT FOUND_ROWS()' );