Hi - looking at the old forum, I’ve found comments where folk have had issues trying to use getResources with &tvFilters on the TV ‘articlestags’ where resources have more than 1 tag applied to them… has anyone ever resolved this so there is a fix…?
I want to list the resources that match a given ‘articlestags’ TV - this works fine for any resources with just one ‘articlestags’ TV value, but those that have more than one are not included in the getResources list…
Any ideas on a fix?
I believe these tags are saved as a comma-separated list.
The best way to query that is probably to write a custom subquery with FIND_IN_SET or REGEXP.
//replace 'mytag' with the actual filter tag
[[getResources?
...
&where=`["EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'articlestags' AND tc.contentid = `modResource`.id AND FIND_IN_SET('mytag',tc.value) > 0)"]`
]]
//replace mytag in the regular expression with the actual filter tag
[[!getResources?
...
&where=`["EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'articlestags' AND tc.contentid = `modResource`.id AND tc.value REGEXP '(^|,)+mytag(,|$)+')"]`
]]
Thanks - would that approach work for tags with any values or would I need this line for each tag that is in use? I note at the end the tag name is specified as ‘mytag’ ? Likely I could just no understand the syntax and this is a dumb question!
You should be able to replace ‘mytag’ with a placeholder or snippet call.
... AND FIND_IN_SET('[[!+myplaceholder]]',tc.value) > 0)
In the version with REGEXP you probably need to be more careful, because some characters have a special meaning in regular expressions.
OK thanks - You’ve lost me though… sorry! I am not a PHP person really. So, I’m just using a TV called ‘articlestags’ at the moment - would i replace ‘mytag’ with ‘articlestags’ then?
This is all SQL not PHP.
No, you would replace ‘mytag’ with the value of the tag you want your resources to be filtered by.
Ahh ok - I will give your code a try and see what happens - thanks
Have tried adding this but I get no results dispalyed in the getResources call…
If you add &debug=`1` to your call to getResources, it should output the SQL query in the MODX error log. What is the value of this SQL query?
[[!getResources?
...
&debug=`1`
]]
Only error logged is “core/cache/includes/elements/modsnippet/1.include.cache.php : 142) PHP warning: count(): Parameter must be an array or an object that implements Countable”
not sure this is relevant to this code? Actually - think it is as just hit the page a number more times and same error repeated in the logs
Full code call is:
[[!pdoPage?
&elementClass=`modSnippet`
&element=`pdoResources`
&parents=`20,32,305`
&limit=`12`
&pageVarKey=`both_page`
&includeTVs=`1`
&processTVs=`1`
&includeContent=`1`
&tpl=`BlogAndNewsArticleTpl`
&tvPrefix=``
&sortby=`{"publishedon":"DESC"}`
&showHidden=`1`
&pageNavVar=`Both.nav`
&pageLimit=`1000`
&maxLimit=`1000`
&tvFilters=`articlestags==Tag1||articlestags==Tag2`
&where=`["EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'articlestags' AND tc.contentid = `modResource`.id AND FIND_IN_SET('mytag',tc.value) > 0)"]`
&debug=`1`
&includeTVList=`NewsAndBlogArticle_isFeatured,BlogAndNewsArticleSource,BlogAndNewsSummaryImageClass,BlogAndNewsSummaryImage,BlogAuthorName,EVENTINFO_City,EVENTINFO_Country,EVENTINFO_StartDate,EVENTINFO_EndDate,EVENTINFO_ShortDescription,EVENTINFO_BoothNumber`
]]
The title of this thread and your original question implies that you are using getResources.
But apparently you are using pdoResources.
There are differences between these snippets. &debug=`1` being one of them. You can debug pdoResources with &showLog=`1`. (That should output the SQL query on the frontend.)
&tvFilters?Sorry - I thought using pdo worked in the same format as getResources.
I didnt realise your code was to replace the &tvFilters. I will give that a try.
Error logging now working - content is as below:
0.0000880: pdoTools loaded
0.0000169: xPDO query object created
0.0004849: Included list of tvs: BlogAndNewsArticleSource, BlogAndNewsSummaryImage, BlogAndNewsSummaryImageClass, BlogAuthorName, EVENTINFO_BoothNumber, EVENTINFO_City, EVENTINFO_Country, EVENTINFO_EndDate, EVENTINFO_ShortDescription, EVENTINFO_StartDate, NewsAndBlogArticle_isFeatured
0.0001860: leftJoined modTemplateVarResource as TVblogandnewsarticlesource
0.0000911: leftJoined modTemplateVarResource as TVblogandnewssummaryimage
0.0000870: leftJoined modTemplateVarResource as TVblogandnewssummaryimageclass
0.0000851: leftJoined modTemplateVarResource as TVblogauthorname
0.0000851: leftJoined modTemplateVarResource as TVeventinfo_boothnumber
0.0000849: leftJoined modTemplateVarResource as TVeventinfo_city
0.0000849: leftJoined modTemplateVarResource as TVeventinfo_country
0.0000851: leftJoined modTemplateVarResource as TVeventinfo_enddate
0.0000839: leftJoined modTemplateVarResource as TVeventinfo_shortdescription
0.0001259: leftJoined modTemplateVarResource as TVeventinfo_startdate
0.0000942: leftJoined modTemplateVarResource as TVnewsandblogarticle_isfeatured
0.0002270: Added selection of modResource: SQL_CALC_FOUND_ROWS `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `content`, `richtext`, `template`, `menuindex`, `searchable`, `cacheable`, `createdby`, `createdon`, `editedby`, `editedon`, `deleted`, `deletedon`, `deletedby`, `publishedon`, `publishedby`, `menutitle`, `donthit`, `privateweb`, `privatemgr`, `content_dispo`, `hidemenu`, `class_key`, `context_key`, `content_type`, `uri`, `uri_override`, `hide_children_in_tree`, `show_in_tree`, `properties`
0.0000079: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `BlogAndNewsArticleSource`
0.0000038: Added selection of modTemplateVarResource: IFNULL(`value`, ' ') AS `BlogAndNewsSummaryImage`
0.0000038: Added selection of modTemplateVarResource: IFNULL(`value`, 'DontShowMe') AS `BlogAndNewsSummaryImageClass`
0.0000029: Added selection of modTemplateVarResource: IFNULL(`value`, 'Jon Wallace') AS `BlogAuthorName`
0.0000029: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_BoothNumber`
0.0000021: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_City`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_Country`
0.0000019: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_EndDate`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_ShortDescription`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `EVENTINFO_StartDate`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, 'No') AS `NewsAndBlogArticle_isFeatured`
0.0001009: Replaced TV conditions
0.0006571: Processed additional conditions
0.0009410: Added where condition: 0=EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'articlestags' AND tc.contentid = `modResource`.id AND FIND_IN_SET('mytag',tc.value) > 0), modResource.parent:IN(20,32,305,27,127,131,134,136,145,151,126,155,157,158,159,166,167,180,186,189,190,191,308,195,309,196,310,198,311,199,312,200,313,314,317,353,361,367,377,382,384,386,387,392,401,407,410,417,419,421,422,423,428,430,433,434,436,437,438,440,443,444,446,448,449,450,451,457,458,463,464,471,473,477,478,480,481,482,484,492,576,578,580,581,583,596,591,594,599,601,602,604,605,609,610,611,615,617,619,620,623,625,627,631,634,632,637,649,647,653,658,661,663,665,670,668,672,673,674,675,676,681,682,683,684,686,690,692,693,699,696,704,707,708,714,717,725,719,722,726,824,811,827,833,829,834,838,842,846,852,850,853,854,857,862,861,864,869,870,872,875,879,877,884,137,140,141,142,143,144,146,147,148,149,150,152,153,156,160,161,162,163,164,168,169,170,171,172,173,174,185,187,192,318,193,319,194,320,197,321,201,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,354,356,359,360,363,364,365,366,369,370,371,375,378,379,380,381,383,385,388,389,393,394,400,404,405,406,408,409,411,412,413,414,418,425,426,427,429,431,432,435,439,441,442,445,447,453,454,455,456,459,461,462,466,467,468,469,470,472,475,476,479,483,485,487,488,491,560,573,574,575,577,582,584,588,589,592,593,595,597,598,600,603,607,608,612,613,614,616,618,621,622,624,626,628,629,630,633,635,636,640,641,642,643,644,645,646,648,650,651,652,654,657,659,660,662,664,667,669,671,677,678,680,685,687,688,695,698,701,702,703,705,706,709,710,711,712,713,715,716,718,720,721,724,822,823,825,826,828,831,832,839,840,841,843,844,845,847,848,849,851,855,856,858,859,863,865,866,867,868,871,873,874,876,878,880,881,882,883,306), modResource.published=1, modResource.deleted=0
0.0000041: Replaced TV conditions
0.0001311: Sorted by modResource.publishedon, DESC
0.0000029: Limited to 12, offset 0
0.0003099: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `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`.`content`, `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`, IFNULL(`TVblogandnewsarticlesource`.`value`, '') AS `BlogAndNewsArticleSource`, IFNULL(`TVblogandnewssummaryimage`.`value`, ' ') AS `BlogAndNewsSummaryImage`, IFNULL(`TVblogandnewssummaryimageclass`.`value`, 'DontShowMe') AS `BlogAndNewsSummaryImageClass`, IFNULL(`TVblogauthorname`.`value`, 'Jon Wallace') AS `BlogAuthorName`, IFNULL(`TVeventinfo_boothnumber`.`value`, '') AS `EVENTINFO_BoothNumber`, IFNULL(`TVeventinfo_city`.`value`, '') AS `EVENTINFO_City`, IFNULL(`TVeventinfo_country`.`value`, '') AS `EVENTINFO_Country`, IFNULL(`TVeventinfo_enddate`.`value`, '') AS `EVENTINFO_EndDate`, IFNULL(`TVeventinfo_shortdescription`.`value`, '') AS `EVENTINFO_ShortDescription`, IFNULL(`TVeventinfo_startdate`.`value`, '') AS `EVENTINFO_StartDate`, IFNULL(`TVnewsandblogarticle_isfeatured`.`value`, 'No') AS `NewsAndBlogArticle_isFeatured` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVblogandnewsarticlesource` ON `TVblogandnewsarticlesource`.`contentid` = `modResource`.`id` AND `TVblogandnewsarticlesource`.`tmplvarid` = 80 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVblogandnewssummaryimage` ON `TVblogandnewssummaryimage`.`contentid` = `modResource`.`id` AND `TVblogandnewssummaryimage`.`tmplvarid` = 81 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVblogandnewssummaryimageclass` ON `TVblogandnewssummaryimageclass`.`contentid` = `modResource`.`id` AND `TVblogandnewssummaryimageclass`.`tmplvarid` = 82 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVblogauthorname` ON `TVblogauthorname`.`contentid` = `modResource`.`id` AND `TVblogauthorname`.`tmplvarid` = 55 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_boothnumber` ON `TVeventinfo_boothnumber`.`contentid` = `modResource`.`id` AND `TVeventinfo_boothnumber`.`tmplvarid` = 61 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_city` ON `TVeventinfo_city`.`contentid` = `modResource`.`id` AND `TVeventinfo_city`.`tmplvarid` = 57 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_country` ON `TVeventinfo_country`.`contentid` = `modResource`.`id` AND `TVeventinfo_country`.`tmplvarid` = 58 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_enddate` ON `TVeventinfo_enddate`.`contentid` = `modResource`.`id` AND `TVeventinfo_enddate`.`tmplvarid` = 64 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_shortdescription` ON `TVeventinfo_shortdescription`.`contentid` = `modResource`.`id` AND `TVeventinfo_shortdescription`.`tmplvarid` = 60 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventinfo_startdate` ON `TVeventinfo_startdate`.`contentid` = `modResource`.`id` AND `TVeventinfo_startdate`.`tmplvarid` = 59 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVnewsandblogarticle_isfeatured` ON `TVnewsandblogarticle_isfeatured`.`contentid` = `modResource`.`id` AND `TVnewsandblogarticle_isfeatured`.`tmplvarid` = 85 WHERE ( EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'articlestags' AND tc.contentid = `modResource`.id AND FIND_IN_SET('mytag',tc.value) > 0) AND `modResource`.`parent` IN (20,32,305,27,127,131,134,136,145,151,126,155,157,158,159,166,167,180,186,189,190,191,308,195,309,196,310,198,311,199,312,200,313,314,317,353,361,367,377,382,384,386,387,392,401,407,410,417,419,421,422,423,428,430,433,434,436,437,438,440,443,444,446,448,449,450,451,457,458,463,464,471,473,477,478,480,481,482,484,492,576,578,580,581,583,596,591,594,599,601,602,604,605,609,610,611,615,617,619,620,623,625,627,631,634,632,637,649,647,653,658,661,663,665,670,668,672,673,674,675,676,681,682,683,684,686,690,692,693,699,696,704,707,708,714,717,725,719,722,726,824,811,827,833,829,834,838,842,846,852,850,853,854,857,862,861,864,869,870,872,875,879,877,884,137,140,141,142,143,144,146,147,148,149,150,152,153,156,160,161,162,163,164,168,169,170,171,172,173,174,185,187,192,318,193,319,194,320,197,321,201,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,354,356,359,360,363,364,365,366,369,370,371,375,378,379,380,381,383,385,388,389,393,394,400,404,405,406,408,409,411,412,413,414,418,425,426,427,429,431,432,435,439,441,442,445,447,453,454,455,456,459,461,462,466,467,468,469,470,472,475,476,479,483,485,487,488,491,560,573,574,575,577,582,584,588,589,592,593,595,597,598,600,603,607,608,612,613,614,616,618,621,622,624,626,628,629,630,633,635,636,640,641,642,643,644,645,646,648,650,651,652,654,657,659,660,662,664,667,669,671,677,678,680,685,687,688,695,698,701,702,703,705,706,709,710,711,712,713,715,716,718,720,721,724,822,823,825,826,828,831,832,839,840,841,843,844,845,847,848,849,851,855,856,858,859,863,865,866,867,868,871,873,874,876,878,880,881,882,883,306) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 ) ORDER BY modResource.publishedon DESC LIMIT 12 "
0.0001390: SQL executed
0.0001070: Total rows: 0
0.0000060: Rows fetched
0.0000119: Prepared and processed TVs
0.0000010: Returning processed chunks
0.0037351: Total time
16 777 216: Memory usage
This SQL query doesn’t look wrong.
Have you tried replacing 'mytag' in the subquery with an actual value of one of your tags? (Maybe with Tag1 or Tag2 that you used in &tvFilters before).
Yes, If I replace ‘mytag’ with one of the tag names it works - but so was using &tvFilters with one tag name - the issue is that if a resource has 2 tags applied it does not appear in the results for either tag being searched for.
Maybe I’m misunderstanding your question.
Can you give a concrete example what you are trying to do?
value in the database table modx_site_tmplvar_contentvalues for the resources that don’t appear in the result?Please switch to Tagger, if that’s possible. Tag handling with TVs is very ineffective.
Maybe - im not sure what the function of the code you have sent on actually does so hard for me to tell to be honest - Have a quick look at Retrieve articles outside of articles by tag | MODX Community Forums. Basically - using &tvFilter &tvFilters=articlestags==Tag1` means you can use getResources / pdoResources to pull the resources from a blog (run by articles), or any parents of resources that match the tag included on the TV called articlestags. That works great… BUT… the issue is, if a resource has more than one tag value included on the articlestags TV - then it does not display when the resources are fetched.
So, on a pdoResources call where I say display results that match articlestags TV value that matches tag1 - I want all resources that have tag1 value included on their articlestags TV to appear - even those resources which have other values in the articlestags TV. e.g. a resource might have “tag1,tag2,tag3” - this resource should appear in the pdoResources call to display resources which have tag1 - curerntly it does not do this. The resource with multiple values in the articlestags field does not display in the results pdoResources returns.
Thanks @Jako - Id rather keep things with just a TV - I can make the articlestags TV a string with commas seperating values if that would make it easier to get a solution working as I have things setup currently?
@Jako - Tagger seems to work well - however… can it work on articles resources? its working well on none articles pages - but the options to add tags on an articles page doesnt appear for me despite me setting the template ID in the Group settings for the articles pages.
The functionality of the property &tvFilters is limited. If you want a clean solution to query a comma-separated list of values, you need the MySQL functions FIND_IN_SET or REGEXP that aren’t supported by &tvFilters.
The FIND_IN_SET function returns the position of a value in a comma-separated list.
--returns 1 because tag1 is at the first position
SELECT FIND_IN_SET('tag1','tag1,tag2,tag3');
--returns 2 because tag1 is at the second position
SELECT FIND_IN_SET('tag1','tag2,tag1');
--returns 0 because tag1 is not in the list
SELECT FIND_IN_SET('tag1','tag2,tag3');
--returns all rows of the table modx_site_tmplvar_contentvalues where tag1 is in the list
SELECT * FROM `modx_site_tmplvar_contentvalues` WHERE FIND_IN_SET('tag1',value) > 0
The rest of the subquery is just to connect the database table for the TVs with the table of the resources.
I don’t know why these resources are not displayed in the result.
When I test it with this subquery, it does work.
I have never worked with Tagger in the last years. It is old and somehow deprecated. I would switch to Collections + Tagger if possible.