Group By in a Metaquery

by sanders   Last Updated May 15, 2019 21:08 PM

I would like to add a group by in a meta Query.

The current generated query is as follows:

SELECT SQL_CALC_FOUND_ROWS wp_posts.post_title, 
wp_postmeta.meta_value, mt2.meta_value
FROM wp_posts
     INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
     INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
     INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
     INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
WHERE 1 = 1
AND (
    wp_postmeta.meta_key = 'child-project-why-become-fundraiser'
    AND
    (
            mt1.meta_key = 'project_goal'
            AND
            mt2.meta_key = 'total_raised_amount'
            AND
            mt3.meta_key = 'parent-project-id'
        )
)
AND wp_posts.post_type = 'projects'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value + 0 DESC
LIMIT 0, 500;

What I Acutally would like to achieve is this

SELECT SQL_CALC_FOUND_ROWS wp_posts.post_title, 
wp_postmeta.meta_value, mt2.meta_value
FROM wp_posts
     INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
     INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
     INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
     INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
WHERE 1 = 1
AND (
    wp_postmeta.meta_key = 'child-project-why-become-fundraiser'
    AND
    (
            mt1.meta_key = 'project_goal'
            AND
            mt2.meta_key = 'total_raised_amount'
            AND
            mt3.meta_key = 'parent-project-id'
        )
)
AND wp_posts.post_type = 'projects'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID, wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value, cast(mt2.meta_value as unsigned) DESC
LIMIT 0, 500

Please note the differences in the GROUP BY andORDER BY` in the two queries.

I am using the WP_Query object.

Thanks.



Related Questions


Updated March 14, 2017 00:08 AM

Updated March 08, 2019 09:08 AM

Updated April 15, 2015 20:03 PM

Updated November 27, 2017 09:08 AM