Using raw query instead of meta_query

  • Resolved islp

    (@islp)


    5 years, 4 months ago

    Some years ago, I purchased a premium theme from a very well know market. Only recently I found an unwanted behavior in one of its widgets.

    The widget is an event widget: it displays a certain number of rows containing a rectangle with the date, the title of the event, etc.

    Problem is I found the widget doesn’t display the events based on the event date, but, instead, it uses the publish date; so, for example, if today I post an event in 1984, it displays this event at the top of the list.

    I found the code involved is the following:

    <?php

 $args = array(
	'post_type' => 'event',
	'post_per_page' => $number,
	'showposts' => $number
     );

 $latest_events = new WP_Query($args);
 if ($latest_events->have_posts()): global $post;
?>

<?php while($latest_events->have_posts()): $latest_events->the_post();
				
$event_month = get_post_meta($post->ID, 'core_event_month', true);
$event_day = get_post_meta($post->ID, 'core_event_day', true);
$event_time = get_post_meta($post->ID, 'core_event_time', true);
$event_location = get_post_meta($post->ID, 'core_event_location', true); 
		
?>

    As you can see, the developer saves in wp_postmeta month, day and time (luckily, year too), but not a useful timestamp. Month is a string (eg. “November”), day and year are numbers. I don’t care about time at the moment.

    I tried to change the WP_Query $args in any possible way, but I could not find one that could order the results by date.

    Finally, I decided to create an hardcoded SQL version of the query, this one:

    SELECT wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta AS meta1 ON ( wp_posts.ID = meta1.post_id ) ?
INNER JOIN wp_postmeta AS meta2 ON ( wp_posts.ID = meta2.post_id ) ?
INNER JOIN wp_postmeta AS meta3 ON ( wp_posts.ID = meta3.post_id ) 
WHERE wp_posts.post_type = 'event' AND wp_posts.post_status = 'publish'
AND meta1.meta_key = 'core_event_month' AND meta1.meta_value IN ('November','December')
AND meta2.meta_key = 'core_event_day' AND meta2.meta_value >= 4
AND meta3.meta_key = 'core_event_year' AND meta3.meta_value = 2019
GROUP BY wp_posts.ID
ORDER BY STR_TO_DATE(CONCAT(meta1.meta_value," ",meta2.meta_value," ",meta3.meta_value), "%M %d %Y") DESC

    The idea in the last line is to concat the meta fields values to build a string representing a date, so that I can sort events by date.

    Now, I have a couple of questions:

    1. is it possible to create a WP_Query version of my query?
    2. is there any way to use my query in the context of the code I posted? How?

    Thanks anybody, any help is appreciated

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    WP_Query does not support that sort of query. It does have request filters where you can directly replace various SQL clauses with your preferred version, so indirectly you could use WP_Query. You can even replace the entire SQL used in one go if you wanted to, for the sake of saying you used WP_Query. (“posts_request” filter)

    But if you go that far, you may as well use the global $wpdb object methods to get the desired results. ($wpdb->query()) The thing is though with $wpdb, you get an array of DB data whereas with WP_Query you get an array of WP_Post objects. Not a big difference, but overriding a WP_Query SQL is one way to hack existent code in a more compatible manner.

    Thread Starter islp

    (@islp)

    Hi @bcworkz, first of all thanks for your kind reply.

    I tried $wpdb->query($query) (and get_results too) today but it returned an empty array for that query (I tested the same query in PhpMyAdmin and it returned three rows).

    If nothing works, maybe I can try a PDO query, but I was looking for a WP solution before.

    Thread Starter islp

    (@islp)

    Ok, I didn’t know WPDB featured a special SQL syntax; so, this one finally worked for me:

    
$query = "SELECT * FROM $wpdb->posts INNER JOIN $wpdb->postmeta AS meta1 ON ($wpdb->posts.ID = meta1.post_id) INNER JOIN $wpdb->postmeta AS meta2 ON ($wpdb->posts.ID = meta2.post_id) INNER JOIN $wpdb->postmeta AS meta3 ON ($wpdb->posts.ID = meta3.post_id) WHERE post_type = 'event' AND post_status = 'publish' AND meta1.meta_key = 'core_event_month' AND meta1.meta_value IN ('November','December') AND meta2.meta_key = 'core_event_day' AND meta2.meta_value >= 4 AND meta3.meta_key = 'core_event_year' AND meta3.meta_value = 2019 GROUP BY ID ORDER BY STR_TO_DATE(CONCAT(meta1.meta_value,' ',meta2.meta_value,' ',meta3.meta_value), '%M %d %Y') DESC";

    I’ll find a way to integrate the result with the rest of the code.

    Thank you very much ??

    • This reply was modified 5 years, 4 months ago by islp.
    Moderator bcworkz

    (@bcworkz)

    You’re welcome. I’m glad you figured it out.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Using raw query instead of meta_query’ is closed to new replies.