Wordpress WP_Query Multiple Queries

Question

I'm trying to come up with a way of querying a CPT by a specific author but also if a meta_key has a specific value.

For example, my CPT has an author ID attached to it as well as a 'recipient' custom field. What i need is all posts of this type by this author as well as all posts of this type where 'recipient' = this author.

(Hope I've explained that well enough!)

I'm usually pretty OK with SQL but just having a random brain fart moment and can't figure this one out. Like I said, any help is much appreciated!

== Solved ==

Thanks to @3pepe3 for suggesting the meta_query parameter of WP_Query. Modified my CPT logic slightly and now have a beautifully working system.

The solution was to add a new meta field for each post containing the original sender's user id, then run two meta_queries to retrieve posts where the current user_id matches either the sender or recipient fields, likes so:

$args = array(
    'post_type' => 'activity',
    'post_status' => 'publish',
    'orderby' => 'date',
    'order' => 'DESC',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'sender',
            'value' => $user_id,
            'type' => 'BINARY',
            'compare' => '='
        ),
        array(
            'key' => 'recipient',
            'value' => $user_id,
            'type' => 'BINARY',
            'compare' => '='
        )
    )
);

$query = new WP_Query( $args );

3pepe3 · Accepted Answer · 2015-03-09 14:47:12Z

1

Helo JRM47R1X, Instead of using SQL queries you should use the WP_Query Class

<?php
$args = array(
    'post_type' => 'activity',
    'author' => $user_id,
    'post_status' => 'publish',
    'orderby' => 'date',
    'order' => 'DESC',
    'meta_query' => array(
        array(
            'key' => 'recipient',
            'value' => $user_id,
            'type' => 'BINARY',
            'compare' => '='
            ),
        ),
    );
$query = new WP_Query($args);
?>

The advantage of using the WP_Query is that you can mix meta queries, taxonomies and dates with logical operands like OR, AND BETWEEN....

answered Mar 9, 2015 at 14:47

3pepe3

61310 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JRM47R1X Over a year ago

hi @3pepe3, thanks for the suggestion, unfortunately the result set returned by this query only contains results where the author is also the recipient. I think you're onto something with the meta_query though, +1 for that!

JRM47R1X Over a year ago

Based off your answer, if I were to add a new custom field ('sender' for example) I could then use multiple meta_query arrays and use an OR operator to match either sender of receiver. A little untidy as it's essentially duplication the already available 'author' param but I can't find another way of joining the query between post data and post metadata

3pepe3 Over a year ago

In your query you retrieve the posts where $user_id is the author and then in your union you are retrieving the posts where meta_key 'recipient is the same $user_id. So it's normal that the return is only contains results where the author is also the recipient. So if you want to retrieve the post from one author to to another 'recipient' then you only need to change 'author' => $author_id, and in the array meta_query select the desired recipient 'value' => $recipient_id, (you must declare the $author_id and $recipient_id)

3pepe3 Over a year ago

So if you add the 'sender' field you only need to add a new array

$args = array( 	'post_type' => 'activity', 	'post_status' => 'publish', 	'orderby' => 'date', 	'order' => 'DESC', 	'meta_query' => array( 		'relation' => 'AND', 		array( 			'key' => 'recipient', 			'value' => $recipient_id, 			'compare' => '=', 			), 		array( 			'key' => 'sender', 			'value' => $sender_id, 			'compare' => '=' 			), 		) 	);

JRM47R1X Over a year ago

Hi @3pepe3, the problem was that I had no way of knowing the value of 'sender' and I needed a sorted list of results for the current user where the returned posts were either their own publications or they were the recipient of the post. Your last comment is almost the exact correct answer, the only difference being that the 'relation' param is 'OR' instead of 'AND'. Thanks for your help with this one though, it was frustrating to say the least when it really shouldn't have been!

JRM47R1X · Accepted Answer · 2015-03-09 13:46:29Z

Ok I eventually figured it out. Turns out all I needed was a Union on the two queries like so:

SELECT * FROM (
    SELECT $wpdb->posts.* 
        FROM $wpdb->posts 
        WHERE $wpdb->posts.post_author = $user_id
        AND $wpdb->posts.post_type = 'activity'
        AND $wpdb->posts.post_status = 'publish'
    UNION
    SELECT $wpdb->posts.*
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->postmeta.meta_key = 'recipient'
        AND $wpdb->postmeta.meta_value = $user_id
        AND $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->posts.post_type = 'activity'
        AND $wpdb->posts.post_status = 'publish'
    ) as x
ORDER BY x.post_date DESC

Hope this helps anyone else having this bizarrely obscure problem!

Collectives™ on Stack Overflow

Wordpress WP_Query Multiple Queries

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related