Using custom SQL with WP_Query class

  • Hi,

    So I’m trying to understand how the WP_Query class works exactly. I’m using a theme that already uses it to display elements on a page. Although, I need to add some conditions to it and I don’t know how to do it. I need to add left joins on the users and usermeta tables.

    I was able to get the raw query that WP_Query generates, which is:

    SELECT SQL_CALC_FOUND_ROWS dv_posts.ID FROM dv_posts WHERE 1=1 AND dv_posts.post_type = ‘estate_agent’ AND ((dv_posts.post_status = ‘publish’)) ORDER BY RAND() LIMIT 0, 200

    Although, I tried throwing the above SQL query directly as the wp_query class parameter and it didn’t work. My end query needs to look like this:

    SELECT *
    FROM dv_posts
    LEFT JOIN dv_postmeta ON dv_postmeta.post_id = dv_posts.ID AND dv_postmeta.meta_key LIKE ‘user_meda_id’
    LEFT JOIN dv_users ON dv_users.ID = dv_postmeta.meta_value
    LEFT JOIN dv_usermeta um1 ON um1.user_id = dv_users.ID AND um1.meta_key LIKE ‘package_activation’
    LEFT JOIN dv_usermeta um2 ON um2.user_id = dv_users.ID AND um2.meta_key LIKE ‘package_id’
    WHERE 1=1
    AND dv_posts.post_type = ‘estate_agent’
    AND ((dv_posts.post_status = ‘publish’))
    AND (dv_users.ID IS NULL OR (um2.meta_value IS NOT NULL AND CURDATE() <= DATE_ADD(um1.meta_value, INTERVAL 1 MONTH)))
    ORDER BY RAND()
    LIMIT 0, 200

    The reason why I need to keep using wp_query is because the template is using the global variables generated by wp_query and I think it would be complicated to change that.

    Thanks in advance

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

    (@bcworkz)

    With WP_Query class, you can only instantiate an object using the specific parameters described on the doc page. It’s not tolerant of wishful parameters that are not supported :). In no parameter is SQL understood. However, it is possible to insert SQL or alter the resulting SQL through various filters. There are quite a few available, look through WP_Query::get_posts() source code to see what’s available.

    Perhaps the most flexible and useful filter is “posts_request”, where your callback is passed the complete, final SQL that WP_Query wants to use. You can alter it in any way you like, or replace it entirely. Just realize that what you do affects ALL post queries in WP, so some sort of conditional logic and/or selectively adding filters is necessary.

    That said, I still think you are better off using the global $wpdb object to directly make your query. You of course would need to reconstruct the template that uses WP_Query to use $wpdb instead. This may sound ominous, but it does not need to be. Only certain parts need to be changed, a lot of the template code can remain the same. Yes, the globals that are setup in WP_Query are useful, but the ones you would need to use to run The Loop can easily be re-created with setup_postdata().

    If nothing else, being able to switch from WP_Query to $wpdb is a useful WP coding skill to develop. If you do much of this, you’ll encounter it again. It’s nice to have the confidence that you can do it without a lot of heartache ??

    Thread Starter fchevalier

    (@fchevalier)

    Thanks alot for your answer, very clear and helpful! Thank you

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Using custom SQL with WP_Query class’ is closed to new replies.