Support » Developing with WordPress » Intranet breaking with Custom SQL code

  • Resolved aralmac123

    (@aralmac123)


    I have written a custom sql statement that will act as the filter for my posts, pages and documents. Unfortunately when i am trying to transform my sqlquery to work in WordPress it is breaking our site and there is no way for me to debug it.

    Could anyone suggest the best way to imbed this SQL statement into my functions.php page in order for it to work? Thanks!

    function wpb_search_filter($query) {
    	global $current_user;
    	global $current_user_Location;
    	
    	$current_user = wp_get_current_user();
    	$current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
    	
    	global $wdpb;
    	global $sqlquery;
    	global $strSite1;
    	global $strSite2;
    	global $strSite3;
    	
    	$sqlquery = "SELECT DISTINCT wp_posts.id
    	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    	WHERE wp_posts.id = wp_term_relationships.object_id
    	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    	AND wp_term_taxonomy.term_id = wp_terms.term_id
    	AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    	
    	UNION ALL
    
    	SELECT DISTINCT posts.id
    	FROM wp_posts posts
    	WHERE NOT EXISTS(
    	SELECT *
    	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    	WHERE posts.id =  wp_posts.id
    	AND wp_posts.id = wp_term_relationships.object_id
    	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    	AND wp_term_taxonomy.term_id = wp_terms.term_id)
    	AND posts.post_type IN ('wpfd_file', 'page', 'post')
    	AND posts.post_status NOT IN ('auto-draft', 'trash')";
    	
    	if ($query->is_search) {
            if ($current_user_Location == 'CR'){
    		$strSite1 = "'Craigavon'";
    		$strSite2 = "'All'";
    		$strSite3 = "'UK'";	
        	}
    
    	$query = $wpdb->get_results($sqlquery);
    	return $query;
    }
    add_filter('pre_get_posts','wpb_search_filter');

    Or can anyone see where I am going wrong?

    • This topic was modified 1 week, 3 days ago by aralmac123.
    • This topic was modified 1 week, 3 days ago by aralmac123.
Viewing 11 replies - 1 through 11 (of 11 total)
  • I guess the issue is with the following line in case of string value:

    
    AND wp_terms.name IN ($strSite1, $strSite2, $strSite3)
    

    That should be

    
    AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    

    Alternative option is to print the query and check to run it on phpmyadmin;

    
    $query = "SELECT DISTINCT wp_posts.id .......";
    
    // Perform print in case of Error
    // print( $query );
    
    $sqlquery = $wpdb->get_results($query);
    

    @jogesh_pi thanks for the reply! see in terms of my return statement at the end – what is the correct way for me to set the query returned equal to my sql statement?

    Is it $query = $wpdb->get_results($sqlquery)?

    Thanks for the tip about stating the variables also!

    You could change the variable accordingly.

    
    $raw_query = "SELECT DISTINCT wp_posts.id
    	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    	WHERE wp_posts.id = wp_term_relationships.object_id
    	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    	AND wp_term_taxonomy.term_id = wp_terms.term_id
    	AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    	
    	UNION ALL
    
    	SELECT DISTINCT posts.id
    	FROM wp_posts posts
    	WHERE NOT EXISTS(
    	SELECT *
    	FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    	WHERE posts.id =  wp_posts.id
    	AND wp_posts.id = wp_term_relationships.object_id
    	AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    	AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    	AND wp_term_taxonomy.term_id = wp_terms.term_id)
    	AND posts.post_type IN ('wpfd_file', 'page', 'post')
    	AND posts.post_status NOT IN ('auto-draft', 'trash')";
    
    // Perform print in case of Error
    // print( $raw_query );
    
    $sqlquery = $wpdb->get_results($raw_query);
    

    @jogesh_pi ive updated my code in the post to match what you have shown me, I also ran the SQL statements into PhpMyAdmin and they are definitely correct and working.

    Site is still breaking 🙁

    Hi @jogesh_pi !
    So ive made a lot of progress with the fact that it now works and my sql is returning the post ids of the posts/pages i want to display.

    The problem I am having now is that although I have an array with these IDs in it – it does not seem to be applying it to the filter when the user searches?

    I have added my updated code for below. Is my code around the “return $query” correct? or how do I actually apply the results from my sql to the filtering process.

    Id really appreciate the help 🙂

    function wpb_search_filter($query) {
    global $current_user;
    global $current_user_Location;
    
    $current_user = wp_get_current_user();
    $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
    
    global $wpdb;
    global $sqlquery;
    global $strSite1;
    global $strSite2;
    global $strSite3;
    
    if ($query->is_search) {
        if ($current_user_Location == 'CR'){
        $strSite1 = 'Craigavon';
        $strSite2 = 'All';
        $strSite3 = 'UK';
    }
    
    $sqlquery = "SELECT DISTINCT wp_posts.id
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id
    AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    
    UNION ALL
    
    SELECT DISTINCT posts.id
    FROM wp_posts posts
    WHERE NOT EXISTS(
    SELECT *
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE posts.id =  wp_posts.id
    AND wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id)
    AND posts.post_type IN ('wpfd_file', 'page', 'post')
    AND posts.post_status NOT IN ('auto-draft', 'trash')";
    
    echo $strSite1;
    echo "<br>";
    echo $strSite2;
    echo "<br>"; 
    echo $strSite3;
    echo "<br>";
    echo $sqlquery;
    echo "<br>";
    
    $query = array();
    $query = $wpdb->get_results($sqlquery);
        foreach ($query as $details) {
          echo $details->id . ",";
          } 
    }
    return $query;
    }
    add_filter('pre_get_posts','wpb_search_filter');
    • This reply was modified 1 week, 3 days ago by aralmac123.
    • This reply was modified 1 week, 3 days ago by aralmac123.
    Moderator bcworkz

    (@bcworkz)

    You’re having trouble despite a working query because you are not using “pre_get_posts” correctly. To start with, it’s an action, not a filter, so there is little point in returning anything. Your callback is passed the WP_Query object by reference, so any changes you want done with the query needs to be done through object methods, usually $query->set(), which sets values for specified query vars.

    If you want the overall query results to be the results of your custom query, you can set the 'posts__in' query var to an array of post IDs.

    Running a separate query in order to run a query is not optimal. If at all possible you should set the appropriate query vars to achieve your goals so that it’s all taken care of in one query. If what you desire is not possible by setting query vars, there are other filters (not actions 😉 ) you can use to modify the actual SQL that WP_Query will run. There are filters for each individual query clause (where, join, order by, etc.) as well as “posts_request” which filters the entire SQL query to be run.

    If none of those would work for you, you should consider if WP_Query is really the best route. Perhaps a completely custom query would make more sense.

    Hi @bcworkz @jogesh_pi ,
    Taking your advice I’ve updated my code to this:

    add_action('pre_get_posts','wpb_search_filter');
    
    function wpb_search_filter($query) {
    global $current_user;
    global $current_user_Location;
    
    $current_user = wp_get_current_user();
    $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
    
    global $wpdb;
    global $sqlquery;
    global $strSite1;
    global $strSite2;
    global $strSite3;
    
    if ($query->is_search) {
     if ($current_user_Location == 'CR'){
        $strSite1 = 'Craigavon';
        $strSite2 = 'All';
        $strSite3 = 'UK';
    }
    
    $sqlquery = "SELECT DISTINCT wp_posts.id
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id
    AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    
    UNION ALL
    
    SELECT DISTINCT posts.id
    FROM wp_posts posts
    WHERE NOT EXISTS(
    SELECT *
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE posts.id =  wp_posts.id
    AND wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id)
    AND posts.post_type IN ('wpfd_file', 'page', 'post')
    AND posts.post_status NOT IN ('auto-draft', 'trash')";
    
    $postIds = array();
    $postIds = $wpdb->get_results($sqlquery);
        foreach ($postIds as $details) {
          echo $details->id . ",";
          }
    
        if ($current_user_Location == 'CR'){
        echo "<Br>";
        echo "you have reached this point and it is a search!";
    
        $query->set('posts__in', $query);
    
    }
    }
    return $query;
    }

    I’ve also tried it this way:

    if ($current_user_Location == 'CR'){
        echo "<Br>";
        echo "you have reached this point and it is a search!";
    
        echo "<Br>";
        echo "Query has been set!";
    
        $query = new WP_Query(array(
        'post__in' => $postIds
    ));

    (I am displaying all of my echos, which are displaying a list of comma separated Ids so I am getting to the point of setting the queries, so I only want to display posts/pages that IDs exist in that array, if doesn’t exist – don’t show)
    Both having no luck as it seems just to be displaying all posts and pages and still not applying the filter. Am I still making errors?

    Really appreciate this help by the way, would be lost without it

    • This reply was modified 1 week, 2 days ago by aralmac123.

    @bcworkz , my bad its supposed to be "Post__in",you see when we do the get_resultsfrom the query it wasn’t comma separated and that’s why we had to add that into the echo, do you think our $postIdsarray will consist of them all as one long string e.g. array('983574845475845863296373834') or will it be smart enough to comma separate these based on the Ids?

    Moderator bcworkz

    (@bcworkz)

    Yeah, you cannot insert a PHP array into a SQL string. Two incompatible data types. Concatenating array elements into a string is OK, but when doing so in a loop you end up with a trailing comma which is frequently a syntax error in some languages. SQL is probably one of them. Better is to use PHP’s implode() to “stringify” an array. It avoids the trailing comma problem.

    Don’t create a new WP_Query instance within a callback for “pre_get_posts”. It causes an infinite loop. While there are ways to prevent the looping, a new instance is unnecessary. You’re still not using “pre_get_posts” correctly. I know doing it right is a tricky concept to grasp. Once you really understand it seems simple, but getting there can be difficult.

    Don’t bother returning any value. The function calling your callback ignores it. It does no harm, but it demonstrates you’re still unclear on the concept 🙂 Instead, you need to make changes to the passed object. Because it’s passed by reference, changing the object affects the query being run. This is a bit unusual. Most action callback arguments are passed by value and altering them has no effect.

    Your $query->set('posts__in', $query); is the right idea, but $query is the wrong value to pass. The value to pass is an array of ID values. Passing $postIds is closer to the right value, but still not right because it is an array of post data arrays, not IDs. If you change your SELECT clause to only collect the ID field instead of * you can use $postIds.

    I saw your response in your other related topic. To allow future readers landing from search results the ability to follow along, I’m directing them to this topic and closing that other topic so all relevant discussion remains here.

    @bcworkz – I’ve finally cracked it! Here is my code for anyone else struggling with this:

    function wpb_search_filter($query) {
    global $current_user;
    global $current_user_Location;
    
    $current_user = wp_get_current_user();
    $current_user_Location=get_metadata( 'user', $current_user->ID, 'mo_ldap_local_custom_attribute_l', true );
    
    global $wpdb;
    global $sqlquery;
    global $strSite1;
    global $strSite2;
    global $strSite3;
    
    if ($query->is_search) {
    if ($current_user_Location == 'CR'){
        $strSite1 = 'Craigavon';
        $strSite2 = 'All';
        $strSite3 = 'UK';
    }
    $sqlquery = "SELECT DISTINCT wp_posts.id
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id
    AND wp_terms.name IN ('{$strSite1}', '{$strSite2}', '{$strSite3}')
    
    UNION ALL
    
    SELECT DISTINCT posts.id
    FROM wp_posts posts
    WHERE NOT EXISTS(
    SELECT *
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms
    WHERE posts.id =  wp_posts.id
    AND wp_posts.id = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy IN ('post_tag','wpfd-tag')
    AND wp_term_taxonomy.term_id = wp_terms.term_id)
    AND posts.post_type IN ('wpfd_file', 'page', 'post')
    AND posts.post_status NOT IN ('auto-draft', 'trash')";
    
    $postIds = array();
    $idArray = array();
    $postIds = $wpdb->get_results($sqlquery);
        foreach ($postIds as $details) {
          array_push($idArray, $details->id . ",");
    }
        if ($current_user_Location == 'CR'){
    
        $query->set('post__in', $idArray);
    }
    }
    
    return $query;
    
    }
    add_filter('pre_get_posts','wpb_search_filter');

    @bcworkz – Thank you for all your guidance, been excellent help!! 🙂
    and i’m sure you will come across some of my questions in the future lol

    • This reply was modified 1 week, 2 days ago by aralmac123.
    Moderator bcworkz

    (@bcworkz)

    You’re welcome! Always happy to help where I can.

Viewing 11 replies - 1 through 11 (of 11 total)
  • You must be logged in to reply to this topic.