0

Hi so i'm new to wordpress development but not new to programming.

My problem is

I am trying to return a custom post type in a WP_Query and order them by a custom meta key, my query looks like this

$mp_wp_query = new WP_Query(
        array(
            'posts_per_page'      => 10,
            'order_by'               => 'meta_value_num',
            'order'              => 'DESC',
            'post_status'         => 'publish',
            'post_type' => 'tribe_events',
            'meta_key' => 'wpb_post_views_count',
            'post_parent' => '0'
        )
    );
echo "Last SQL-Query: {$mp_wp_query->request}";

This is generating a SQL query as below

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' ) WHERE 1=1 AND wp_posts.post_parent = 0 AND ( wp_postmeta.meta_key = 'wpb_post_views_count' AND ( mt1.meta_key = '_EventStartDate' ) ) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) AND (wp_postmeta.meta_value >= '2018-05-22 20:15:06' OR (wp_postmeta.meta_value <= '2018-05-22 20:15:06' AND tribe_event_end_date.meta_value >= '2018-05-22 20:15:06' )) GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_posts.post_date DESC LIMIT 0, 10

You can see at the end the order by is ORDER BY EventStartDate can a custom post type somehow specify how it should be ordered and my order is being ignored? I have had a look though the code of the custom post type when register_post_type is called and the args and cannot see anything that would suggest it does.

  • Note that you shouldn't be manually storing views, it's expensive as it involves a DB write on every page, making caching impossible, and it's incredibly unreliable as you'll face race conditions that can corrupt the data, undercounting is a major problem. Use a 3rd party service or software built for these things instead – Tom J Nowell May 22 '18 at 18:37
  • @TomJNowell thanks I did mull this over, I will eventually use Google Analytics API to get the views, im just doing it as cheap as possible and the site gets around 100 visitors a day so the DB expense is minimal and I hear I can use some partial caching to get around the non cachable issue. – squareborg May 22 '18 at 18:48
1

There is nothing wrong, the reason the data is there is to handle the problem of ordering when 2 posts have the same value for their meta key.

ORDER BY EventStartDate DESC, wp_posts.post_date DESC

Here we see that post data is not the only thing it's ordering on, EventStartDate is what's being ordered on, and those are defined at the beginning:

MIN(wp_postmeta.meta_value) as EventStartDate, 
MIN(tribe_event_end_date.meta_value) as EventEndDate

Which leads me to the conclusion that this SQL query is not the one for your WP_Query, but for another query entirely.

Additionally, if the end result is what you want, and the performance is good, then SQL used isn't particularly relevant.


Upon further inspection, you're trying to order by page views, but have used post meta is a means of storing page views. This leads to some problems:

  • every single page load has a DB write, this is awful for performance, the frontend should not make DB writes unless a user is logged in and filling out forms or setting things, especially not for simply viewing content
  • It's incredibly unreliable. Updating the view count is not atomic, and there's time between fetching the meta value and setting it. Opening the page in several tabs is enough to cause race conditions that result in under counting.
  • Caching pages is not possible, reducing performance, scalability, and acting as a negative hit to SEO

Use a 3rd party service or dedicated software to track this, then poll its APIs. E.g. Google Analytics or Jetpack stats

| improve this answer | |
  • Hi Tom thanks for your valuable information, I have edited my question with how I am getting the SQL query used, Its basically echo "Last SQL-Query: {$mp_wp_query->request}"; is there someway this would be another query? – squareborg May 22 '18 at 19:00
  • and you're sure you're not doing anything with pre_get_posts? You've not said yet if you're getting the expected results or incorrect results. I'd also avoid embedding variables in strings like that as it isn't possible to use escaping functions that way – Tom J Nowell May 22 '18 at 19:16
  • Thanks I don't need to escape it, its there so I can debug it, even though now its rather redundant as I can find it in the debugger. Its not really the results thats the issue, im really confused about why EventStartDate, EventEndDate are even in the query, what machinary has got involved to instruct WP_Query to even think about them? – squareborg May 22 '18 at 19:22
  • You're absolutely sure there are no pre_get_posts filters active adding or modifying those parameters? Where does tribe_events come from? – Tom J Nowell May 23 '18 at 1:42

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.