[Resolved] Using posts_join and posts_where to query posts/postmeta with another table

[Author]

Posts

[chrisG1876]

Hello Nigel,

I hope you are all well.

As you might remember, I'm building a classified ad site with Toolset. Since WP_QUERY could not query the posts table with another table using the JOIN statement to display only ads whose authors have an active membership, I would need to create a custom field for my ad custom post type that would need to be updated whenever there's a change in my member's membership status and then use it in my View's filter.

Well, it turned out that was not easy at all. After working on it for weeks, I still could not get it to work correctly. It has something to do with how my membership plugin sends the event status when someone's membership status changes. It does it asynchronously so I could not tell which event status comes first or second or third... or when it will come...

So I've been looking for an alternative of doing it, and I've found these 2 filter hooks posts_join and posts_where that would allow me to query the posts and postmeta tables with another table in the database at once.

I was able to write the scripts to add the JOIN and WHERE statements to the original query. But the problem is, I don't know how to restrict/assign the new query only to a specific query done by View. As a result, it looks like it applies to every SQL query and crashes my site.

So my question is, what variables or commands can I use to restrict or assign the execution of the scripts below only to a specific query created by the View plugin?

I know with a post, you can restrict it by its ID ($post->ID). Do we have View ID ($view->ID)? Nope.

These are my scripts:

function add_join_clause_to_view_sql( $join, $query ) {
	global $wpdb, $post, $wp_query;

	$postmeta_table_name = $wpdb->postmeta;
	$posts_table_name = $wpdb->posts;
	$members_table_name = $wpdb->prefix . 'members';
	
	$join .= "
				INNER JOIN
					$members_table_name
						ON
							$posts_table_name.post_author = $members_table_name.user.id
			";
	return $join;
}

add_filter('posts_join', 'add_join_clause_to_view_sql', 10, 2);


function add_where_clause_to_view_sql( $where, $query ) {
	global $wpdb, $post, $membership_ids;
	
	$members_table_name = $wpdb->prefix . 'members';
	
	$where .= "
				AND
					$members_table_name.memberships IN $membership_ids
			";
	return $where;
}

add_filter( 'posts_where' , 'add_where_clause_to_view_sql', 10, 2 );

Thank you very much.

chris

[Nigel]

Hi Chris

The current WP_Query object is available in those filters, so I would examine the queries to see what you can use to pin-point the particular queries where you want to modify the join or where clauses.

So, for now just use this:

add_filter( 'posts_join', 'tssupp_test_join', 10, 2 );
function tssupp_test_join( $join, $query ){

	$type = $query->get('post_type');

	if ( 'post-type-slug' == $type ) {

		error_log(print_r($query, true));
	
	}

	return $join;
}

Presumably you have a View querying a particular post type. Edit the above for the post type in question, and then visit a page that contains the View, then check what is output in your logs.

You should see the complete content of the query object.

I'm guessing that the post type alone isn't enough to pinpoint the query, but you can see if there are any other query properties you can use to narrow it down.

You will probably just need to use the get method as per the above example, but the available methods and properties are described here: https://codex.wordpress.org/Class_Reference/WP_Query#Methods_and_Properties

[chrisG1876]

Hi Nigel!

Yes, it worked!!! You're a genius!!!

I looked into the content of the query object, but it didn't seem to contain anything that would differentiate it from another View's query.

But with the post object, it showed the View's slug ($post->post_content), so I used it and it seemed to work well. I haven't tried to run it on another View in another page, though. But at least it didn't crash my site now.

I wish I had known of these 2 hooks earlier. It would've saved me from weeks of pulling my hair 🙂

Thank you very much!

chris