mysql - 如何查询 "Entity Attribute Value"表和另一个表之间的多个关系？

Question

我正在尝试查询“实体属性值”表，就像您在 WordPress 的 postmeta 表中找到的那样，其中包含有关帖子的元数据。

请注意，我不是从 WordPress 内部查询数据库。

下面是 wp_postmeta 表中的一些示例数据，其中显示了同一 _EventVenueID 处两个不同事件的数据:

+---------+---------+-------------------+---------------------+
| meta_id | post_id | meta_key          | meta_value          |
+---------+---------+-------------------+---------------------+
| 3914    | 422     | _EventStateDate   | 2016-01-27 01:00:00 |
| 3915    | 422     | _EventEndDate     | 2016-01-27 02:00:00 |
| 3400    | 422     | _EventVenueID     | 333                 |
| 3916    | 422     | _EventOrganizerID | 410                 |
| 3917    | 423     | _EventStartDate   | 2017-01-28 01:00:00 |
| 3918    | 423     | _EventEndDate     | 2017-01-28 02:00:00 |
| 3401    | 423     | _EventVenueID     | 333                 |
| 3919    | 423     | _EventOrganizerID | 411                 |
+---------+---------+-------------------+---------------------+

下面是来自 wp_posts 表的截断版本的一些示例数据:

+---------+------------+-----------------+-------------+
| ID      | post_title | post_type       | post_status |
+---------+------------+-----------------+-------------+
| 422     | Picnic     | tribe_events    | publish     |
| 423     | Concert    | tribe_events    | publish     |
| 411     | Bob Jones  | tribe_organizer | publish     |
| 410     | Mary Smith | tribe_organizer | publish     |
| 333     | Park       | tribe_venue     | publish     |
+---------+------------+-----------------+-------------+

如您所见，实体分为三种:事件、组织者和 field 。每个事件可以有一个 field 和一个或多个组织者。另请注意，wp_posts.ID 列对 wp_postmeta.post_id 列以及 wp_postmeta.meta_value 列进行索引。

因此，如果我想提取特定 field (在本例中为公园)的事件数据，我可以使用以下查询来实现:

SELECT pm1.meta_value AS _EventVenueID,
  p.post_title,
  pm2.meta_value AS _EventStartDate,
  pm3.meta_value AS _EventEndDate,
  pm4.meta_value AS _EventOrganizerID
  FROM wp_posts p
  LEFT JOIN wp_postmeta AS pm1 ON (p.ID = pm1.post_id AND pm1.meta_key='_EventVenueID')
  LEFT JOIN wp_postmeta AS pm2 ON (p.ID = pm2.post_id AND pm2.meta_key='_EventStartDate')
  LEFT JOIN wp_postmeta AS pm3 ON (p.ID = pm3.post_id AND pm3.meta_key='_EventEndDate')
  LEFT JOIN wp_postmeta AS pm4 ON (p.ID = pm4.post_id AND pm4.meta_key='_EventOrganizerID')
  WHERE p.post_type = 'tribe_events' AND p.post_status = 'publish'
  HAVING _EventVenueID = 333

现在假设我也想选择组织者的名称作为列。因此，换句话说，我想将 wp_postmeta 表中的 _EventOrganizerId 与 wp_posts 表中的数据相关联，这样我也可以拉取输出与事件相关的组织者的 post_title。

我该怎么做？

更新评论中有关所需输出的问题:

目前，我在行结果上得到如下输出:

  [
    '333',
    'Picnic',
    '2016-01-27 01:00:00',
    '2016-01-27 02:00:00',
    '410'
  ],

我想要得到的是同样的东西，但也有组织者的名字:

  [
    '333',
    'Picnic',
    '2016-01-27 01:00:00',
    '2016-01-27 02:00:00',
    '410',
    'Mary Smith'
  ],

Answer 1

只是更多相同...

SELECT pm1.meta_value _EventVenueID
     , p1.post_title event_type
     , p2.post_title organiser
     , pm2.meta_value _EventStartDate
     , pm3.meta_value _EventEndDate
     , pm4.meta_value _EventOrganizerID
  FROM wp_posts p1 
  JOIN wp_postmeta pm1 
    ON p1.ID = pm1.post_id 
   AND pm1.meta_key = '_EventVenueID'
  LEFT 
  JOIN wp_postmeta pm2 
    ON p1.ID = pm2.post_id 
   AND pm2.meta_key = '_EventStartDate'
  LEFT 
  JOIN wp_postmeta pm3 
    ON p1.ID = pm3.post_id 
   AND pm3.meta_key = '_EventEndDate'
  LEFT 
  JOIN wp_postmeta pm4 
    ON p1.ID = pm4.post_id 
   AND pm4.meta_key = '_EventOrganizerID'

  LEFT
  JOIN wp_posts p2
    ON p2.id = pm4.meta_value 

 WHERE p1.post_type = 'tribe_events' 
   AND p1.post_status = 'publish'
   AND pm1.meta_value = 333;