getResources and pdoResources handle &where very differently

[rdegler]

I am having trouble with pdoResources not retrieving the correct resources when I use &where filtering.

When I add the &where pdoResouces does not limit the query to the selected &parents but instead queries the entire context:

The following works and limits itself to child of resources 617

[[!pdoResources?
	&parents=`617`
	&limit=`0`
	&includeTVs=`report-meeting`
	&tvPrefix=``
	&tpl=`reports-table-by-creator-tpl`
	&showHidden=`1`
]]

However this includes all results EVEN resources that are not child of Resource 617 but does apply the &where limit to only return the two templates.

[[!pdoResources?
	&parents=`617`
	&limit=`0`
	&includeTVs=`report-meeting`
	&tvPrefix=``
	&tpl=`reports-table-by-creator-tpl`
	&showHidden=`0`
	&where=`{"template:=":9, "OR:template:=":10}`
]]

getResources works correctly:

[[!getResources?
	&parents=`617`
	&limit=`0`
	&includeTVs=`1`
	&tvPrefix=``
	&tpl=`reports-table-by-creator-tpl`
	&showHidden=`0`
	&where=`{"template:=":9, "OR:template:=":10}`
]]

The number of resources will grow greatly and I think pdoResources will be much faster when querying thousands of records.

System: Modx 2.8.4 on modxcloud

Any advice would be very helpful.

Thanks
Roy

[halftrainedharry]

Try using an array in the where property:

&where=`[{"template:=":9, "OR:template:=":10}]`

Add the property &showLog=`1` and check the SQL query to see if the brackets in the where conditions are now correct.

[rdegler]

I have used the where statement as listed in this thread and as an IN array. Both successfully limit the output to resources with the listed template ids.

The issue is coming from pdoResources ignoring the &parents=617 and instead returning resources with the template INCLUDING resources that are not children of resource id 617. It seems the &where causes pdoResources to ignore the ids in the &parent parameter.

It works correctly using getResources. Is using getResources that much slower than pdoResources when returning thousands of resources?

I was lead to believe that pdoResources was significantly faster.

Might this be a bug with pdoResources? Should I report the issue?

Roy

[halftrainedharry]

If the where property is &where=`{"template:=":9, "OR:template:=":10}`
then the SQL query is

SELECT *
FROM `modx_site_content` AS `modResource` 
WHERE ( 
	`modResource`.`template` = 9 
	OR `modResource`.`template` = 10 
		AND `modResource`.`parent` IN (1,2,3,4,5) 
		AND `modResource`.`published` = 1 
		AND `modResource`.`hidemenu` = 0 
		AND `modResource`.`deleted` = 0
)

which is a problem, because AND has precedence over OR. If the template is equal to 9 then the resource is always in the result no matter what the values of parent, published etc. are.

This is the same as

WHERE ( 
	( `modResource`.`template` = 9 )
	OR ( `modResource`.`template` = 10 AND `modResource`.`parent` IN (1,2,3,4,5) AND `modResource`.`published` = 1 AND `modResource`.`hidemenu` = 0 AND `modResource`.`deleted` = 0 )
)

---

However with the where property &where=`[{"template:=":9, "OR:template:=":10}]`, there are now brackets around the OR condition and the query should be correct.

SELECT *
FROM `modx_site_content` AS `modResource` 
WHERE (
	( `modResource`.`template` = 9 OR `modResource`.`template` = 10 )
	AND `modResource`.`parent` IN (1,2,3,4,5) 
	AND `modResource`.`published` = 1 
	AND `modResource`.`hidemenu` = 0 
	AND `modResource`.`deleted` = 0
)

Using &where=`[{"template:IN":[9,10]}]` the query should be correct as well.

SELECT *
FROM `modx_site_content` AS `modResource` 
WHERE ( 
	`modResource`.`template` IN (9,10) 
	AND `modResource`.`parent` IN (1,2,3,4,5) 
	AND `modResource`.`published` = 1 
	AND `modResource`.`hidemenu` = 0 
	AND `modResource`.`deleted` = 0
)

[rdegler]

@halftrainedharry

&where=[{"template:IN":[9,10]}]

Thanks, Your explanation really clarified the issue.

I went with the IN Array approach. As we will likely develop a third template for resources stored in the archives. It will be much easier to add to the pdoResource calls once implemented.

I thought that parameters like &parents and &resources would have had priority over the other parameters like &where and &tvfilters. Resulting in (&parents AND &resources) then applying (other filtering limits).

I’ll make use of &showlog=1 and brush up on my SQL query knowledge when I encounter unexpected results.

Seems boolean operators like AND, OR, and NOT have been a big focus of my MODX time recently. We are working on adding them to a MODX sites search capabilities.

Your help is greatly appreciated. Thanks so much

Roy