Filter resources by template variable value with M

Question

I have a site that uses Wayfinder to display the latest 3 entries from an Articles blog. Now, I want to only consider those blog entries that are tagged Highlights.

My original Wayfinder call looks like this, nothing spectacular:

[[!Wayfinder? &startId=`296` &level=`1`
    &outerTpl=`emptyTpl`
    &innerTpl=``
    &rowTpl=`thumbnails_formatter`
    &ignoreHidden=`1`
    &sortBy=`menuindex`
    &sortOrder=`DESC`
    &limit=`3`
    &cacheResults=`0`
]]

as Articles tags are managed via the articlestags TV, I thought that a &where might do the trick, but with no luck yet:

&where=`[{"articlestags:LIKE":"%Highlights%"}]`

does not yield anything. As a sanity check, I tried [{"pagetitle:LIKE":"%something%"}], which worked. Obviously, the problem is that articlestags is not a column of modx_site_content, but I'm not sure about how to put the subquery.

SELECT contentid
FROM modx_site_tmplvar_contentvalues
WHERE tmplvarid=17
  AND value LIKE '%Highlights%'

Gave me the right IDs on the sql prompt, but adding it to the Wayfinder call like this gave an empty result again:

&where=`["id IN (SELECT contentid FROM modx_site_tmplvar_contentvalues WHERE tmplvarid=17 AND value LIKE '%Highlights%')"]`

Any ideas on how to achieve this? I'd like to stay with Wayfinder for consistency, but other solutions are welcome as well.

Answer 1

You can just use pdomenu (part of pdoTools) instead Wayfinder

[[!PdoMenu? 
    &startId=`296` 
    &level=`1`
    &outerTpl=`emptyTpl`
    &innerTpl=``
    &rowTpl=`thumbnails_formatter`
    &ignoreHidden=`1`
    &sortBy=`menuindex`
    &sortOrder=`DESC`
    &limit=`3`
    &cacheResults=`0`

    &includeTVs=`articlestags`
    &where=`[{"TVarticlestags.value:LIKE":"%filter%"}]`

]]

Answer 2

Take a peek at some of the config files [core/components/wayfinder/configs ] - I have not tried it, but it looks as if you can run your select query right in the config & pass the tmplvarid array to the $where variable.

Answer 3

A little playing around led me to a solution: I needed to include the class name (not table name) when referring to the ID:

&where=`["modResource.id IN (SELECT contentid FROM modx_site_tmplvar_contentvalues WHERE tmplvarid=17 AND value LIKE '%Highlights%')"]`

a small test showed that even a simple

&where=`["id = 123"]`

does not work without modResource..

A look at wayfinder.class.php shows the following line, which seems to be the "culprit":

$c->select($this->modx->getSelectColumns('modResource','modResource'));

This method aliases the selected columns - relevant code is in xpdoobject.class.php. The first parameter is the class name, the second a table alias. The effect is that the query selects id AS modResource.id, and so on.

---

EDIT: final version of my query:

&where=`["modResource.id IN (
    SELECT val.contentid
    FROM modx_site_tmplvars AS tv
    JOIN modx_site_tmplvar_contentvalues AS val
     ON tv.id = val.tmplvarid
    WHERE tv.name = 'articlestags' AND (
        val.value = 'Highlights'
     OR val.value LIKE 'Highlights,%'
     OR val.value LIKE '%,Highlights'
     OR val.value LIKE '%,Highlights,%'
    )
)"]`

I don't claim this query is particularly efficient (I seem to recall that OR conditions are bad). Also, MODx won't work with this one if the newlines aren't stripped out. Still, I prefer to publish the query in its well-formatted form.

Answer 4

I used snippet as a parameter for the includeDocs of wayfinder, In my case it was useful because I was need different resources in menu depend on user browser (mobile or desktop)

[[!Wayfinder? 
    &startId=`4`
    &level=`1`
    &includeDocs=`[[!menu_docs?&startId=`4`]]`
    &outerTpl=`home_menu_outer`
    &rowTpl=`menu_row`
]] 

and then menu_docs snippet

<?php
if (empty ($startId))
    return;

if (!isMobileDevice())
    return;


$query = $modx->newQuery('modResource');
$query->innerJoin('modTemplateVarResource','TemplateVarResources');
$query->where(array(
    'TemplateVarResources.tmplvarid' => 3,
    'TemplateVarResources.value:LIKE' => 'yes',
    'modResource.parent' => $startId,
    'modResource.deleted' => 0,
    'modResource.published' => 1,
    'modResource.hidemenu' => 0
));
$resources = $modx->getCollection('modResource', $query);

$ouput = array();
foreach ($resources as $resource)
    $output[] = $resource->get('id');

return implode (',', $output);