plugin development - Group By in a Metaquery

admin管理员组

文章数量:1427091

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. My Query Call looks like this (these are the arguments to new WP_Query

 Array
(
    [post_type] => projects
    [post_status] => publish
    [posts_per_page] => 500
    [paged] => 1
    [meta_key] => child-project-why-become-fundraiser
    [orderby] => meta_value_num
    [order] => DESC
    [meta_query] => Array
        (
            [relation] => AND
            [project_goal] => Array
                (
                    [key] => project_goal
                    [compare] => EXISTS
                )

            [total_raised_amount] => Array
                (
                    [key] => total_raised_amount
                    [compare] => EXISTS
                )

            [parent-project-id] => Array
                (
                    [key] => parent-project-id
                    [compare] => EXISTS
                )

        )

)

Thanks.

本文标签： plugin developmentGroup By in a Metaquery