Easy way to write complex queries in wordpress

Question

I want to run one sql query like this in wordpress. Just wanted to know if there is an easy way to do this in wp. Thanks.

Is this query possible with the use of query_posts anyhow?

select p1.meta_value as avg,p2.meta_value as total,p1.meta_value*p2.meta_value as product from wp_postmeta as p1, wp_postmeta as p2 where p1.meta_key = 'crfp-average-rating' and p2.meta_key = 'crfp-total-ratings' and p1.post_id = p2.post_id;

Answer 1

I wouldn't say it's "easy", but any complex queries like the one you have that can't be handled via the API, WP_Query, or its filters should use the wpdb class.

global $wpdb;

$rows = $wpdb->get_results(
    "
    SELECT  p1.meta_value as avg,
            p2.meta_value as total,
            p1.meta_value * p2.meta_value as product
    FROM    $wpdb->postmeta as p1,
            $wpdb->postmeta as p2
    WHERE   p1.meta_key = 'crfp-average-rating'
            AND p2.meta_key = 'crfp-total-ratings'
            AND p1.post_id = p2.post_id
    "
);

foreach( $rows as $row ){
    print_r( $row );
}

Answer 2

Since I don't have enough points to comment, I'll just make this an answer.

What are you trying to use these queries for? It might be much more simpler to use the built in functions of wordpress. If you're adamant, I can't say much for making this much more easy in wordpress, but you could try using a PHP to MySQL framework, like medoo. That framework helped me cut down on a lot of php to MySQL development time, but it will require you to set up another file with access to your wordpress database! (You could use a combination of the two) Good luck!