WordPress环境下4表多连接查询夜间签到用户的诗歌数据

Let's walk through how to build this multi-table query, and tackle those WordPress storage quirks you're dealing with.

Step 1: Map Out Relationships & Key Filters #

First, let's align all the pieces we need to connect:

• Your checkin table's ID field links directly to WordPress user IDs (which correspond to the post_author column in wp0k_posts).
• The checkin.date timestamp needs to filter users who checked in during "night"—we'll define that as 8 PM (20:00) to 6 AM (06:00) the next day (feel free to adjust this window to match your exact definition).
• We only care about posts where post_type = 'um_story' (your poetry content).

Step 2: Full SQL Query (With WordPress-Specific Fixes) #

Here's the complete query, with comments explaining each part to avoid common WordPress pitfalls:

SELECT 
    p.post_title, 
    p.post_content
FROM 
    wp0k_posts p
JOIN 
    checkin c ON p.post_author = c.ID
WHERE 
    -- Target your custom poetry post type
    p.post_type = 'um_story'
    -- Ensure we only pull live, published posts (WordPress stores drafts/trash in the same table!)
    AND p.post_status = 'publish'
    -- Filter for night check-ins: convert timestamp to hour, cover cross-day window
    AND (
        HOUR(FROM_UNIXTIME(c.date)) >= 20 
        OR HOUR(FROM_UNIXTIME(c.date)) <= 5
    );

Step 3: Address WordPress Storage Mechanism Challenges #

The main hurdles here are tied directly to how WordPress stores content:

• Post Status Overlook: WordPress keeps all post states (drafts, trashed, pending) in wp0k_posts. Adding p.post_status = 'publish' ensures you're only grabbing visible, live poetry posts—omit this if you need to include drafts too.
• User ID Validation: Double-check that checkin.ID exactly matches the ID column in wp0k_users (since wp0k_posts.post_author references wp0k_users.ID). A mismatch here will break your join entirely.
• Timestamp Conversion: Since checkin.date is a Unix timestamp, we use FROM_UNIXTIME() to convert it to a readable date/time, then HOUR() to extract the hour for our night filter. Tweak the hour range (20 to 5) to fit your definition of "night".

Optional Customizations #

• If you want to include user details like display names, add a join to wp0k_users:

  SELECT 
      u.display_name,
      p.post_title, 
      p.post_content
  FROM 
      wp0k_posts p
  JOIN 
      checkin c ON p.post_author = c.ID
  JOIN
      wp0k_users u ON u.ID = c.ID
  WHERE 
      p.post_type = 'um_story'
      AND p.post_status = 'publish'
      AND (HOUR(FROM_UNIXTIME(c.date)) >= 20 OR HOUR(FROM_UNIXTIME(c.date)) <= 5);
  

• Adjust the hour values in the WHERE clause if your "night" window is different (e.g., 21:00 to 07:00).

内容的提问来源于stack exchange，提问作者JJ Pagac