Select post id where some values are true and sort them with a value among them

Question

Age-old question(#1, #2), I still am struggling with the issue. Now trying with a simple SQL Query:

global $wpbd;
$post_table = $wpdb->prefix . 'posts';
$meta_table = $wpdb->prefix . 'postmeta';
$fs_query = "SELECT $post_table.ID
                FROM $post_table
            INNER JOIN $meta_table
                ON ( $post_table.ID = $meta_table.post_id )
            WHERE 1=1
                AND $post_table.post_type = 'services'
                AND $post_table.post_status = 'publish'
                AND (
                    $meta_table.meta_key = 'example_featured'
                    AND $meta_table.meta_value = 1
                    )
                AND $meta_table.meta_key = 'example_sort'
            GROUP BY $post_table.ID
            ORDER BY $meta_table.meta_value+0 ASC
            LIMIT 0, 8";
            my_var_dump($fs_query);

TABLE 1 : wp_posts

+------------+--------------+----------------+
|     ID     |   post_type  |   post_status  |
+------------+--------------+----------------+
|     1      |   services   |     publish    |
+------------+--------------+----------------+

TABLE 2 : wp_postmeta

+------------+------------+------------------+--------------+
|     ID     |   post_id  |     meta_key     |  meta_value  |
+------------+------------+------------------+--------------+
|     1      |      1     | example_featured |       1      |
|     2      |      1     | example_sort     |       5      |
+------------+------------+------------------+--------------+

Struggling with making this SQL query:
I want all the post_id where post_type = 'services', and post_status = 'publish', and the value for the associate meta_value (example_featured) is 1 (one). And then want to sort the result using another meta value I passed using another meta_key (example_sort). If I remove AND $meta_table.meta_key = 'example_sort' from the query then get those post IDs, but with the query above I get nothing at all.

How can I do it?

This is unrelated to your original question but there are properties for the posts and postmeta table. Change $wpdb->prefix . 'posts' to $wpdb->posts and $wpdb->prefix . 'postmeta' to $wpdb->postmeta. codex.wordpress.org/Class_Reference/wpdb#Tables — Nathan Dawson
Thank you. I did it because in one of my project it's creating problem with the prefix. :) BTW without that it's still working. Thanks for the tips. — Mayeenul Islam

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-11-09T14:02:57

You are already doing a group by, so you can approach this using conditional aggregation. This means moving some of the conditions to a having clause. I also introduced table aliases -- they make queries easier to write and to read:

SELECT p.ID
FROM $post_table p INNER JOIN
     $meta_table m
     ON (p.ID = m.post_id )
WHERE p.post_type = 'services' AND
      p.post_status = 'publish'
GROUP BY p.ID
HAVING SUM(m.meta_key = 'example_featured' AND m.meta_value = 1) > 0
ORDER BY MAX(CASE WHEN m.meta_key = 'example_sort' then m.meta_value end)
LIMIT 0, 8;

This is an example of a set-within-sets query -- that is, you are looking at logic for all the meta keys for a given post. The logic has two pieces, the filtering piece (on "example_featured") and the ordering piece (on "example_sort"). The problem you faced is that this information is on two different rows in the meta table.

The HAVING clause counts the number of rows that meet the condition on "example_featured". If any are encountered, the SUM() is greater than 0. As an aside, if you wanted to guarantee that the value was never there, then you would use = 0 rather than > 0.

The ORDER BY clause extracts the value from the row where the key is "example_sort". If there is more than one match, then the maximum value is used. If there are no matches, then the result of the MAX() expression is NULL, and sorted accordingly.

Select post id where some values are true and sort them with a value among them

TABLE 1 : wp_posts

TABLE 2 : wp_postmeta

1 Answers