SimpleSearch: Filter results by Template/TV?

Hello,
I am using the SimpleSearch Plugin for a search function which lets users search for terms across the entire website.
However, I need to exclude resources that use certain templates (and in some cases resources with a specific template variable set to “true”) from the search results. This is because those resources only exist to be displayed on certain overview pages, but do not actually have their own page (for example, I have a template for team members, each team member is displayed on the “team” page, but does not have an individual page - so if a user searches for the name of a team member and clicks on the result, they end up on an empty page).

The obvious solution would be to simply disable the “searchable” setting on each resource - this does work, but it relies on the editors remembering to disable this each time they create a new resource, so I’d like to find a more elegant solution.

I have found an old forum post which seems to offer a solution: How to limit search results to a particular Template with SimpleSearch? | MODX Community Forums
However, I’m getting a 500 error using that snippet.

<?php
 
$template_ids = explode(',', $modx->getOption('template_ids', $scriptProperties, 0));
$mode = $modx->getOption('mode', $scriptProperties, 'show'); //show or hide resources with given templates
 
$c = $modx->newQuery('modResource');
$c->where(array('template:IN' => $template_ids));
if ($collection = $modx->getCollection('modResource')) {
    foreach ($collection as $object) {
        $ids[] = $object->get('id');
    }
}
 
if ($mode == 'hide') {
    $c = $modx->newQuery('modResource');
    $c->select($modx->getSelectColumns('modResource', 'modResource', array('id')));
    $rows = array();
    $resources = array();
    if ($c->stmt->execute()) {
        if ($results = $c->stmt->fetchAll(PDO::FETCH_ASSOC)) {
            $rows = $results;
        }
    }
    foreach ($rows as $row){
        if (isset($row['id'])){
            $resources[] =  $row['id'];
        }
    }
    $scriptProperties['ids'] = implode(',', $resources);
    $scriptProperties['exclude'] = implode(',', $ids);
 
} else {
    $scriptProperties['ids'] = implode(',', $ids);
}
 
return $modx->runSnippet('SimpleSearch', $scriptProperties);
[[!filterSearchResults? 
    &template_ids=`7`
    &idType=`documents`
    &mode=`hide`
]]

This would also only solve half of my problem, as I’d still need a way to filter by template variable…

I also tried to use GetResources, like this:

[[!getResources?
    &parents=`10,127`
    &toPlaceholder=`excludedIdsTeam`
]]

[[!SimpleSearch? &exclude=`[[+excludedIdsTeam]]`]]

This did not work, presumably because I need to get the resources’ IDs instead, but I have not yet figured out how I can do that.

Any advice on how I could get this to work?

EDIT: I’m using MODX 3.0.4-pl and SimpleSearch 3.0.0-pl.

Do you use MODX 3 or MODX 2.x?

I’m using MODX 3.0.4-pl and SimpleSearch 3.0.0-pl.

With SimpleSearch you could use a custom driver.
This would be faster than running one query to get the IDs and then a second one to do the actual search.

I created this Github repository some while ago as an example:

Basically what you have to do is the following:

  • Create a custom folder in core/components with a “bootstrap.php” to load your custom class.
  • Add a namespace for this custom folder in the MODX manager (:gear: → “Namespaces”)
  • Use either the default driver class from SimpleSearch (core/components/simplesearch/src/Driver//SimpleSearchDriverBasic.php) or src/Driver/Custom.php from my Github repo as the starting point and hard-code the specific changes you need to filter the query.
  • Change the system setting simplesearch.driver_class to your custom driver class.

I’m going to give it a try, thank you!

I’ve made some progress using your custom driver:

[[!SimpleSearch?
    &includeTVList=`reference_technology,technology_has_page`
    &where=`{"template:NOT IN":[7]}`
]]

Using this, I have been able to filter out all resources with the team member template from the results.

What I haven’t managed to solve yet is how I can also filter out resources based on a certain TV value, I’d need to be able to do something like this:

[[!SimpleSearch?
    &includeTVList=`reference_technology,technology_has_page`
    &where=`{"template:NOT IN":[7],"AND:technology_has_page:!=":[1]}`
]]

The easiest solution for this is probably to add a subquery to the &where property. Like this:

[[!SimpleSearch?
    &includeTVList=`reference_technology`
    &where=`[{"template:NOT IN":[7]},"NOT EXISTS (SELECT id FROM modx_site_tmplvar_contentvalues WHERE tmplvarid = 21 AND contentid = modResource.id AND value = '1')"]`
]]
  • Change the value 21 in the subquery to the actual ID of the TV “technology_has_page” on your system.
  • It’s not necessary to include the TV “technology_has_page” in the property &includeTVList for this to work.
  • This won’t work if “1” is the default value of the TV “technology_has_page”.

Hmm, something is still not quite right - I’m not getting any search results with this query. I also made a mistake in my code above, I’m trying to get those resources where technology_has_page = 1 OR where technology_has_page doesn’t exist (not all templates use that TV).
Based on this, I figured something like the following should work:

[[!SimpleSearch?
    &includeTVList=`reference_technology`
    &where=`[{"template:NOT IN":[7]},"EXISTS (SELECT id FROM modx_site_tmplvar_contentvalues WHERE tmplvarid = 171 AND contentid = modResource.id AND value = '1')"]`
]]

This should at least get those resources with technology_has_page=1, but when I try it, I’m still not getting anything. :thinking:

When you use the &debug property, the placeholder [[!+SimpleSearchCustomDriver.SQL]] outputs the used SQL query.

<pre>[[!+SimpleSearchCustomDriver.SQL]]</pre>

[[!SimpleSearchForm]]
<h2>Results</h2>
[[!SimpleSearch?
    ...
    &debug=`1`
]]

Analyze the query to see what’s wrong. Maybe use phpMyAdmin to run the query. Check the database table modx_site_tmplvar_contentvalues. Is the value really saved as 1?

Alright, I think I got it working! :slight_smile:
First thing I noticed when checking the Database with phpMyAdmin is that the db prefix is actually set to bsf_ instead of the default modx_. I was not aware of that, this is an older modx site that I am working on updating and redesigning. So I changed the query accordingly:

[[!SimpleSearch?
    &where=`[{"template:NOT IN":[7]},"EXISTS (SELECT id FROM bsf_site_tmplvar_contentvalues WHERE tmplvarid = 171 AND contentid = modResource.id AND value = '1')"]`
    &debug=`1`
]]

And it works! The only thing left now was to figure out how I can include resources in the search results that don’t use the technology_has_page TV. In the end, the query should do the following:

Get results where the template is not 7
AND (TV 171 is “1” OR TV 171 does not exist)

After a lot of trial and error (I’m not very familiar with SQL), I ended up using the following query:

[[!SimpleSearch?
    &includeTVList=`reference_technology`
    &processTVs=`1`
    &where=`[{"template:NOT IN":[7]},"EXISTS (SELECT id FROM bsf_site_tmplvar_contentvalues WHERE tmplvarid = 171 AND contentid = modResource.id AND value = '1') OR ( modResource.template NOT IN (32,7) )"]`
]]

This seems to give me the desired results.
Thanks a lot for your help and advice @halftrainedharry !

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.