Intranet breaking with Custom SQL code

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 5 years, 2 months ago by aralmac123.
• This reply was modified 5 years, 2 months ago by aralmac123.

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 5 years, 2 months 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?

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 5 years, 2 months ago by aralmac123.

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