CODEWITHSUNDEEP
mysqldatabasewordpress
StevieD
StevieD

Reputation: 7443

How do you query across multiple relationships between an "Entity Attribute Value" table and another table?

I'm trying to wrap my head around querying an "Entity Attribute Value" table like you find in WordPress' postmeta table which contains meta data about the posts.

Note that I am not querying the database from within WordPress.

So here's some sample data from the wp_postmeta table that shows data from two different events at the same _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                 |
+---------+---------+-------------------+---------------------+

And here is some sample data from a truncated version of the wp_posts table:

+---------+------------+-----------------+-------------+
| 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     |
+---------+------------+-----------------+-------------+

As you can see, there are three kinds of entities: events, organizers and venues. Each event can have one venue and one or more organizers. Also notice that wp_posts.ID column indexes the wp_postmeta.post_id column as well as the wp_postmeta.meta_value column.

So, if I want to pull the event data for a particular venue (in this case, the Park), I can do that with this query:

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

Now let's say I also want to select the name of the organizer as a column, too. So, in other words, I want to relate the _EventOrganizerId in the wp_postmeta table to data that is in the wp_posts table so I can also pull out the post_title for the organizer associated with the event.

How do I do this?

UPDATE for question on desired output in comments:

Currently, I get an output like this on a row result:

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

What I want to get is the same thing, but with the name of the organizer, too:

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

Upvotes: 0

Views: 102

Answers (1)

Strawberry
Strawberry

Reputation: 33945

It's just more of the same...

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;

Upvotes: 1

Related Questions