Filter wordpress query with SQL-CodePudding

I want to create a custom filter for the wordpress posts query. I tested the SQL query, and it returns the posts. When added to the parse_query hook it generates an SQL sytax error. I think it's too complex to use the query_vars. In SQL $wpdb-> is replaced with 'wp_'. How can I get this SQL query to work with the parse_query hook?

public function filter_request_query( $query ) {
  //modify the query only if it admin and main query.
  if( !( is_admin() AND $query->is_main_query() ) ) {
    return $query;
  }

  $post_type = 'product';
  $term_id = 492;

  global $wpdb;
  $results = $wpdb->query(
    $wpdb->get_results( "
      SELECT * FROM {$wpdb->posts} t2 WHERE t2.post_type = %s AND t2.ID IN
      (SELECT post_parent FROM {$wpdb->posts} t1, {$wpdb->term_relationships} t3
      WHERE t1.ID IN
      (SELECT t3.`object_id`
      WHERE t3.`term_taxonomy_id`= %d))
      ORDER BY ID", $post_type, $term_id) );
  return $results;
}

add_filter('parse_query', 'filter_request_query', 10);

CodePudding user response：

The solution is to modify the query_vars of the original query. Using $wpdb most likely isn't possible using this filter. The ids of the parent are retrieved with WP_Query, and moved into an array. The query_vars are modified to include these ids using post__in

  //modify the query_vars.
  $sub_query = new WP_Query(
    array(
    'post_type' => 'custom_pt',
    'posts_per_page' => -1,
    'tax_query' => array(
      array(
        'taxonomy' => 'custom_tax',
        'field' => 'slug',
        'terms' => 'term'
      )
    ),
    'fields' => 'id=>parent'
  ));

  $temp = [];
  foreach( $sub_query->posts as $post_ob ) {
    $temp[] = $post_ob->post_parent;
  }

  $query->query_vars['post__in'] = $temp;
  return $query;

CodePudding user response：

EDIT:

The get_results don't take any variables. Hence, i updated the code like this which does take variables:

function filter_request_query( $query ) {
  //modify the query only if it admin and main query.
  if( !( is_admin() AND $query->is_main_query() ) ) {
    return $query;
  }

  $post_type = 'product';
  $term_id = 492;

    global $wpdb;
    $results = $wpdb->query(
        $wpdb->prepare(
            "SELECT * FROM {$wpdb->posts} t2 WHERE t2.post_type = %s AND t2.ID IN 
            (SELECT post_parent FROM {$wpdb->posts} t1, {$wpdb->term_relationships} t3 WHERE t1.ID IN 
                (SELECT t3.`object_id` from {$wpdb->term_relationships} WHERE t3.`term_taxonomy_id`= %d)
            ) ORDER BY ID", $post_type, $term_id
        )
    );
    return $results;
}

add_filter('parse_query', 'filter_request_query', 10);

It's running without any errors. I don't know about the results as I don't have the same environment with similar data to test. I've checked it with the query monitor plugin and found that it is executing the hook and function in the theme.

OLD ANSWER

So, i tested this query on one of my woocommerce databases and found out that the query is not correct.

Here's the correct query without syntax error:

SELECT * FROM {$wpdb->posts} t2 WHERE t2.post_type = %s AND t2.ID IN
      (SELECT post_parent FROM {$wpdb->posts} t1, {$wpdb->term_relationships} t3
      WHERE t1.ID IN
      (SELECT t3.`object_id` from {$wpdb->term_relationships}
      WHERE t3.`term_taxonomy_id`= %d))
      ORDER BY ID", $post_type, $term_id) );

The from {$wpdb->term_relationships} was missing in the 3rd SELECT query and hence it was giving a syntax error. I added it and it started executing in my PhpMyAdmin.

Hope your code works after this fix as that looks correct to me.