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

7 Comments

  1. 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!!

    Reply
  2. 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

    Reply

Leave a Reply