title: Wordpress wp_query filter using posts_groupby - how to COUNT values in joined table?
body: <p>try to join my custom table to WP_Query, here is my wp_query code</p>
  
  <pre><code>     $the_query = new WP_Query( array('post_type'=&gt; $post_type, 'paged' =&gt; $paged ,'posts_per_page' =&gt; 1, 'meta_query' =&gt; $meta_query, 'tax_query' =&gt; $tax_query, 'post_status' =&gt; 'publish', 'follower_id' =&gt; '') );
  
  if ( $the_query-&gt;have_posts() ) : while ( $the_query-&gt;have_posts() ) : $the_query-&gt;the_post(); 
  </code></pre>
  
  <p>I saw the online tutorial said there I can join my custom table to the wp_query,
  using posts_join, posts_group, posts_where WordPress filter.</p>
  
  <p>my problem is having a custom table, which calls wp_order_history</p>
  
  <pre><code>ID      post_id      order_number
  1       211          AFD123D342234
  2       211          dsafa23411414
  3       110          sdafsaf234234
  4       211          sdafasdfadsfs
  </code></pre>
  
  <p>there is the SQL code I want to merge to wp_query</p>
  
  <pre><code>LEFT JOIN(Select count(post_id), post_id as total_order From wp_order_history as oh WHERE oh.post_id = $wpdb-&gt;post_id group by post_id)
  </code></pre>
  
  <p>here I try to usings posts_where filter but not working :(</p>
  
  <pre><code>function custom_posts_where($where) {
              global $wpdb;
              $table_name = $wpdb-&gt;prefix . 'order_history';
              $where .= $wpdb-&gt;prepare("LEFT JOIN(Select count(post_id), post_id as total_order From wp_order_history as oh WHERE oh.post_id = $wpdb-&gt;post_id group by post_id)");
              return $where;
          }
  add_filter('posts_where', 'custom_posts_where');
  </code></pre>
  
  <p>The result I want to get in the wp_query loop,  count the total of  orders each business have. </p>
  
  <p>UPDATE, seem like code running through, but how can i output this? inside the wp_query loop? $the_query->total?</p>
  
  <pre><code>add_filter( 'posts_join', 'custom_posts_join', 10, 2 );
      function custom_posts_join( $join, $query ) {
  
          global $wpdb;
          //* if main query and search...
          // if ( is_main_query() &amp;&amp; is_search() ) {
  
              //* join term_relationships, term_taxonomy, and terms into the current SQL where clause
              $join .= "
                  LEFT JOIN 
                      (SELECT count(b.post_id) as total, b.post_id FROM wp_order_history as b group by b.business_id) as c
                  ON c.post_id = $wpdb-&gt;posts.ID ";
  
          // }
          return $join;
  
      }
  </code></pre>
  
  <p><strong>problem solve, thanks Hobo</strong></p>