What does the $posts_join filter join to?

Question

What does the $posts_join filter join to?

LeonardShelby

2020年4月15日 11:12

I am trying to use the posts_join filter to join 2 tables: wp_postmeta to wp_posts. I am a novice at SQL, so I'm not sure if I am confused because I don't understand how WordPress is implementing posts_join, or if I just don't understand the SQL syntax.

My questions are:

Why is only one table used in the join statement? I thought 2 tables were needed for a join statement
Is this because the posts_join filter assumes you are always joining a table to wp_posts?

The example in the Codex at https://codex.wordpress.org/Plugin_API/Filter_Reference/posts_join uses a similar situation of joining wp_postmeta to wp_posts.

$join .= "LEFT JOIN $wpdb-postmeta ON $wpdb-posts.ID = $wpdb-postmeta.post_id ";

Based on this example, I can see that $wpdb-postmeta (i.e. wp_postmeta) is explicitly stated as one of the tables to join. But from my understanding of SQL, I thought $wpdb-posts would also have to be stated BEFORE the ON clause, like so:

$join .= "$wpdb-posts LEFT JOIN $wpdb-postmeta ON $wpdb-posts.ID = $wpdb-postmeta.post_id ";

So I am very confused why $wpdb-posts is not specified before the ON clause. How else does WordPress know what is the second table to be joined?

Thank you for your help.

Topic join-tables Wordpress sql

Category Web

Dave Hilditch · Accepted Answer · 2020年4月15日 11:12

The posts_join filter is referencing joins to the 'posts' table, i.e. wp_posts

That's why it's called the 'posts_join' filter.

You need to use this filter if you wish to be able to select columns from additional tables in your output or if you want to be able to filter by columns from other tables in your WHERE clause - although if you are joining in order to filter, you could just use a WHERE EXISTS or WHERE NOT EXISTS instead.

Milo · Accepted Answer · 2015年10月7日 16:42

posts_join is only a part of the full SQL query, the table you're joining to is referenced earlier in the query.

You can see the full query with the posts_request filter.

See the documentation for the rest of the query filters.

What does the $posts_join filter join to?

About