SQL query to get post and its meta_values as one row of record (single data grid)

How to query EAV attributes into separate columns ? Here is the SQL query to get post and its meta_values as one row of record (single data grid). 🙂 🙂 WordPress stores the post data mainly in two tables (i.e. wp_posts and wp_postmeta). The first table stores the data which are common and apply to all the individual entities( like the entities of type post, page, media, attachment, custom post type etc..) Second table stores the attributes of those entities as key-value pair. So this is kind of following EAV model. For those who doesn’t know about EAV, it stands for Entity Attribute & Value. Generally when we are not following EAV style, we store the entity and its attributes as separate columns in one table, but in EAV each attribute is stored as rows. Usually it is done for a purpose: to allow columns to be added without having to make DDL changes . 6NF and EAV as principles and concepts offer substantial benefits, and performance increases.

But on certain scenarios we want the attributes stored as rows to be fetched and displayed as columns in single data grid. This is the most tedious part to select the data from two different tables and showing as single row of entity. However we can fetch and display the data by writing query somewhat like the following.SQL query to get post and its meta_values as one row of record.

SELECT  wp_posts.ID, wp_posts.post_title, pm1.meta_value as field1, pm2.meta_value as field2, pm3.meta_value as field3 
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='metakey1')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id  AND pm2.meta_key='metakey2') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id  AND pm3.meta_key='metakey3') 
WHERE wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish' 
AND ((pm1.meta_key = 'metakey1') OR (pm2.meta_key = 'metakey2') OR (pm3.meta_key = 'metakey3')) 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

This will display the posts which is having at least one of the above meta_keys set in the post_meta table. To display all the posts including those which doesn’t have any meta values in the post_meta tables.

SELECT  wp_posts.ID, wp_posts.post_title, pm1.meta_value as field1, pm2.meta_value as field2, pm3.meta_value as field3 
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='metakey1')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id  AND pm2.meta_key='metakey2') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id  AND pm3.meta_key='metakey3') 
WHERE wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish' 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC