mysql - 修改 WpDataTables 的 MySQL 查询

Question

我正在 WpDataTables 中构建一个表，其中涉及两种自定义帖子类型:“申请人”和“评论”。我正在尝试创建一个表，显示所有已由正在查看 WpDataTable 的当前用户(审阅者)审阅的申请人。每个申请人可以有一个或多个评论。

该查询的运行方式与我希望在 MySQL 中运行的方式完全相同，但是，WpDataTables 由于某种原因不喜欢它。我相信这是因为子查询。

SELECT a.ID, r.post_title, a.post_title, a.guid, r.post_type, a.post_type, r.post_author,
b.meta_value review_app_score,
c.meta_value review_app_comment
FROM (SELECT * FROM `wppm_2_posts` WHERE post_author = 1 AND post_type = 'reviews' AND post_status = 'publish') as r
RIGHT JOIN `wppm_2_posts`as a ON r.post_title = a.ID
LEFT JOIN `wppm_2_postmeta` b ON r.ID = b.post_id AND b.meta_key='review_app_score'
LEFT JOIN `wppm_2_postmeta` c ON r.ID = c.post_id AND c.meta_key='review_app_comment'
WHERE a.post_type = 'applicants'
AND a.post_status = 'publish'

以下是 MySQL 结果: /image/KzvHt.png

这正是我所需要的，只是我需要它在 WpDataTables 中工作。

原因是我希望利用它们的动态占位符 (%CURRENT_USER_ID%)，而不是上面 post_author 子查询中的“1”。如:

(SELECT * FROM `wppm_2_posts` WHERE post_author = %CURRENT_USER_ID% AND post_type = 'reviews' AND post_status = 'publish')

是否有另一种方法可以在不使用子查询的情况下编写此代码？或者有另一种方法可以构建它而不需要动态占位符(例如 PHP)？

感谢任何帮助。

Answer 1

这是编写查询的专业方式(又名不是我)。不确定它是否对其他人有帮助，但希望它会。

SELECT 
    APPLICANTS.ID AS applicants_id
    ,APPLICANTS.post_title AS applicants_post_title
    ,APPLICANTS.guid AS applicants_guid
    ,APPLICANTS.post_type AS applicants_post_type
    ,REVIEWS.post_author AS reviews_author_id
    ,REVIEWS.post_type AS reviews_post_type
    ,REVIEWS.post_title AS reviews_post_title
    ,R_APP_SCORES.meta_value AS review_app_score
    ,R_APP_COMMENTS.meta_value AS review_app_comment
FROM 
    `%WPDB%posts` AS APPLICANTS 
    LEFT JOIN
    `%WPDB%posts` AS REVIEWS ON
        (REVIEWS.post_author = %CURRENT_USER_ID%) AND     
        (REVIEWS.post_title = APPLICANTS.ID) AND 
        (REVIEWS.post_type = 'reviews') AND 
        (REVIEWS.post_status = 'publish')
    LEFT JOIN 
    `%WPDB%postmeta` R_APP_SCORES ON
        (R_APP_SCORES.post_id = REVIEWS.ID) AND 
        (R_APP_SCORES.meta_key = 'review_app_score')
    LEFT JOIN 
    `%WPDB%postmeta` R_APP_COMMENTS ON 
        (R_APP_COMMENTS.post_id = REVIEWS.ID) AND
        (R_APP_COMMENTS.meta_key = 'review_app_comment')
WHERE 
    (APPLICANTS.post_type = 'applicants') AND
    (APPLICANTS.post_status = 'publish')