pdoResources and tvFilters

☆ A M B ☆
24,524 Posts

sottwell Reply #1, 12 years, 2 months ago

I don't seem to be able to get pdoResources to work with tvFilters. ShowLog just says 0 records were returned.

[[!pdoResources? 
    &parents=`1`
    &limit=`1`
    &tpl=`popularTripsTpl` 
    &tvFilters=`featured==1`
    &includeTVs=`tripthumb,mml_pagetitle,mml_introtext,featured,count-trips` 
    &prepareTVs=`1`
    &loadModels=`migxmultilang`
    &prepareSnippet = `mmlTranslatePdoToolsRow`
    &showLog=`1`
]]

A very similar listing using a TV to orderby works fine:

[[!pdoResources? 
    &parents=`1`
    &limit=`3`
    &tpl=`popularTripsTpl` 
    &sortby=`count-trips`
    &sortdir=`DESC`
    &includeTVs=`tripthumb,mml_pagetitle,mml_introtext,count-trips` 
    &prepareTVs=`1`
    &loadModels=`migxmultilang`
    &prepareSnippet = `mmlTranslatePdoToolsRow`
]]

The log output:

0.0002019: pdoTools loaded
0.0000432: Loaded model "migxmultilang" from "/core/components/migxmultilang/model/"
0.0000498: xPDO query object created
0.0000701: Added TVs filters
0.0007250: Included list of tvs: mml_pagetitle, mml_introtext, tripthumb, count-trips, featured
0.0005169: leftJoined modTemplateVarResource as TVmml_pagetitle
0.0005069: leftJoined modTemplateVarResource as TVmml_introtext
0.0004590: leftJoined modTemplateVarResource as TVtripthumb
0.0005240: leftJoined modTemplateVarResource as TVcount-trips
0.0004590: leftJoined modTemplateVarResource as TVfeatured
0.0007908: 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`, `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.0000191: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.mml_pagetitle`
0.0000150: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.mml_introtext`
0.0000141: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.tripthumb`
0.0000150: Added selection of modTemplateVarResource: IFNULL(`value`, '0') AS `tv.count-trips`
0.0000150: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.featured`
0.0000808: Replaced TV conditions
0.0008380: Processed additional conditions
0.0015631: Added where condition: 0=`TVfeatured`.`value` LIKE '1', modResource.parent:IN(1,18,19,20,21,22,23,24,25), modResource.published=1, modResource.deleted=0
0.0000470: Replaced TV conditions
0.0002229: Sorted by modResource.publishedon, DESC
0.0000081: Limited to 1, offset 0
0.0004170: 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`.`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(`TVmml_pagetitle`.`value`, '') AS `tv.mml_pagetitle`, IFNULL(`TVmml_introtext`.`value`, '') AS `tv.mml_introtext`, IFNULL(`TVtripthumb`.`value`, '') AS `tv.tripthumb`, IFNULL(`TVcount-trips`.`value`, '0') AS `tv.count-trips`, IFNULL(`TVfeatured`.`value`, '') AS `tv.featured` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVmml_pagetitle` ON `TVmml_pagetitle`.`contentid` = `modResource`.`id` AND `TVmml_pagetitle`.`tmplvarid` = 1 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVmml_introtext` ON `TVmml_introtext`.`contentid` = `modResource`.`id` AND `TVmml_introtext`.`tmplvarid` = 4 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVtripthumb` ON `TVtripthumb`.`contentid` = `modResource`.`id` AND `TVtripthumb`.`tmplvarid` = 8 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVcount-trips` ON `TVcount-trips`.`contentid` = `modResource`.`id` AND `TVcount-trips`.`tmplvarid` = 9 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVfeatured` ON `TVfeatured`.`contentid` = `modResource`.`id` AND `TVfeatured`.`tmplvarid` = 10 WHERE  ( `TVfeatured`.`value` LIKE '1' AND `modResource`.`parent` IN (1,18,19,20,21,22,23,24,25) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )  ORDER BY modResource.publishedon DESC LIMIT 1 "
0.0001550: SQL executed
0.0001361: Total rows: 0
0.0000169: Rows fetched
0.0000391: Prepared and processed TVs
0.0000010: Returning processed chunks
0.0073311: Total time
13 631 488: Memory usage

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

5,888 Posts

Bruno17 Reply #2, 12 years, 2 months ago

this doesn't look right to me

0=`TVfeatured`.`value` LIKE '1'

alternatively, you could try:

[[!pdoResources?
    &parents=`1`
    &limit=`1`
    &tpl=`popularTripsTpl`
    &where=`{"TVfeatured.value":"1"}`
    &includeTVs=`tripthumb,mml_pagetitle,mml_introtext,featured,count-trips`
    &prepareTVs=`1`
    &loadModels=`migxmultilang`
    &prepareSnippet = `mmlTranslatePdoToolsRow`
    &showLog=`1`
]]

does this work?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
24,524 Posts

sottwell Reply #3, 12 years, 2 months ago

No, still nothing. Didn't work when I tried it using getResources, either.

Wait... it does work when I do it right.

[[!pdoResources? 
    &parents=`1`
    &limit=`1`
    &tpl=`popularTripsTpl` 
    &includeTVs=`tripthumb,mml_pagetitle,mml_introtext,count-trips,featured` 
    &where=`{"TVfeatured.value":"1"}` 
    &prepareTVs=`1`
    &loadModels=`migxmultilang`
    &prepareSnippet = `mmlTranslatePdoToolsRow`
]]

[ed. note: sottwell last edited this post 12 years, 2 months ago.]

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

☆ A M B ☆
350 Posts

boomerang Reply #4, 11 years, 10 months ago

Hi there,

I'm not getting on too well with pdoResources and tvFilters either. I'm trying to filter so that only resources which have future dates listed in a TV are shown. I've tried the solution https://forums.modx.com/index.php/topic,63824.msg361181.html but this doesn't seem to work with pdoResources.

Then moved on to try my luck with &where but again no joy! My code:

[[pdoResources? &parents=`20,10,11` &depth=`0`  &limit=`5` &tpl=`dateTpl` &includeTVs=`eventDate` &processTVs=`1` &where=`{"TVeventDate>":"[[currentDate:date=`%d/%m/%Y`]]"}` &prepareTVs=`1`&sortby=`eventDate` &sortdir=`ASC` &showLog=`1`]]

I've checked that currentDate does indeed return the date format I want and does the info in the dateTpl.

currentDate is a simple snippet:

return time();

Log report

0.0002589: pdoTools loaded
0.0000751: xPDO query object created
0.0008371: Included list of tvs: eventDate
0.0008140: leftJoined modTemplateVarResource as TVeventdate
0.0010641: 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`, `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.0000279: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.eventDate`
0.0000601: Replaced TV conditions
0.0000939: Processed additional conditions
0.0010569: Added where condition: TVeventDate>=29/10/2014, modResource.parent:IN(20,10,11,21,22,23,24,25), modResource.published=1, modResource.deleted=0
0.0000560: Replaced TV conditions
0.0003428: Sorted by CAST(`TVeventdate`.`value` AS DATETIME), ASC
0.0000091: Limited to 5, offset 0
0.0005031: 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`.`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(`TVeventdate`.`value`, '') AS `tv.eventDate` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVeventdate` ON `TVeventdate`.`contentid` = `modResource`.`id` AND `TVeventdate`.`tmplvarid` = 3 WHERE  ( `modResource`.`parent` IN (20,10,11,21,22,23,24,25) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )  ORDER BY CAST(`TVeventdate`.`value` AS DATETIME) ASC LIMIT 5 "
0.0001609: SQL executed
0.0001850: Total rows: 3
0.0000660: Rows fetched
0.0150440: Prepared and processed TVs
0.0021448: Loaded chunk "dateTpl"
0.0070648: Returning processed chunks
0.0278659: Total time

Any suggestions please.

Helen Warner
Number one pixel!
Proud to be linked with MODX at Crimson Pixel

☆ A M B ☆
24,524 Posts

sottwell Reply #5, 11 years, 10 months ago

As far as I know, you can't compare dates like that - TVeventDate>=29/10/2014 doesn't work. You need to convert the date strings to timestamps, and compare the timestamps.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

5,888 Posts

Bruno17 Reply #6, 11 years, 10 months ago

try

&where=`{"TVeventDate.value:>":"[[currentDate:date=`%Y-%m-%d 00:00:00`]]"}`

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
350 Posts

boomerang Reply #7, 11 years, 10 months ago

Unfortunately neither suggestions worked - I tried putting date back to unix timestamp and comparing then tried Bruno's suggestion.

No joy.

EDIT: Actually when I check again my dateTpl doesn't output a unix timestamp so I'll check this properly. My snippet does though

[ed. note: boomerang last edited this post 11 years, 10 months ago.]

Helen Warner
Number one pixel!
Proud to be linked with MODX at Crimson Pixel

5,888 Posts

Bruno17 Reply #8, 11 years, 10 months ago

If this is a normal date-TV it should be stored in that format in the db:

%Y-%m-%d 00:00:00

so, IMO, it should work, also TV-filters should do it.

and in any case your snippet should be called uncached

[[!currentDate:date=`%Y-%m-%d 00:00:00`]]

also pdoResources should be called uncached in that case.

but I think, that isn't the issue here.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
350 Posts

boomerang Reply #9, 11 years, 10 months ago

Ok after a bit more digging around this is what worked - thank you Bruno.

[[!pdoResources? &parents=`20,10` &depth=`0`  &limit=`1` &tpl=`dateTpl` &includeTVs=`eventDate` &processTVs=`1` &where=`{"eventDate:>":"[[!currentDate:date=`%Y-%m-%d`]]"}` &sortby=`eventDate` &sortdir=`ASC`]]

I added the snippet uncached - initially that didn't work because I have another call doing much the same which was cached, both have to be uncached in order to work.

The call for the tv simply needed to the name of the tv, in my case eventDate and then it worked! Many thanks for your input.

Helen Warner
Number one pixel!
Proud to be linked with MODX at Crimson Pixel

179 Posts

Dave Smith Reply #10, 10 years, 9 months ago

I know this is an old topic, but it's still quite relevant (to me at least), and I have some new insight given the changes to getResources that are described here for v1.4.2: https://rtfm.modx.com/extras/revo/getresources#getResources-SelectionProperties

It seems the "==" operator doesn't cast the input type. For the numerical value of the checkbox I was trying to set to 1 or 0 (zero) I had to use the === operator to get the type cast to numerical. Then it worked, and I imagine Sotwell's original problem would be solved.

I have a new, related problem: it seems pdoResources might not know about all the new operators, and it doesn't appear to honour default values. So if you have a single checkbox and it's supposed to represent 0 when unchecked and 1 when checked, the default that is presumably null won't be detectable as 0, and you can't then use the other operators (<=> "null safe equals"?) to find the ones that are supposed to be 0 (zero).

I'm still testing this issue, in an attempt to make this work without needing to go back to getResources.