Wordpress中的meta键和meta_value查询 [英] meta key and meta_value query in wordpress

问题描述

我在wordpress中成功运行查询.查询如下.

I am running a query successfully in wordpress.Query is as follows.

SELECT wp_posts.*, wp_postmeta.meta_value FROM wp_posts, wp_postmeta, wp_term_relationships, wp_terms 
WHERE term_id = '12' AND term_taxonomy_id = '12' AND ID = post_id 
AND ID = object_id AND post_type = 'property' AND post_status = 'publish' 
AND meta_key = 'property_amount' AND replace( replace(meta_value, ',', ''), '"', '' ) >= 1 
GROUP BY wp_posts.ID ORDER BY replace( replace(meta_value, ',', ''), '"', '' ) DESC 
LIMIT 0, 10

但是我想在上面的查询中再添加一个meta_key及其值条件，所以我将查询更改为此

But I want to add one more meta_key and its value condition in above query so I changed my query to this

  SELECT wp_posts.*, wp_postmeta.meta_value FROM wp_posts, wp_postmeta, wp_term_relationships, wp_terms 
  WHERE term_id = '12' AND term_taxonomy_id = '12' AND ID = post_id
  AND ID = object_id AND post_type = 'property' AND post_status = 'publish' 
  AND meta_key = 'property_amount' AND replace( replace(meta_value, ',', ''), '"', '' )
>= 1
  AND meta_key="property_land_type" and meta_value IN ('L','H','C')
  GROUP BY wp_posts.ID ORDER BY replace( replace(meta_value, ',', ''), '"', '' ) DESC 
  LIMIT 0, 10

在第一次查询中，以下行是多余的

Following line is extra in first query

meta_key="property_land_type" and meta_value in ('L','H','C')

但是它不起作用.如何执行.由于我有很多其他基于此查询的查询，因此我这次无法编写WP_Query.

But it is not working. How to do this.I can not write WP_Query this time as I have lots of other queries based on this query.

提前谢谢！

推荐答案

对于一个列，您有多个meta_keys，对于meta_key和诸如

You have multiple meta_keys against one column you need IN() for both meta_key and value like

meta_key IN( 'property_amount','property_land_type') 
AND meta_value IN ('L','H','C',replace( replace(meta_value, ',', ''), '"', '' ) >= 1)

尝试此查询

SELECT wp_posts.*, wp_postmeta.meta_value FROM 
wp_posts, wp_postmeta, wp_term_relationships, wp_terms 
WHERE term_id = '12' AND term_taxonomy_id = '12' AND ID = post_id 
AND ID = object_id AND post_type = 'property' AND post_status = 'publish' 
AND meta_key IN( 'property_amount','property_land_type') 
AND meta_value IN ('L','H','C',replace( replace(meta_value, ',', ''), '"', '' ) >= 1)
GROUP BY wp_posts.ID ORDER BY replace( replace(meta_value, ',', ''), '"', '' ) DESC 
LIMIT 0, 10

编辑

尝试使用join进行此操作，我已经两次为两个meta_key加入了wp_postmeta

Try this one with join ,i have joined wp_postmeta two times for two meta_keys

SELECT wp.*, wpm1.meta_value AS `propert_type`,
wpm2.meta_value AS `property_amount`
FROM 
wp_posts  wp
LEFT JOIN wp_postmeta wpm1 ON (wp.ID = wpm1.post_id)
LEFT JOIN wp_postmeta wpm2 ON (wp.ID = wpm2.post_id)
INNER JOIN wp_term_relationships wptr ON (wp.ID = wptr.object_id)
INNER JOIN `wp_term_taxonomy` wptt ON (wptr.term_taxonomy_id = wptt.term_taxonomy_id)
INNER JOIN wp_terms wpt ON (wptt.term_id = wpt.term_id)

WHERE wpt.term_id = '12' AND wptr.term_taxonomy_id = '12' 
AND  wp.post_type = 'property' AND wp.post_status = 'publish'
AND wpm1.meta_key = 'property_land_type' 
AND wpm2.meta_key = 'property_amount'
AND REPLACE( REPLACE(wpm2.meta_value, ',', ''), '"', '' ) >= 1
AND wpm1.meta_value IN ('L','H','C') AND  wpm1.meta_value IS NOT NULL AND  wpm2.meta_value IS NOT NULL 
GROUP BY wp.ID ORDER BY REPLACE( REPLACE(wpm2.meta_value, ',', ''), '"', '' ) DESC 
LIMIT 0, 10

这篇关于Wordpress中的meta键和meta_value查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！