0

The following code returns the maximum value for the meta key, "session" across all posts of post-type, "calibration".

I have two other fields in the "calibration" post-type that I would like returned: "countdown" and "timer". However, tacking on these fields at the end of the first line of the SELECT statement... SELECT max( cast( meta_value as unsigned) ), countdown, timer ... returns NULL.

How should the function be changed to return the additional fields?

function getMaxSessionNoAndSettings() {
  global $wpdb;
  $userID = get_current_user_id();

  $lastSessionNumber = $wpdb->get_var( $wpdb->prepare( 
            "   
                SELECT max( cast( meta_value as unsigned) )
                FROM wp_postmeta 
                LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
                WHERE meta_key = 'session' AND post_status = 'publish' AND post_type = 'calibration' AND post_author = $userID
            ", 
            $post_type
        ) );
 // var_dump($lastSessionNumber);
  return $lastSessionNumber;
}
add_action( 'pre_get_posts', 'getMaxSessionNoAndSettings');
  • pre_get_posts is a chance to modify the main query object, you need to accept and return a WP_Query object, neither of which you do. I would also note that the SQL query you wrote will be super expensive to execute. Post meta tables are optimised for finding data when you already know the post ID. Can you explain what you're trying to do without the technical baggage? E.g. are you trying to make only the session with the highest value show on an archive? Or are you trying to display it elsewhere? – Tom J Nowell Aug 25 '17 at 11:08
  • @TomJNowell, thanks for following up twice. The post type is effectively a quiz which a user takes as many times as they want. The "session" field is counter. So the next time a User takes a "quiz", the code above determines if a value exists (had they taken a quiz previously) and if it does, I increment "session" for the current quiz. I need to get values for the other two fields. I am not displaying data in an archive, etc., just using them to make decisions. – quint Aug 25 '17 at 15:21
  • Would it not be faster then to simply retrieve the newest post of type calibration that has the same author? That would be a stupendously fast query in comparison to the one you're trying to do – Tom J Nowell Aug 25 '17 at 16:51
  • As for the additional fields, I'm not sure what you mean by include them, you can use get_post_meta to retrieve post meta. Did you mean to filter by those values, or did you just want to use them in the resulting template? – Tom J Nowell Aug 25 '17 at 16:53
  • With regard to your question about retrieving the most recent calibration post for that author, absolutely that would provide what's needed. I guess this is probably very simple to do but I am much more familiar with javaScript than I am with PHP. Is this what you are referring to? <?php get_most_recent_post_of_user( $user_id ); ?> – quint Aug 25 '17 at 20:33
0

That's not how pre_get_posts works, or what it's for.

The pre_get_posts filter, is an opportunity to change set or modify the arguments of a WP_Query object before it fetches posts from the information.

For example, here is one that modifies the homepage query to only fetch 5 posts:

function tomjn_fetch_five_posts( \WP_Query $q ) {
    if ( $q->is_main_query() && $q->is_home() ) {
        $q->set( 'posts_per_page', 5 );
    }
}
add_action( 'pre_get_posts', 'tomjn_fetch_five_posts' );

In your case, it's not clear what you're trying to do, but lets say we only want to show the post with the highest session meta key value, we might do:

$q->set( 'meta_key', 'session');
$q->set( 'meta_value', $lastSessionNumber );

However, both this, and the SQL query you wrote have major performance and traffic scaling problems, the kind that can't be swept under the rug or optimised a way

Performance Caching and Traffic Scaling

The crux of the problem is that the data isn't stored efficiently. If you want to store things in a way that you can query them quickly to find posts, you have to use taxonomies. That's what taxonomies are built for, else categories and tags would be stored in post meta

Meta queries are extremely slow

Don't expect more than a handful of people to be able to view that page concurrently before things start falling over and page load times skyrocket. Meta queries are super expensive

Maybe you can cache them though?

You can never cache that page

Because it shows different things to different users, if you started caching the page it would show the same thing to all users.

Data Storage

If session is a post ID, you should use the post parent instead to link them.

Otherwise, you would be better off hooking into post save, and checking if the session is the highest number and storing it in user meta, so that next time you need it, you've already figured it out and it's a simple/fast get_user_meta call

  • Tom, to follow up on this post: The code above is part of a plugin where I use wp_localize_script to pass the value to the js file that the plugin loaded. Presently the plugin passes 4 values. With regard to performance, are you saying that as more and more users access that "page" and thus the plugin executes, then performance will degrade significantly? – quint Aug 25 '17 at 15:41
  • querying posts by their post meta is extremely expensive as the meta table wasn't optimised for that, MySQL has to build a temporary table to do the output on as well as a full table scan, and a large chunk of memory to hold the temporary tables, so the query is super slow, and not only that but it gets exponentially slower as the number of posts and post meta increases. – Tom J Nowell Aug 25 '17 at 16:50

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.