Articles of join tables

What does the $posts_join filter join to?

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 […]

$wpdb – joining shows no result

For one of my client’s WP site, I’m making a plugin, where I’m using the db Query. I’ve two custom tables dedicated to the plugin: user_req — stores user chosen posts along with some custom fields’ data. user_req_admin — stores some very basic things, with a special foreign key user_req_id. Table: user_req Table: user_req_admin My […]

List author's posts with SQL

I want display authors’posts with the code below but authors’ name aren’t showing. What is wrong? <? // query test $qry = $wpdb->get_results( $wpdb->prepare(” SELECT * FROM $wpdb->posts WHERE post_type =’post’ AND post_status=’publish’ order by ‘ID’ DESC LIMIT 5 “)); echo “<table width=100% cellspacing=1 cellpadding=5 bgColor=’#cccccc’>”; foreach ( $qry AS $k => $v ){ $qry_name […]

Inner Join user tables to select users with roles

I am trying to view a list of users (who are music artists) with the following function. function show_authors($ltr) { global $wpdb; $querystr = “SELECT $wpdb->users.* FROM $wpdb->users WHERE $wpdb->users.display_name LIKE ‘$ltr%’ ORDER BY $wpdb->users.display_name”; /** $querystr = ” SELECT * FROM $wpdb->users INNER JOIN $wpdb->usermeta.meta_value ON $wpdb->users.user_id = $wpdb->usermeta.user_id AND $wpdb->usermeta.meta_key = ‘wp_capabilities’ AND […]

Using $wpdb to fetch posts with meta data

I have a post type “shows” with 500 posts in it. I want to list all 400 in one page listing their title + 5 meta fields and a url to the show post thumbnail. I initially went the classic wordpress way by using a custom WP_Query, looping through them with appropriate template tags such […]

Join new table with SQL query

I created a plugin with a new table (wp_soundcloud) and I need to retrieve the data from that new table along with the post data. Here is my query: $querystr = ” SELECT $wpdb->posts.*, FROM $wpdb->posts LEFT JOIN $wpdb->soundcloud ON($wpdb->posts.ID = $wpdb->soundcloud.idpost) WHERE $wpdb->posts.post_type = ‘soundcloud’ AND $wpdb->posts.post_name = ‘$name’ “; $pageposts = $wpdb->get_results($querystr, OBJECT); […]

Query All users that has post

I have this query so far : $user_args = array( ‘role’ => ‘frontend_vendor’, ‘orderby’ => ‘display_name’, ‘order’ => ‘ASC’, ‘number’ => $no, ‘offset’ => $offset ); $user_query = new WP_User_Query( $user_args ); Now what I want to do is to get all users that has only posts, i have an example query that i want […]

How to use filter hook posts_join for querying taxonomy terms in posts_where?

For the last few hours, I have been trying to use the filter hooks posts_where and posts_join to search for posts based on taxonomy terms, in this case, the taxonomy is ‘category’ and the term is ‘politics’ This is what I tried and resulted in a empty query: This line is inside the hook posts_where […]

Join inside a wpdb query.. confused!

I need to get a return from a query that provides me with email, username or nicename as well as post title based on author id. The query is to run once daily via cron and look for posts that have spent a specific live time before they come up for removal (the query is […]

How to search for posts IN OR title OR content OR author?

I have looked at many posts and questions regarding searches for custom meta, but I want to search for posts and include those if the search query matches an author name. I have come up with the following code but it is not working. For some reason, it returns posts with status “revision” First, I […]