Categories
MySql PHP WordPress

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.

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.

EAV to Single row of Record with Separte Column
EAV to Single row of Record with Separte Column

By Subharanjan

Zend Certified Engineer (PHP), WordPress Developer, Software Engineer at Axelerant, India. I love PHP, MySql, Wordpress, BuddyPress, JavaScript, HTML5, CSS. Currently playing around with WordPress Development, Plugin Development, Theme Customization, Drupal. Subharanjan Mantri

8 replies on “SQL query to get post and its meta_values as one row of record (single data grid)”

Hi ,
I have found the solution.
1. Use Max, Case in query but there will be performance issue when huge EAV structure.

SELECT l.id, l.name, l.email, lm.id, lm.name, lm.email,
MAX(CASE WHEN f.title = ‘Firstname’ THEN fd.val END) FirstName,
MAX(CASE WHEN f.title = ‘Lastname’ THEN fd.val END) Lastname,
MAX(CASE WHEN f.title = ‘Country’ THEN fd.val END) Country,
MAX(CASE WHEN f.title = ‘Town’ THEN fd.val END) Town
FROM lists l
JOIN listmembers lm ON l.id=lm.nl
JOIN fieldsdata fd ON fd.eid = lm.id
JOIN fields f ON f.id = fd.fid
GROUP BY l.id, lm.id
———————————————————————————————————
2 .Build Temp table ( it will be good for export, search). Rebuild temp table if there are changes in EAV.
———————————————————————————————————–
3.Use Mongo DB. There is no issue for performance .

Cheer Up!
TDA

Leave a Reply