Crude php script to create "materialized views" of Wordpress Custom Post Types with their Post Meta data.

WP_MaterializedViews.php

<?php
require '../../../../wp-load.php';
function post_type_mv( $post_type , $keys = array('post_id')){
    global $wpdb;
    $post_meta_sql = "SELECT DISTINCT pm.meta_key
                        FROM wp_posts p
                        LEFT OUTER JOIN wp_postmeta pm ON pm.post_id = p.ID
                        WHERE p.post_type = '$post_type'";
    $post_meta = array_flip($wpdb->get_col($post_meta_sql));
    //error_log(print_r($post_meta, TRUE));
    $fields = '';
    foreach( $post_meta as $field => $pos ){
        if( strpos(strtolower($field), 'date') !== FALSE && strpos(strtolower($field), 'dated') === FALSE )
            $fields .= ",$field DATE";
        else
            $fields .= ",$field VARCHAR(255)";
        
    }
    $setkeys = '';
    foreach( $keys as $key => $keyType ){
        if( $keyType === 'UNIQUE' )
            $setkeys .= ",UNIQUE KEY $key ($key)";
        else if( $keyType ==='PRIMARY KEY' )
            $setkeys .= ",PRIMARY KEY $key ($key)";
        else
            $setkeys .= ",INDEX $key ($key)";
    }
    
    $sql1 = "DROP PROCEDURE IF EXISTS " . $post_type . "_mv;";
    $sql2 = "
        CREATE PROCEDURE " . $post_type . "_mv ()
        BEGIN
        DROP TABLE IF EXISTS " . $post_type . "_mv;
        CREATE TABLE " . $post_type . "_mv (
            post_id INT NOT NULL
           " . $fields . " " . $setkeys . "
        
        );

        INSERT INTO " . $post_type . "_mv
        SELECT p.id as 'ID'";
        foreach ( $post_meta as $field => $pos){
            if( strpos(strtolower($field), 'date') !== FALSE && strpos(strtolower($field), 'dated') === FALSE)
                $sql2 .= ", MAX(CASE WHEN pm.meta_key = '$field' then STR_TO_DATE(pm.meta_value, '%Y-%m-%d') ELSE NULL END) as '$field' ";
            else
                $sql2 .= ", MAX(CASE WHEN pm.meta_key = '$field' then pm.meta_value ELSE NULL END) as '$field' ";
        }
        $sql2 .= "
            FROM
                wp_posts p
            LEFT JOIN wp_postmeta pm ON (pm.post_id = p.ID)
            WHERE p.post_type = '$post_type'
            GROUP BY p.ID
        ;
        END;";
    $sql3 = "CALL " . $post_type . "_mv();";

    error_log("Executing query");
    $wpdb->query($sql1);
    $wpdb->query($sql2);
    $wpdb->query($sql3);
}

post_type_mv('posttypehere', array('post_id' => 'PRIMARY KEY'));