Filtering using TVFilters in GetResources / PdoResources

Support
1

Hi all,

I’m trying to build what I think are relatively simple queries using pdoResources / tvFilters.

The sort of thing I want to achieve would be described as:

(groups contains "Teachers" OR groups contains "All Groups") 
AND 
(local-authorities contains "Glasgow City" OR local-authorities contains "Scotland-wide")

My code currently builds the tvFilters like this:

&tvFilters=`groups==%Teachers%||groups==%All Groups%,local-authorities==%Glasgow City%||local-authorities==%Scotland%`

This doesn’t work because - since the OR delimiter is processed first - it will just return a tonne of results based on how it interprets the filters.

So, what is the correct syntax to achieve (A or B) AND (X or Y) within the tvFilters?

Or is this not possible?

Many thanks as always.

2

You probably have to create your own &where condition instead of using the &tvFilters property (or alternatively duplicate the getResources snippet and change the code to process AND first).

Something like this might work:

&where=`["EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'groups' AND tc.contentid = `modResource`.id AND (tc.value LIKE '%Teachers%' OR tc.value LIKE '%All Groups%')) AND EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'local-authorities' AND tc.contentid = `modResource`.id AND (tc.value LIKE '%Glasgow City%' OR tc.value LIKE '%Scotland%'))"]`
3

Many thanks @halftrainedharry …

The code behind this has been in place for a while and works well - I’ve only just been asked to add the OR conditions in now - hence have reached this point.

I’ll have a go at rewriting it using &where …

4

Hey @halftrainedharry …

I currently have a &where parameter in addition to the &tvFilters:

&where=`[{ "template:IN" : [ 11 ] },{ "parent:IN" : [ 116 ] }]`

Any advice on how the syntax would work to add your suggested code as additional AND conditions to this?

I have tried the following:

&where=`[{ "template:IN" : [ 11 ] },{ "parent:IN" : [ 116 ] },{EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = "groups" AND tc.contentid = `modResource`.id AND (tc.value LIKE "%Teachers%" OR tc.value LIKE "%All Groups%"))}]`

but the added condition seems to be ignored and has no effect on the number of results returned [it should].

Any idea what I’m doing wrong?

Thanks again.

5

I think the subquery has to be a string "EXISTS ..." and not an object {EXISTS ...}.

I tested it with pdoResources and it only works if the property &includeTVs is empty.

If the TVs are included, then the code tries to be clever and replaces every value of the TV-name in the where-condition with the proper query value (groups becomes `TVgroups`.`value` ). This can help to simplify the where condition like in this example:

[[pdoResources?
    ...
    &includeTVs=`groups,local-authorities`
    &where=`[{"template:IN":[11]},{"parent:IN":[116]},"(groups LIKE '%Teachers%' OR groups LIKE '%All Groups%') AND (local-authorities LIKE '%Glasgow City%' OR local-authorities LIKE '%Scotland%')"]`
]]

But the problem is, that the code replaces groups in %All Groups% as well. You’d have to rename the TV to make it work with pdoResources.

Also, setting the property &showLog=`1` and then checking the SQL query can be very helpful to analyze why something doesn’t work.

6

Thanks again @halftrainedharry …

I think we might be getting closer.

I added the TV names into &includeTVs and changed the “All Groups” option to “Relevant to all”.

Then I tried this method:

I’ve still to check the numbers but it looks like that’s working.

I’m going to try to roll it out throughout the other TVs and automate it :crossed_fingers:

7

Ok, so this is probably the “correct” way to do it with pdoResources.
(This also avoids the problem with the %All Groups% replacement.)

[[pdoResources?
    ...
    &includeTVs=`groups,local-authorities`
    &where=`[{"template:IN":[11]},{"parent:IN":[116]},{"groups:LIKE":"%Teachers%", "OR:groups:LIKE":"%All Groups%"},{"local-authorities:LIKE":"%Glasgow City%", "OR:local-authorities:LIKE":"%Scotland%"}]`
]]

With getResources I think you have to use this ugly EXISTS subquery (and it only works if the TVs have no default value).

[[!getResources?
    ...
    &where=`[{"template:IN":[20]},{"parent:IN":[1]},"EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'groups' AND tc.contentid = `modResource`.id AND (tc.value LIKE '%Teachers%' OR tc.value LIKE '%All Groups%')) AND EXISTS (SELECT 1 FROM `modx_site_tmplvars` t INNER JOIN `modx_site_tmplvar_contentvalues` tc ON tc.tmplvarid = t.id WHERE t.name = 'local-authorities' AND tc.contentid = `modResource`.id AND (tc.value LIKE '%Glasgow City%' OR tc.value LIKE '%Scotland%'))"]`
]]
8

Perfect! That works and is easier to produce from code :+1:

9

Just one caveat: This only works for 2 OR conditions.

If you have 3 like this

{"groups:LIKE":"%Teachers%","OR:groups:LIKE":"%All Groups%", "OR:groups:LIKE":"%Other%"}

it won’t work, because there are 2 identical keys in the object.

10

I don’t think I’ll need more than two [hopefully!]

But multiple objects are OK?

11

I think so.
If in doubt verify the SQL query to make sure it works correctly.

12

It seems to be working a treat - bit more testing to do but I think we’re almost there.

Oddly

&showLog=`1`

on the pdoResources call doesn’t seem to have any effect.

Should the log detail just appear before or after the results on the page?

13

Are you on MODX 3?
I think in this version you have to use a placeholder to see the log output

<pre>[[+pdoResourcesLog]]</pre>
14

That’s it :+1: yes MODX3

I think this is now working as expected @halftrainedharry - above and beyond as always!

Hugely appreciated.

15

As an appendix: For more than 2 OR conditions this syntax should work with pdoResources:

[[pdoResources?
    ...
    &includeTVs=`groups,local-authorities`
    &where=`[{"template:IN":[11]},{"parent:IN":[116]},[{"groups:LIKE":"%Teachers%"},{"OR:groups:LIKE":"%All Groups%"},{"OR:groups:LIKE":"%Other%"}] ]`
]]

Be sure to leave a space between the 2 closing arrays (] ]) in the &where property, so that it doesn’t get interpreted as the end of a MODX tag.

1 Like
16

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”.