Wordpress query relationship between custom post types

Question

I have two Custom Post Types: Movies and Directors

A Director owns many Movies.
A Director can be either an In-House or Freelance employee.

I'm using Advanced Custom Fields to make a radio group on the Director Post Type where you can set it to either In-House or Freelance.

I'm also using ACF to make a select box in the Movies Post Type where you select a Director and you get the Post Object in return.

How do I make a query so I only get Movies where the Director is an In-House employee?

Edit: I'll provide an example using plain SQL:

SELECT movies.name, directors.name
FROM movies JOIN directors ON directors.id = movies.director_id
WHERE directors.type LIKE '%In-House%'

Is there a better way of querying like this in Wordpress other than doing raw SQL?

You question is not understandable please describe it properly. — siddhesh
Thank you. @siddhesh I've added an SQL example to illustrate the simple query that actually has to happen. — dadord

Rich Rich · Accepted Answer · 2015-03-17T16:18:18

Inside your loop for movies you need something like the following:

<?php 
$inhouse = get_posts(array(
    'numberposts' => -1,
    'post_type' => 'DIRECTOR_POST_TYPE_NAME',
    'meta_key' => 'SELECT_FIELD_NAME',
    'meta_value' => 'IN_HOUSE_FIELD_VALUE'
    )
));

?>
<?php if( $inhouse ): ?>
    <ul>
    <?php foreach( $inhouse as $ihdirector ): ?>
        <li>
            <a href="<?php echo get_permalink( $doctor->ID ); ?>">
                <?php echo get_the_title( $ihdirector->ID ); ?>
            </a>
        </li>
    <?php endforeach; ?>
    </ul>
<?php endif; ?>

Wordpress query relationship between custom post types

1 Answers