EAV to Single row of Record with Separte Column

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
EAV to Single row of Record with Separte Column
EAV to Single row of Record with Separte Column

Posted

in

, ,

by

Tags:

Comments

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

  1. Shaiful Avatar
    Shaiful

    Thanks buddy, this guide is superb!

  2. Maintain Web Avatar

    Thank you my friend.
    You saved me a lot of time. I’m starting to realize how essential good SQL skills are.

  3. TDA Avatar
    TDA

    But if there are more than 61 attributes in EAV model, Join not working in mysql .MySQL can only use 61 tables in a join…http://dev.mysql.com/doc/refman/5.1/en/joins-limits.html .. 🙁

    1. Subharanjan Avatar

      Yes, this is correct. But do we need all the attributes as a flat table ? Can’t we use get_post_meta or something for rest of the attributes? Please share any tricks if you have already found. Thanks!!

  4. Rajesh Avatar

    That is a must knowledge for a WordPress developer.
    I was wonder for a solution for a problem of mine which was made easy by this post.
    Pure Awesomeness! Cheerzz!!

  5. TDA Avatar
    TDA

    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

  6. Sooraj Avatar
    Sooraj

    Good post to retrieve wp_post & wp_postmeta table record.
    way to go…

  7. lorenzogiacomini Avatar
    lorenzogiacomini

    Great! Thanks a lot

  8. abodymagdy Avatar
    abodymagdy

    thanks so muck for that ♥ ♥

Leave a Reply

Your email address will not be published. Required fields are marked *