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 and
ORDER 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.