Joining/summing custom fields in WordPress database query

1 Views

Despite Googling this for 2 hours and trying a variety of JOINS and UNIONS and ALIASES and whatnot, I still can’t retrieve in one table the result of two subqueries in the wp_postmeta table from the WordPress database (I don’t even care about wp_posts here, all the data I’m interested in is in postmeta).

I have custom fields (managed with the ACF plugin) that look like the following in the database, with one key for regions and another for a numeric value.

Read More
|   ID   |   meta_key           |   meta_value   |
|   1    |   group_name         |   Japan        |
|   1    |   facility_index     |   0.83         |
|   2    |   group_name         |   USA          |
|   2    |   facility_index     |   4.51         |

I need a query that returns the following so that I can calculate aggregates by region (for instance, the sum of facility_index for all facilities, by country/region, i.e. by group_name): 

|   ID   |   group_name  |   facility_index   |
|   1    |   Japan       |   0.83             |
|   2    |   USA         |   4.51             |

This query lists group_name twice instead of listing facility_index in the 3rd column: 

SELECT post_id, meta_value, result2.meta_value FROM
(
(
SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = "group_name"
)
UNION
(
SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = "facility_index"
)
) AS result2
GROUP BY post_id

Edit to display the final query that does exactly what I wanted, thanks to the accepted answer below: 

SELECT
  meta1.meta_value AS group_name,
  meta2.meta_value AS facility_index,
  SUM(meta2.meta_value) AS 'fac_index_geo' 
FROM wp_posts post
  LEFT JOIN wp_postmeta meta1 ON meta1.post_id = post.ID AND meta1.meta_key = 'group_name'
  LEFT JOIN wp_postmeta meta2 ON meta2.post_id = post.ID AND meta2.meta_key = 'facility_index'
WHERE
  post.post_type = 'facility'
  GROUP BY group_name
Post Views: 1

Related posts

1 comment

  1. As I understand you are looking for a custom SQL query – i.e. you don’t want to use a WP_Query object.

    For this I’d use some JOINS instead of subqueries.
    Also I encourage you to query for the post_type, as otherwise you might get data from autosaves/revisions or similar wrong results.

    /*
INNER JOIN
This query will only return posts that contain all meta values
i.e. all 'post' items with group_name AND facility_index
*/
$sql = "
SELECT
  post.ID,
  meta1.meta_value AS group_name,
  meta2.meta_value AS facility_index
FROM {$wpdb->posts} post
  INNER JOIN {$wpdb->postmeta} meta1 ON meta1.post_id = post.ID AND meta1.meta_key = 'group_name'
  INNER JOIN {$wpdb->postmeta} meta2 ON meta2.post_id = post.ID AND meta2.meta_key = 'facility_index'
WHERE
  post.post_type = 'post'
";

$data = $wpdb->get_results( $sql );

    Just make sure to update the post_type condition to match your required post type.

    Alternatively you can use this query if you want to also get posts that have a missing meta value

    /* 
LEFT JOIN
This query will return all posts even if they have missing meta values
i.e. if group_name or facility_index is empty, then NULL is returned in the column
*/
$sql = "
SELECT
  post.ID,
  meta1.meta_value AS group_name,
  meta2.meta_value AS facility_index
FROM {$wpdb->posts} post
  LEFT JOIN {$wpdb->postmeta} meta1 ON meta1.post_id = post.ID AND meta1.meta_key = 'group_name'
  LEFT JOIN {$wpdb->postmeta} meta2 ON meta2.post_id = post.ID AND meta2.meta_key = 'facility_index'
WHERE
  post.post_type = 'post'
";

Comments are closed.