Display posts if a custom field value is equal to another custom field value

Question

I have 2 custom post types "Shops" and "Restaurants".

Each of these have custom fields associcated with them.

For example, One field in Shops is "Shop ID" and one in Restaurants is "Restaurant ID".

I want to query both Custom Post Types and if Shop ID is 20, I want to display all Restaurants with ID 20.

I've been toying with this code:

            <?php

            $args = array(
                'numberposts'   => -1,
                'post_type'     => 'shops', 'restaurants',
                'meta_query'    => array(
                    'relation'      => 'AND',
                    array(
                        'meta_key'      => 'shop-id',
                        'meta_value'    => '12345',
                        'compare'   => '='
                    ),
                    array(
                        'meta_key'      => 'restaurant-id',
                        'meta_value'    => '12345',
                        'type'      => 'NUMERIC',
                        'compare'   => '>'
                    )
                )
            );

            // query
            $the_query = new WP_Query( $args );

            ?>
            <?php if( $the_query->have_posts() ): ?>
                <?php while( $the_query->have_posts() ) : $the_query->the_post(); ?>
                    <h4>
                        <a href="<?php the_permalink(); ?>">
                            <?php the_title(); ?>
                        </a>
                    </h4>
                <?php endwhile; ?>
            <?php endif; ?>

Note I'm also using Advanced Custom Fields.

Answer 1

There's some contradiction between your question and the code you provided, so I'm not exactly sure which of two scenarios you're dealing with.

Scenario 1 - You want to show all Shops with a specific value for shop-id and all Restaurants with the same specific value for restaurant-id. Let's say that value is 20. You can do this with a single WP_Query() loop that looks for both post types where either custom field has the specified value. This assumes that Shops will never have a value for restaurant-id and vice-versa.

$args = array(
    'posts_per_page' => -1,
    'post_type' => array( 'shop', 'restaurant' ),
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'restaurant-id',
            'value' => 20,
            'compare' => '='
        ),
        array(
            'key' => 'shop-id',
            'value' => 20,
            'compare' => '='
        )
    )
);
$query = new WP_Query( $args );

Scenario 2 - You want to show all Shops, each Shop followed by all Restaurants that have the same value for restaurant-id as the value of the current Shop's shop-id.

$shops = new WP_Query(
    array(
        'posts_per_page' => -1,
        'post_type' => 'shop'
    )
);

if ( $shops->have_posts() ) {
    while ( $shops->have_posts() ) {
        $shops->the_post();
        ...
        $shop_id = get_post_meta( $post->ID, 'shop-id', true );
        $restaurants = new WP_Query(
            array(
                'posts_per_page' => -1,
                'post_type' => 'restaurant',
                'meta_query' => array(
                    array(
                        'key' => 'restaurant-id',
                        'value' => $shop_id,
                        'compare' => '='
                    )
                )
            )
        );
        if ( $restaurants->have_posts() ) {
            while ( $restaurants->have_posts() ) {
                $restaurants->the_post();
                ...
            }
        }
    }
}

Answer 2

I don't know of a way to do this using WP_Query, but you can write custom SQL to achieve this:

global $wpdb;

$id = '20';

$sql = "SELECT * FROM $wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_id AND pm.meta_key IN ('shop-id','restaurant-id')
WHERE p.post_type IN ('shops','restaurants')
AND (
    ( pm.meta_key = 'shop-id' AND pm.meta_value = %i )
    OR ( pm.meta_key = 'restaurant-id' AND pm.meta_value = %i )
)";

$query = $wpdb->get_results($wpdb->prepare($sql,$id));

This will get back all the results where the post type is a shop or restaurant, and where the shop-id or restaurant-id matches the provided ID.

Answer 3

This works below, but I only want it to compare the values and return "shops", at the moment, it is returning both shop and restaurants where the value is 20!

            $args = array(
                'posts_per_page' => -1,
                'post_type' => array( 'shop', 'restaurant' ),
                'meta_query' => array(
                    'relation' => 'OR',
                    array(
                        'key' => 'restaurant-id',
                        'value' => 20,
                        'compare' => '='
                    ),
                    array(
                        'key' => 'shop-id',
                        'value' => 20,
                        'compare' => '='
                    )
                )
            );
            $query = new WP_Query( $args );