Getresources filter to match IDs from a TV with multiple IDs. Wild card is delivering multiple results

1

I have a filter on a TV that has “multi select” for pages so the end result is yielding a list of IDs comma separated. However, I have trials using wildcards with AND/OR statements BUT if I am filtering for just 14, I can get results 14,140,1014,514. Any suggestions?

[[!getResources?
&tpl=MembersTpl
&parents=169
&depth=0
&limit=0
&includeContent=1
&includeTVs=1
&tvFilters=Trades==[[*id]],Trades==[[*id]]%||Trades==%[[*id]]
&sortby=RAND()
]]

2

There are different solutions.

If you want a clean solution, use a custom database table instead of the TV with multiple values.

In the case of the “multi select” TV, you could use FIND_IN_SET in the query

[[pdoResources?
    ...
    &includeTVs=`Trades`
    &where=`FIND_IN_SET([[*id]], REPLACE(Trades, '||', ',')) > 0`
]]

or maybe REGEXP.

If you want to make “&tvFilters” work, you also have to include the separator (between the IDs in the TV value) into the query, to make sure you match the whole ID.

3

This did the trick! Thank you @halftrainedharry
[[pdoResources?
…
&includeTVs=Trades
&where=FIND_IN_SET([[*id]], REPLACE(Trades, '||', ',')) > 0
]]

Do you know if there is a way to do that same call for &totalVar=``?

4

I don’t understand what you mean by that.

5

My goal is to use the same query as

[[pdoResources?
…
&includeTVs=Trades
&where=FIND_IN_SET([[*id]], REPLACE(Trades, '||', ',')) > 0
]]

to get a total resource count. So if that query pulls 5 IDs then I need the count to show 5

6

Does the totalVar placeholder not show the correct number of results?

7

No, it does not work with this code.

[[pdoResources?
&parents=169
&resources=[[-+id]]
&includeTVs=Trades
&processTVs=1
&depth=0
&showHidden=1
&where=FIND_IN_SET([[*id]], REPLACE(Trades, '||', ',')) > 0
&totalVar=[[+id]]Cnt
]]

3.[[+pagetitle]] ([[+[[+id]]Cnt]])
8

Try adding the property “setTotal”:

&setTotal=`1`