Get terms of posts with category with sql

I have a taxonomy of type and a taxonomy of category.

How can I return a list of every type term of posts in a single category?

I know I can do this:

// Final array of all types:
$allTypesFromThisCategory = [];


if (have_posts()) : while (have_posts()) : the_post();
    $types = wp_get_post_terms( $post->ID, 'type' );
    foreach($types as $type){
        // Pseudo code:
        if( ! in_array($type, $allTypesFromThisCategory) ){
            // Add the type to the array
            $allTypesFromThisCategory[] = $type;
        }
    }
endwhile; endif;

print_r($allTypesFromThisCategory);

But there are a couple of problems with this.

1. There are about 1,000 posts, so it will be really slow.
2. Assuming posts_per_page isn’t -1 this query won’t always reflect all the types of all the posts, just the ones on the current page.

I’d like to write something like

global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM ...", OBJECT );

Where the ... represents:

1. Use the current category taxonomy
2. Find all the posts with that category taxonomy ID
3. Select all types taxonomy of those posts

… but the problem is I can’t / am terrible at writing joins and raw SQL.

Here’s the solution:

global $wpdb;
$wpdb->get_results( $wpdb->prepare(
    "SELECT tags.*, COUNT(tags_rel.object_id) as posts_count
    FROM
        {$wpdb->prefix}terms tags
        INNER JOIN {$wpdb->prefix}term_taxonomy tags_tax ON (tags_tax.term_id = tags.term_id)
        INNER JOIN {$wpdb->prefix}term_relationships tags_rel ON (tags_tax.term_taxonomy_id = tags_rel.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}posts posts ON (tags_rel.object_id = posts.ID)
        INNER JOIN {$wpdb->prefix}term_relationships cats_rel ON (posts.ID = cats_rel.object_id)
        INNER JOIN {$wpdb->prefix}term_taxonomy cats_tax ON (cats_rel.term_taxonomy_id = cats_tax.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}terms cats ON (cats.term_id = cats_tax.term_id)
    WHERE
        tags_tax.taxonomy = 'type'
        AND cats_tax.taxonomy = 'category'
        AND posts.post_type="post"
        AND posts.post_status="publish"
        AND cats.term_id = %d
    GROUP BY tags_tax.term_id",
    <CATEGORY_TERM_ID>  // <-- here goes the category id of current category
) );

PS. Remember that this is pretty nice opportunity to do some caching. These tags won’t change themselves. So you can compute them once and them use those cached values. This way they will be computed only during saving of posts…