Answered getResources TV-filtering (tvFilters) doesn't work as expected

Hello everyone.
There's obviously simple task – resource output via getResources with TV-filtering using &tvFilters parameter.
But something went wrong this time.
Dear MODX'ers, please, help in solving this issue.

Snippet call:
[[!getResources?
&parents=`3`
&resources=`-34,-35,-36,-37,-38,-40`
&tpl=`attorneyOfficeRowTpl`
&limit=`0`
&includeTVs=`1`
&processTVs=`1`
&tvFilters=`attorney_office==[[*office_office]]`
&debug=`1`
]]
As you can see, it uses resource with id=3 as an input source.
There was created two TVs with identical content and input type – Listbox (Single-Select).
Both have Text as an Output type (I've chosen different output types, with no luck).
Both have Input Option Values in the following format: Thousand Oaks CA||Westlake Village CA||San Francisco CA
attorney_office is used by children of the resource with id=3, office_office – by the current resource in which getResources is being called.
tvFilters doesn't filter anything and shows all (bad syntax), or shows nothing (bad syntax again, I suppose).

Error Log says:
[2015-09-29 22:14:07] (ERROR @ /index.php) context for 3 is web
[2015-09-29 22:14:07] (ERROR @ /index.php) SELECT `modResource`.`id`, `modResource`.`type`, `modResource`.`contentType`, `modResource`.`pagetitle`, `modResource`.`longtitle`, `modResource`.`description`, `modResource`.`alias`, `modResource`.`link_attributes`, `modResource`.`published`, `modResource`.`pub_date`, `modResource`.`unpub_date`, `modResource`.`parent`, `modResource`.`isfolder`, `modResource`.`introtext`, `modResource`.`richtext`, `modResource`.`template`, `modResource`.`menuindex`, `modResource`.`searchable`, `modResource`.`cacheable`, `modResource`.`createdby`, `modResource`.`createdon`, `modResource`.`editedby`, `modResource`.`editedon`, `modResource`.`deleted`, `modResource`.`deletedon`, `modResource`.`deletedby`, `modResource`.`publishedon`, `modResource`.`publishedby`, `modResource`.`menutitle`, `modResource`.`donthit`, `modResource`.`privateweb`, `modResource`.`privatemgr`, `modResource`.`content_dispo`, `modResource`.`hidemenu`, `modResource`.`class_key`, `modResource`.`context_key`, `modResource`.`content_type`, `modResource`.`uri`, `modResource`.`uri_override`, `modResource`.`hide_children_in_tree`, `modResource`.`show_in_tree`, `modResource`.`properties` FROM `modx_site_content` AS `modResource` WHERE ( ( ( modResource.parent IN (3,34,35,36,37,38,40,4,41,60,62,63,61,64,65,66,67,69,70,68) AND `modResource`.`deleted` = 0 AND `modResource`.`published` = 1 AND `modResource`.`hidemenu` = 0 ) AND (EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE 'Westlake Village CA' AND tv.name = 'attorney_office' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modx_site_tmplvars` tv WHERE tv.name = 'attorney_office' AND tv.default_text LIKE 'Westlake Village CA' AND tv.id NOT IN (SELECT tmplvarid FROM `modx_site_tmplvar_contentvalues` WHERE contentid = modResource.id)) ) ) AND `modResource`.`id` NOT IN (34,35,36,37,38,40) ) ORDER BY publishedon DESC

It means nothing fir me. I don't understand a thing.
I think that the reason is bad syntax in &tvFilters=`` parameter.
What I tried to do:
&tvFilters=`attorney_office==office_office`
&tvFilters=`tv.attorney_office==tv.office_office`
&tvFilters=`[[tv.attorney_office]]==[[tv.office_office]]`
&tvFilters=`[[+tv.attorney_office]]==[[+tv.office_office]]`
and so on...
I've read that it can be done using &where parameter, but I'm not aware of how JSON works.

Anyway, I would be much appreciate if anyone can help with this (tired to wake up face on keyboard;).
Thanks in advance.

You first need to remove the &debug=`1` property. This is what's filling up your log file with sql queries. Then try this:



BTW, there's no need to call this uncahed. Remove the ! from the call as I need. Your page load times will improve.

Unfortunately, treigh, it didn't work.
I now suspect these two TV more than ever.
I tried to output them using [[Wayfinder? &startId=`3` &rowTpl=`<li>[[attorney_office]]</li>`]]
But it just outputs the structure of the source with id=3.
Here is the screen shot of attorney_office TV https://www.dropbox.com/s/zfteqj25qgca2v5/Screenshot%202015-10-01%2002.32.42.png?dl=0
And output options: https://www.dropbox.com/s/61n8qu80at81ktz/Screenshot%202015-10-01%2002.33.41.png?dl=0
I think &tvFilters just don't know what to compare, or it compares nothing.
How can I be sure, how to test it?

A few more things:

Try this:



I basically changed [[*office_office]] to office_office in the &tvFilters property. I missed this when I copied your code.

Also, If your resource id 3 is a parent with hidden children such a Collections or Articles container, add &showHidden=`1` to your call. This will ensure that hidden children are shown in your results.

Let me know.

EDIT: I changed &tvFilters=`attorney_office==%office_office%` back to &tvFilters=`attorney_office==%[[*office_office]]%`. I didn't realize this was a TV value. [ed. note: treigh last edited this post 11 years ago.]

Thanks for your help, treigh.
But again, didn't help.
I don't understand.
attorney_office TV is appears to be OK – it successfully outputs on Attorneys page.
Same for office_office TV – it outputs with no doubt on Offices page.
But when trying to compare them – something's going wrong.
Whether &tvFilters's algorithm is broken (which I don't think is true), or it can't compare strings due to wrong syntax.
What information can I provide you for further investigation?

This sounds easy to fix, but I'm not sure what's wrong. Can you explain what exactly you're trying to achieve with getResources? Maybe this is what's missing.

Just so we're clear on your result expectations -- &tvFilters=`attorney_office==%office_office%` is instructing getResources to only return resources whose attorney_office TV value matches the value of the [[*office_office]], which I suspect is attached to resource containing the getResource call. If this assumption is correct, the call above should work.

I wouldn't mind having a hands-on look if you want. Just PM me a temporary acces for testing.

Quote from: treigh at Oct 01, 2015, 04:29 PM

Just so we're clear on your result expectations -- &tvFilters=`attorney_office==%office_office%` is instructing getResources to only return resources whose attorney_office TV value matches the value of the [[*office_office]], which I suspect is attached to resource containing the getResource call. If this assumption is correct, the call above should work.

Yes, exactly what I expect it to do.
I'll try to give you access ASAP.
Meanwhile, don't you think that THE value of a TV could result in an unpredictable behavior.
I mean, all TV values have content in a format: Thousand Oaks CA||Westlake Village CA||San Francisco CA
Maybe those spaces between words should be somehow taken into account?

The spaces shouldn't be an issue as long as your values are separated by double-pipes.