请求协助编写WordPress数据库查询语句：获取前6条新房产记录

Got it, let's build the exact SELECT query you need to pull the 6 newest property posts with all the required details. I'll assume your properties are set up as a custom post type (common for real estate in WordPress, usually named something like property) and that you're storing the extra property details (bedrooms, bathrooms, price, etc.) as post meta fields.

Here's the query, tailored to standard WordPress database structure:

SELECT
    p.post_title AS property_title,
    pm_bed.meta_value AS bedrooms,
    pm_bath.meta_value AS bathrooms,
    pm_price.meta_value AS property_price,
    pm_currency.meta_value AS price_currency,
    att.post_title AS image_name,
    att_meta.meta_value AS image_path
FROM
    wp_posts p
LEFT JOIN
    wp_postmeta pm_bed ON p.ID = pm_bed.post_id AND pm_bed.meta_key = 'bedrooms' -- Replace with your actual meta key for bedrooms
LEFT JOIN
    wp_postmeta pm_bath ON p.ID = pm_bath.post_id AND pm_bath.meta_key = 'bathrooms' -- Replace with your actual meta key for bathrooms
LEFT JOIN
    wp_postmeta pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = 'property_price' -- Replace with your actual meta key for price
LEFT JOIN
    wp_postmeta pm_currency ON p.ID = pm_currency.post_id AND pm_currency.meta_key = 'price_currency' -- Replace with your actual meta key for currency
LEFT JOIN
    wp_postmeta pm_thumb ON p.ID = pm_thumb.post_id AND pm_thumb.meta_key = '_thumbnail_id'
LEFT JOIN
    wp_posts att ON pm_thumb.meta_value = att.ID AND att.post_type = 'attachment'
LEFT JOIN
    wp_postmeta att_meta ON att.ID = att_meta.post_id AND att_meta.meta_key = '_wp_attached_file'
WHERE
    p.post_type = 'property' -- Replace with your custom post type slug
    AND p.post_status = 'publish'
ORDER BY
    p.post_date DESC
LIMIT 6;

Key Adjustments for Your Specific Setup: #

• Custom Post Type: If your properties use a different slug (like real_estate instead of property), update the p.post_type = 'property' line to match.
• Meta Field Keys: Double-check your post meta keys for bedrooms, bathrooms, price, and currency. These are set when you created the custom fields (via plugins like ACF or custom code), so swap out the meta_key values with your actual ones.
• Table Prefix: If your WordPress database uses a prefix other than wp_ (e.g., my_wp_), update all table names to match (e.g., my_wp_posts instead of wp_posts).
• Featured Images: This query pulls the post's official WordPress featured image. If you store property images in a custom field instead, you'll need to adjust the joins targeting the attachment data.

This query returns exactly the 6 most recent published properties, with all the core details you requested—no extra map or sorting data included, just what you need to replicate the top section of your "All Properties" page.

内容的提问来源于stack exchange，提问作者Karsten Dall Sørensen