SQL query to list all posts of specific post type with a specific set of associated postmeta values as a column

Question

I want all posts of type 'ajde_events' as a row each with a column for some basics from the post table and some ( not all ) associated postmeta values. I have cribbed the following from this post How to display multiple Post meta_key/meta_values by SQL query but as the op says in a comment it only returns a single row ( which seems to be the row where the location is a string starting with "Z" which I suspect is the "Highest" i.e. maximum string.

SELECT p.ID, p.post_title,
MAX(IF(PM.meta_key = 'evcal_location', PM.meta_value, NULL)) AS location,
MAX(IF(PM.meta_key = 'evcal_srow', PM.meta_value, NULL)) AS start_time
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
WHERE post_type = 'ajde_events' AND post_status = 'publish'
ORDER BY p.ID

How do I get a row for each relevant post?

Answer 1

Using built-in WP functions to get the data is usually a better approach. It can be more performant, and is preferable security-wise because you're using prebuilt queries instead of building your own.

In a plugin, you can use something like:

<?php
// Get all published CPTs
$allEvents = get_posts(array(
    'post_type' => 'ajde_events',
    'post_status' => 'publish',
    'numberposts' => -1
));
// If any were found
if($allEvents) {
    // Open/create your csv (your code here)
    // Loop through all CPTs and grab postmeta
    foreach($allEvents as $event) {
        $meta = get_post_meta($event);
        // Save the postmeta with the post object
        $event->evcal_location = $meta['evcal_location'];
        $event->evcal_srow = $meta['evcal_srow'];
        // Add the full post object, with postmeta, to an array
        $fullData[] = $event;
    }
    // Reset the query
    wp_reset_postdata();
    // Save to the csv and close it (your code here)
}
?>

Where to put this code depends on how you want the plugin to work. Perhaps you want to create an admin screen where a user can manually press a button to download the CSV, or perhaps you want a cron job that automatically builds it every day and emails the file.

Answer 2

The query I posted just needs the ORDER BY clause changing to GROUP BY and it then does what I wanted.