Add key/value metas to sql query as columns

Question

I made an SQL query which is returning the content and the metas as columns

SELECT 
    content.content_id AS id,
    content.content_title AS author,
    content.content_body AS review,
    content.content_date as date,
    m0.meta_value AS email,
    m1.meta_value AS origin,
    m2.meta_value AS recommend,
    m3.meta_value AS avatar
FROM
    molly_contents content,
    molly_content_meta m0,
    molly_content_meta m1,
    molly_content_meta m2,
    molly_content_meta m3
WHERE
    content.content_type = 'review'
        AND content.content_id = m0.meta_content_id
        AND m0.meta_name = 'email'
        AND content.content_id = m1.meta_content_id
        AND m1.meta_name = 'origin'
        AND content.content_id = m2.meta_content_id
        AND m2.meta_name = 'recommendation'
        AND content.content_id = m3.meta_content_id
        AND m3.meta_name = 'avatar'
ORDER BY
    content.content_date DESC

The problem is, if there is a missing meta it wont return that record. Is there any solution to return each record as a single row with the meta names as column names, and if there is no meta name then leave it empty?

Gordon Linoff · Accepted Answer · 2016-02-13 16:21:49Z

1

I think this is simpler as conditional aggregation:

SELECT c.*,
       MAX(CASE WHEN m.meta_name = 'email' THEN m.metavalue END) as email,
       MAX(CASE WHEN m.meta_name = 'origin' THEN m.metavalue END) as email,
       MAX(CASE WHEN m.meta_name = 'recommendation' THEN m.metavalue END) as recommendation,
       MAX(CASE WHEN m.meta_name = 'avatar' THEN m.metavalue END) as avatar
FROM molly_contents c left join
     molly_content_meta m
     ON c.content_id = m.meta_content_id
GROUP BY c.content_id;

The fundamental flaw with your query is that you are using commas in the from clause. Here is a simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax. Then, you would be able to change your joins to LEFT JOIN, which is another way of solving your problem.

answered Feb 13, 2016 at 16:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hunrik Over a year ago

Thank you, this is what i needed.

Collectives™ on Stack Overflow

Add key/value metas to sql query as columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related