1

I need to retrieve data from multiple tables , wp_postmeta to get posts with a specific type ( question ) , wp_user for author informations and also wp_comments .

I need those information to use them in my rest api

I have already created a new route

add_action('rest_api_init', function () {

  register_rest_route( 'myroute/v1', 'mydata/',array(
                'methods'  => 'GET',
                'callback' => 'get_data'
      ));
});


function get_data($request) {

    $args = array(  
       'post_type' => 'question',
       'post_status' => 'publish',
       'posts_per_page' => 8,
   );

How can i join my request and get data from multiple tables

I need author's name and number of comments for every post

  • You can make multiple queries using different functions/classes, you don't have to do it all in one WP_Query call. Besides, WP_Query only works for posts, for users and comments you'll need to use different functions/classes/queries. If it's just the author of the question post and the number of comments, does the REST API endpoint for the question post type not already provide that information? – Tom J Nowell Sep 5 at 10:25
  • the REST API endpoint only provides user id , i need user name and some other data ( points, badges , avatar ... ) all in one database call – AYMEN SOUMER Sep 5 at 10:29
  • If you can write a single SQL query that returns all that you can run it with $wpdb. – Rup Sep 5 at 10:30
  • Okey , i will try $wpdb , is it safe to use this approach for getting data from DB ? – AYMEN SOUMER Sep 5 at 10:43
  • There are hooks in WP_Query that plugins can use to pick up extra fields, change ordering, etc. that you won't get by writing your own query (unless you plumb them in), but depending on what data you want it's probably fine. You should check the query performs fine with EXPLAIN but I can't see how you can go much wrong. – Rup Sep 5 at 10:49
1

How can i join my request and get data from multiple tables

You don't, instead, you break it into multiple steps, and retrieve by individually

For example, lets say I want to list the top 5 posts, and display a piece of user meta about their authors, e.g. a score. That can't be done in a single call, but that's ok, so I can instead do it in several. For example:

$args = [
    'posts_per_page' => 5
];
$query = new WP_query( $args );
if ( $query->have_posts() ) {
    while ( $query->have_posts() ) {
        $query->the_post();
        the_title(); // show the title
        $score = get_user_meta(  get_the_author_id(), 'score', true );
        echo "Author score:" . absint( $score );
    }
}

You don't need to take the user meta table and merge it into the posts table via WP_Query, it doesn't make sense. Use the appropriate API to fetch the appropriate data, don't try and smush it all into 1 call ( it doesn't make it faster, if anything it makes it slower )

  • Can you give me the equivalent for this query SELECT wp0x_posts.ID, wp0x_posts.post_title, wp0x_posts.post_date, wp0x_users.user_nicename FROM wp0x_posts INNER JOIN wp0x_users ON wp0x_users.ID=wp0x_posts.post_author and wp0x_posts.post_type='question' – AYMEN SOUMER Sep 5 at 14:33
  • Or should i just stick with $wpdb ? – AYMEN SOUMER Sep 5 at 14:35
  • Stick with WP_Query, wpdb is an absolute last resort. Trying to fetch both user data AND post data in the same query is not going to be possible via API functions, and it's not good practice. Additionally, you will loose all the benefits of filtering and object caching. Doing it all in one SQL query is a bad idea and unnecessary. Also I won't be adding a copy paste solution – Tom J Nowell Sep 5 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.