Query that loads a custom type AND posts whose ids are not inside those custom types custom fields

Question

On my news website I have posts and another custom type called "aggregato". This type has 10 custom fields, "link01", "link02"... "link10".

Both posts and aggregato has a meta key, called "home", that is used to say WP to put the post on front page.

I want to make a query for the front page that loads the 12 latest post OR aggregato that has a home>0. I also want to exclude the posts that are linked inside the aggregato.

If this was a simple SQL query I'd create a clause that looked something like this:

WHERE post.home>0
AND aggregato.home>0
AND post.id<>aggregato.link01
...
AND post.id<>aggregato.link10
LIMIT 12

A workaround could be a double query. The first of the aggregato type and then a second for the post type. Still, I'd rather find the right WP_Query instead.

Answer 1

First off: You theoretically can query the WordPress database with raw MySQL, of course.
See wpdb's get_results method. That might save you a second query, but obviously will not return proper WP_Post objects.

That being said, nope, if you want to use WP_Query, you will not get around using two queries. I'd still recommend it over the above. Maybe less efficient, but certainly much less of a hassle. Unless your posts table is ginormously massive, it should not be a problem.

First, grab the latest 12 aggregato type posts, iterate over the metadata and thereafter grab the actual 12 to display. Might not look the slickest, but anyhoo:

$twelve_aggregatos = new WP_Query(
    array(
        'post_type' => 'aggregato',
        'posts_per_page'=> 12,
        'meta_query' => array(
            array(
                'key' => 'home',
                'value' => 0,
                'compare' => '>'
            )
        )
    )
);

$linked_posts = array();
if ( $twelve_aggregatos->have_posts() ) {
    while ( $twelve_aggregatos->have_posts() ) {
        $twelve_aggregatos->the_post();
        $current_ID = get_the_ID();
        for ( $i = 1; $i <= 10; $i++ ) {
            /* The below asumes that the custom fields hold post IDs  */
            $linked_ID = get_post_meta(
                $current_ID,
                'link'.str_pad( $i, 2, '0', STR_PAD_LEFT ),
                true
            );
            /* Should they hold URLs, use the following instead */
            /* $linked_ID = url to postid(
                get_post_meta(
                    $current_ID,
                    'link'.str_pad( $i, 2, '0', STR_PAD_LEFT ),
                    true
                )
            ); */
            if ( ! in_array( $linked_ID, $linked_posts ) ) {
                $linked_posts[] = $linked_ID;
            }
        }
    }
}
wp_reset_postdata();

$twelve_reults = new WP_Query(
    array(
        'post_type' => array( 'post', 'aggregato' ),
        'posts_per_page'=> 12,
        'meta_query' => array(
            array(
                'key' => 'home',
                'value' => 0,
                'compare' => '>'
            )
        )
        'post__not_in' => $linked_posts
    )
);

/* Loop over results, do something */

wp_reset_postdata();

Answer 2

$exclude_posts = array();//array of id's that are in aggregato custom fields
$query = new WP_Query( array(
    'post_type' => array( 'post', 'aggregato' ),
    'posts_per_page'=> 12,
    'posts_not_in' => $exclude_posts,
    'meta_query' => array(
        array(
            'key' => 'home',
            'value' => '_wp_zero_value',
            'compare' => '>'
             )
    )
    ) );

You can check WP_Query for more info

You can use url_to_postid() to get id's from url's