Question 2710 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Filter wordpress query with SQL

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);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search