pdoResources - tvFilters not working properly

[excavator]

Having a pdoResources call like:

[[!pdoResources?
	&parents=`0`
	&includeTVs=`date,finished`
	&sortby=`date`
	&sortdir=`DESC`
	&showUnpublished=`0`
	&tvFilters=`date>>2024-01%,finished===0`
	&limit=`5`
	&showLog=`1`
]]

for some reason, the tvFilters is not working as expected, when I provide two TV’s with the AND condition.
date has type Date, finished is a radio with values 0 and 1.

Running the generated SQL directly, returns 0 values. By removing the AND `TVfinished`.`value` = '0' from WHERE, I get the correct rows for the given date, and finished is set to 0. See image below.

Here’s the whole log:

0.0000539: pdoTools loaded
0.0000131: xPDO query object created
0.0000839: Added TVs filters
0.0003581: Included list of tvs: date, finished
0.0001168: leftJoined MODX\Revolution\modTemplateVarResource as TVdate
0.0000811: leftJoined MODX\Revolution\modTemplateVarResource as TVfinished 
0.0001280: Added selection of modResource: `id`, `type`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `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`, `alias_visible`
0.0000062: Added selection of TVdate: IFNULL(`value`, '') AS `tv.date`
0.0000050: Added selection of TVfinished: IFNULL(`value`, '0') AS `tv.finished` 
0.0000091: Replaced TV conditions
0.0000100: Processed additional conditions 
0.0001471: Added where condition: 0=`TVdate`.`value` > '2024-01%' AND `TVfinished`.`value` = 0, modResource.published=1, modResource.deleted=0 
0.0000041: Replaced TV conditions
0.0000410: Sorted by CAST(`TVdate`.`value` AS DATETIME), DESC 
0.0000021: Limited to 5, offset 0 
0.0001628: SQL prepared "SELECT `modResource`.`id`, `modResource`.`type`, `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`, `modResource`.`alias_visible`, IFNULL(`TVdate`.`value`, '') AS `tv.date`, IFNULL(`TVfinished`.`value`, '0') AS `tv.finished` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVdate` ON `TVdate`.`contentid` = `modResource`.`id` AND `TVdate`.`tmplvarid` = 14 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVfinished` ON `TVfinished`.`contentid` = `modResource`.`id` AND `TVfinished`.`tmplvarid` = 16 WHERE ( `TVdate`.`value` > '2024-01%' AND `TVfinished`.`value` = 0 AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 ) ORDER BY CAST(`TVdate`.`value` AS DATETIME) DESC LIMIT 5 "
0.0011969: SQL executed
0.0000112: Rows fetched
0.0000169: Prepared and processed TVs
0.0000019: Returning processed chunks
0.0025139: Total time 18 874 368

[halftrainedharry]

@excavator

finished is a radio with values 0 and 1.

The problem is probably, that you have a default value (or “Default Option”) for this TV with the value of 0.

MODX has this peculiarity, that the value of a TV is not saved to the database table modx_site_tmplvar_contentvalues, when the value is equal to the default value. (A database entry is only created when the value differs from the default value.)

The pdoResources query doesn’t handle rows with the default value correctly when you filter by them.
I believe getResources creates the query correctly though.

[excavator]

Well, this is interesting. modx_site_tmplvar_contentvalues did contain the 0 in there, but as soon as I switched to getResources, it started working as expected.
There’s a lot of imported resources / TV’s so I bet the problem is in there somewhere.

Thanks!