在Sally CJ的善意帮助下question, 我能够创建一个WP查询，但它仍然会给我意外的结果。当我在原始MySQL中编写查询时，它工作得很好，但在WordPress创建的查询中，它就不工作了。
以下是我的WP查询参数：
$args = array(
\'post_type\' => \'tdlrm_store_item\',
\'post_status\' => \'publish\',
\'tax_query\' => array(
array(
\'taxonomy\' => \'store-category\',
\'field\' => \'term_id\',
\'terms\' => 514,
\'include_children\' => false
)
),
\'meta_query\' => array(
array(
\'relation\' => \'OR\',
\'has_tdlrm_mp\' => array(
\'key\' => \'tdlrm_mp\',
\'type\' => \'NUMERIC\',
),
\'no_tdlrm_mp\' => array(
\'key\' => \'tdlrm_mp\',
\'compare\' => \'NOT EXISTS\',
),
),
array(
\'relation\' => \'OR\',
\'has_1C_quantity_total\' => array(
\'key\' => \'1C_quantity_total\',
\'type\' => \'NUMERIC\',
),
\'no_1C_quantity_total\' => array(
\'key\' => \'1C_quantity_total\',
\'compare\' => \'NOT EXISTS\',
),
)
),
\'orderby\' => \'none\',
\'tdlrm_commands\' => array(\'tdlrm_orderby\' => true)
);
posts_orderby 过滤器：add_filter( \'posts_orderby\', function ( $orderby, $query ){
if (!isset($query->query_vars[\'tdlrm_commands\'][\'tdlrm_orderby\'])
|| $query->query_vars[\'tdlrm_commands\'][\'tdlrm_orderby\'] !== true) return $orderby;
global $wpdb;
return "
CASE {$wpdb->postmeta}.meta_key
WHEN \'tdlrm_mp\' THEN 1
WHEN \'1C_quantity_total\' THEN 2
ELSE 3
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN \'tdlrm_mp\' THEN {$wpdb->postmeta}.meta_value+0
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN \'1C_quantity_total\' THEN {$wpdb->postmeta}.meta_value+0
END DESC,
{$wpdb->posts}.post_date DESC
";
}, 10, 2 );
tdlrm_mp 元go首先，按元值排序，从低到高，然后是其他，按1C_quantity_total 元值，从高到低，然后是那些没有1C_quantity_total 元数据，按发布日期排序。SELECT
* FROM wp_postmeta
LEFT JOIN wp_posts on wp_postmeta.post_id = wp_posts.ID
LEFT JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id
WHERE wp_posts.post_type = \'tdlrm_store_item\'
AND wp_posts.post_status = \'publish\'
AND wp_term_relationships.term_taxonomy_id = 514
ORDER BY
CASE wp_postmeta.meta_key
WHEN \'tdlrm_mp\' THEN 1
WHEN \'1C_quantity_total\' THEN 2
ELSE 3
END ASC,
CASE wp_postmeta.meta_key
WHEN \'tdlrm_mp\' THEN wp_postmeta.meta_value+0
END ASC,
CASE wp_postmeta.meta_key
WHEN \'1C_quantity_total\' THEN wp_postmeta.meta_value+0
END DESC,
wp_posts.post_date DESC
LIMIT 0,12
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = \'tdlrm_mp\' )
LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id AND mt3.meta_key = \'1C_quantity_total\' )
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (514) )
AND ( ( wp_postmeta.meta_key = \'tdlrm_mp\' OR mt1.post_id IS NULL ) AND ( mt2.meta_key = \'1C_quantity_total\' OR mt3.post_id IS NULL ) )
AND wp_posts.post_type = \'tdlrm_store_item\'
AND ((wp_posts.post_status = \'publish\'))
GROUP BY wp_posts.ID
ORDER BY
CASE wp_postmeta.meta_key
WHEN \'tdlrm_mp\' THEN 1
WHEN \'1C_quantity_total\' THEN 2
ELSE 3
END ASC,
CASE wp_postmeta.meta_key
WHEN \'tdlrm_mp\' THEN wp_postmeta.meta_value+0
END ASC,
CASE wp_postmeta.meta_key
WHEN \'1C_quantity_total\' THEN wp_postmeta.meta_value+0
END DESC,
wp_posts.post_date DESC LIMIT 0, 12
tdlrm_mp meta，他们的订单很好，但其余的按日期订购，仅此而已。是什么导致了我编写的MYSQL查询和WP MYSQL查询之间的差异，以及如何使其全部工作？Update.
事实证明，WordPress会多次加入Posteta表，具体取决于元查询参数。这就是为什么： //php
$args[\'meta_query\'] = array(
\'relation\' => \'AND\',
array(
\'relation\' => \'OR\',
array(
\'key\' => \'tdlrm_mp\',
\'type\' => \'NUMERIC\',
),
array(
\'key\' => \'tdlrm_mp\',
\'compare\' => \'NOT EXISTS\',
),
),
array(
\'key\' => \'1C_quantity_total\',
\'type\' => \'NUMERIC\',
)
);
//MySQL query by WordPress
"...LEFT JOIN wp_postmeta ON (
wp_posts.ID = wp_postmeta.post_id
)
LEFT JOIN wp_postmeta AS mt1 ON (
wp_posts.ID = mt1.post_id
AND mt1.meta_key = \'tdlrm_mp\'
)
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
WHERE..."
1C_quantity_total 元键必须引用为mt2.meta_key, 不wp_postmeta.meta_key. 我像这样重写了orderby过滤器，它可以工作：add_filter( \'posts_orderby\', function ( $orderby, $query ){
if (! isset( $query->query[\'tdlrm_commands\'] ) ||
! $query->query[\'tdlrm_commands\'][\'default_filter\']
) return $orderby;
global $wpdb;
return "
CASE {$wpdb->postmeta}.meta_key
WHEN \'tdlrm_mp\' THEN 1
ELSE 2
END ASC,
CASE {$wpdb->postmeta}.meta_key
WHEN \'tdlrm_mp\' THEN {$wpdb->postmeta}.meta_value+0
END ASC,
CASE mt2.meta_key
WHEN \'1C_quantity_total\' THEN mt2.meta_value+0
END DESC
";
}, 10, 2 );
1C_quantity_total 在任何情况下都将恰好处于第三个加入的Posteta表中。有没有办法做得更好？例如，“您可以使用”；查找联接的表1C_quantity_total 实际在中，然后按该表中的值排序；。Update 2
谢谢Sally CJ帮助我，你太棒了！在试图解决这个问题的过程中，我遇到了一些问题，这些问题可能对任何来到这里的人都有用：question, question, question, JOIN speed, question, question, question.