73 posts StrictThemes
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $75,000+ on Envato Market and is now an Elite Author
  • Sells items exclusively on Envato Market
  • Has been part of the Envato Community for over 1 year
+3 more
StrictThemes says

Sometimes I have to make a custom query to database e.g. if I need to get a list of posts IDs by custom meta and order the results I use this way:

$posts = $wpdb->get_results(" 
    SELECT T1.post_id
        FROM $wpdb->postmeta T1 
        JOIN $wpdb->posts T2
            ON T1.post_id = T2.ID
            AND T2.post_type = 'post'
            AND T2.post_status = 'publish'
        WHERE T1.meta_key = 'post_views_count'
        ORDER BY CAST(T1.meta_value AS SIGNED) DESC
    ");

I’m not MySQL-guru, by the way :)

Maybe somebody can tell me how to store the results of query by the right way, instead of making a call to database each time.

Any suggestions will be appreciated.

1485 posts
  • Has referred 1+ members
  • Has sold $10,000+ on Envato Market
  • Has collected 10+ items on Envato Market
  • Made it to the Authors' Hall of Fame
+3 more
OriginalEXE says

Hi,

1.) Always use $wpdb->prepare on your custom queries, read: http://ottopress.com/2013/better-know-a-vulnerability-sql-injection/

2.) Why not use get_posts() instead of a custom query. You can use ‘fields’ parameter in arguments array to only load post id’s, read: http://codex.wordpress.org/Class_Reference/WP_Query#Return_Fields_Parameter

3.) Use WordPress transients API: http://codex.wordpress.org/Transients_API to cache your query result. Don’t forget to also empty it once new post of that post type is made.

73 posts StrictThemes
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $75,000+ on Envato Market and is now an Elite Author
  • Sells items exclusively on Envato Market
  • Has been part of the Envato Community for over 1 year
+3 more
StrictThemes says

Hi,

First of all, many thanks for a quick and so complete answer. I really appreciate it!

1) Yes. The $wpdb->prepare is must have.

2) Originally get_posts() is much better, however I didn’t get a success in this case, so I will try to use this way again.

3) That’s exactly what I’m looking for.

Thanks again! Cheers! :)

1485 posts
  • Has referred 1+ members
  • Has sold $10,000+ on Envato Market
  • Has collected 10+ items on Envato Market
  • Made it to the Authors' Hall of Fame
+3 more
OriginalEXE says

No problem.

Based on your SQL above, I believe this should do the same job:

$args = array(
    'orderby'  => 'meta_value_num',
    'meta_key' => 'post_views_count',
    'fields'   => 'ids',
)

$posts_array = get_posts( $args );
73 posts StrictThemes
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $75,000+ on Envato Market and is now an Elite Author
  • Sells items exclusively on Envato Market
  • Has been part of the Envato Community for over 1 year
+3 more
StrictThemes says

Thanks a lot! It works perfect. Here is the result.

$args = array (
    'post_type'             => 'post',
    'posts_per_page'        => '5',
    'ignore_sticky_posts'   => 1,
    'orderby'               => 'meta_value_num',
    'meta_key'              => 'post_views_count',
);

$query = get_transient( 'query_name' );

if ( $query == false ) {
    $query = new WP_Query( $args );
    set_transient( 'query_name', $query, 60*60 );
}

Thanks again! Appreciated! :)

1485 posts
  • Has referred 1+ members
  • Has sold $10,000+ on Envato Market
  • Has collected 10+ items on Envato Market
  • Made it to the Authors' Hall of Fame
+3 more
OriginalEXE says

:)

Just a note, instead of using 60*60, you can just use HOUR_IN_SECONDS (it’s a constant defined by WP).

It’s a lot more readable that way, at least in my opinion.

More about that: http://codex.wordpress.org/Transients_API#Using_Time_Constants

by
by
by
by
by
by