how to use transient method?

Question

It was suggested to use a transient method to remove the duplicate queries Duplicate Queries

Why does this don't work? Is it the wrong way to use it

 private function get_acf_list()
  {
      if(!class_exists('acf')){
        return array();
      }
      global $wpdb;
      if ( false === ( $pgggo_qry = get_transient( 'pgggo_acf_query_results' ) ) ) {
        // It wasn't there, so regenerate the data and save the transient
        $pgggo_qry = "SELECT post_excerpt as 'field_name', post_title as 'field_label' FROM {$wpdb->prefix}posts where post_type = 'acf-field'";
        set_transient( 'pgggo_acf_query_results', $pgggo_qry, 12 * 7200 );
      }

      $pgggo_list_results = $wpdb->get_results($pgggo_qry, ARRAY_A);
      $acf_field_array    = array();
      if (!empty($pgggo_list_results)) {
          foreach ($pgggo_list_results as $value) {
              $acf_field_array[$value['field_name']] = $value['field_label'];
          }
      }
      return $acf_field_array;
  }

Answer 1

First, you don't need a raw SQL query, and by using one you give up all of WP's optimisations.

For example, if you run the query twice, it runs twice. If you had used WP_Query to getch those posts though, it would save them the first time to avoid making additional queries. It's even possible those posts are present as a result of other queries.

On top of that, caching plugins have limited options for working with your query, and you have to do all the validating/sanitising yourself.

So do something like this:

$q = new WP_Query(  [
    'post_type' => 'acf-field',
    'posts_per_page' => 50,
] );
if ( $q->have_posts() ) {
    while ( $q->have_posts() )  {
        $q->the_post();
        // ...
    }
    wp_reset_postdata();
}

Second, you don't need to faff around renaming columns and fetching individual fields. You're doing additional heavy lifting, and making it difficult to draw from already fetched data, and avoiding WP APIs.

Since your field_name is just the post excerpt, and your field_label is the post title, just use get_the_excerpt() and get_the_title() inside the loop.

The final result will naturally avoid duplicate queries, as WP will store what it fetched in WP_Cache and use it the second time around. If you install an object cache drop in with Redis/MemcacheD/etc, it'll avoid the query entirely.

A general rule of thumb in WP, is that if you need to do a raw SQL query to fetch data from anything other than a custom table, either something has gone terribly wrong, or you're making an incredibly expensive query that needs to go in a CLI command or a cron job.