MODX Community

pdoResources don't retreive my TVs

I have a very long querie with GetResources, so I want to optimize it by using pdoResources.
However, the TVs are not retreived and I have an error on the debug log but I don’t know how to solved it and what is the cause if the issue…

My pdoResoucres call :

[[!pdoResources?
  	&parents=`21`
  	&sortby=`menuindex`
  	&sortdir=`ASC`
  	&tpl=`FicheProTpl`
    &includeTVs=`Pro__Adresse,Pro__Categorie,Pro__Certifications,Pro__CP,Pro__Description,Pro__Effectifs,Pro__Geolocalisation,Pro__Gerant,Pro__Mail,Pro__Presentation,Pro__Tel--fixe,Pro__Tel--mobile,Pro__Ville,Pro__Web`
  	&limit=`1000`
	&includeContent=`0`
  	&prepareTVs=`1`
  	&processTVs=`1`
  	&showLog=`1`
  ]]

Here the debug log for this call :

0.0000479: pdoTools loaded
0.0000119: xPDO query object created
0.0006342: Included list of tvs: Pro__Certifications, Pro__Adresse, Pro__Mail, Pro__Tel--fixe, Pro__Web, Pro__Description, Pro__Ville, Pro__Categorie, Pro__Geolocalisation, Pro__CP, Pro__Tel--mobile, Pro__Presentation, Pro__Effectifs, Pro__Gerant
0.0000830: leftJoined modTemplateVarResource as TVpro__certifications
0.0000651: leftJoined modTemplateVarResource as TVpro__adresse
0.0000648: leftJoined modTemplateVarResource as TVpro__mail
0.0000551: leftJoined modTemplateVarResource as TVpro__tel--fixe
0.0000639: leftJoined modTemplateVarResource as TVpro__web
0.0000629: leftJoined modTemplateVarResource as TVpro__description
0.0000629: leftJoined modTemplateVarResource as TVpro__ville
0.0000541: leftJoined modTemplateVarResource as TVpro__categorie
0.0000632: leftJoined modTemplateVarResource as TVpro__geolocalisation
0.0000629: leftJoined modTemplateVarResource as TVpro__cp
0.0000641: leftJoined modTemplateVarResource as TVpro__tel--mobile
0.0000548: leftJoined modTemplateVarResource as TVpro__presentation
0.0000629: leftJoined modTemplateVarResource as TVpro__effectifs
0.0000620: leftJoined modTemplateVarResource as TVpro__gerant
0.0000730: Added selection of modResource: `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `alias_visible`, `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.0000060: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Certifications`
0.0000050: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Adresse`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Mail`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Tel--fixe`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Web`
0.0000050: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Description`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Ville`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Categorie`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Geolocalisation`
0.0000050: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__CP`
0.0000031: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Tel--mobile`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Presentation`
0.0000041: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Effectifs`
0.0000050: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.Pro__Gerant`
0.0004640: Processed additional conditions
0.0005569: Added where condition: modResource.parent:IN(21,262,266,267,268,269,217,218,219,220,221,222,223,224,225,226,227,228,263,264,270,271,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,261,258,259), modResource.published=1, modResource.deleted=0
0.0001030: Replaced TV conditions
0.0001452: Sorted by modResource.menuindex, ASC
0.0000029: Limited to 1000, offset 0
0.0002210: SQL prepared "SELECT `modResource`.`id`, `modResource`.`type`, `modResource`.`contentType`, `modResource`.`pagetitle`, `modResource`.`longtitle`, `modResource`.`description`, `modResource`.`alias`, `modResource`.`alias_visible`, `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(`TVpro__certifications`.`value`, '') AS `tv.Pro__Certifications`, IFNULL(`TVpro__adresse`.`value`, '') AS `tv.Pro__Adresse`, IFNULL(`TVpro__mail`.`value`, '') AS `tv.Pro__Mail`, IFNULL(`TVpro__tel--fixe`.`value`, '') AS `tv.Pro__Tel--fixe`, IFNULL(`TVpro__web`.`value`, '') AS `tv.Pro__Web`, IFNULL(`TVpro__description`.`value`, '') AS `tv.Pro__Description`, IFNULL(`TVpro__ville`.`value`, '') AS `tv.Pro__Ville`, IFNULL(`TVpro__categorie`.`value`, '') AS `tv.Pro__Categorie`, IFNULL(`TVpro__geolocalisation`.`value`, '') AS `tv.Pro__Geolocalisation`, IFNULL(`TVpro__cp`.`value`, '') AS `tv.Pro__CP`, IFNULL(`TVpro__tel--mobile`.`value`, '') AS `tv.Pro__Tel--mobile`, IFNULL(`TVpro__presentation`.`value`, '') AS `tv.Pro__Presentation`, IFNULL(`TVpro__effectifs`.`value`, '') AS `tv.Pro__Effectifs`, IFNULL(`TVpro__gerant`.`value`, '') AS `tv.Pro__Gerant` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__certifications` ON `TVpro__certifications`.`contentid` = `modResource`.`id` AND `TVpro__certifications`.`tmplvarid` = 38 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__adresse` ON `TVpro__adresse`.`contentid` = `modResource`.`id` AND `TVpro__adresse`.`tmplvarid` = 4 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__mail` ON `TVpro__mail`.`contentid` = `modResource`.`id` AND `TVpro__mail`.`tmplvarid` = 5 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__tel--fixe` ON `TVpro__tel--fixe`.`contentid` = `modResource`.`id` AND `TVpro__tel--fixe`.`tmplvarid` = 6 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__web` ON `TVpro__web`.`contentid` = `modResource`.`id` AND `TVpro__web`.`tmplvarid` = 7 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__description` ON `TVpro__description`.`contentid` = `modResource`.`id` AND `TVpro__description`.`tmplvarid` = 8 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__ville` ON `TVpro__ville`.`contentid` = `modResource`.`id` AND `TVpro__ville`.`tmplvarid` = 9 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__categorie` ON `TVpro__categorie`.`contentid` = `modResource`.`id` AND `TVpro__categorie`.`tmplvarid` = 10 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__geolocalisation` ON `TVpro__geolocalisation`.`contentid` = `modResource`.`id` AND `TVpro__geolocalisation`.`tmplvarid` = 15 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__cp` ON `TVpro__cp`.`contentid` = `modResource`.`id` AND `TVpro__cp`.`tmplvarid` = 28 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__tel--mobile` ON `TVpro__tel--mobile`.`contentid` = `modResource`.`id` AND `TVpro__tel--mobile`.`tmplvarid` = 29 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__presentation` ON `TVpro__presentation`.`contentid` = `modResource`.`id` AND `TVpro__presentation`.`tmplvarid` = 37 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__effectifs` ON `TVpro__effectifs`.`contentid` = `modResource`.`id` AND `TVpro__effectifs`.`tmplvarid` = 39 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpro__gerant` ON `TVpro__gerant`.`contentid` = `modResource`.`id` AND `TVpro__gerant`.`tmplvarid` = 35 WHERE  ( `modResource`.`parent` IN (21,262,266,267,268,269,217,218,219,220,221,222,223,224,225,226,227,228,263,264,270,271,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,261,258,259) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )  ORDER BY modResource.menuindex ASC LIMIT 1000 "
0.0019500: Could not process query, error #1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? AND `modResource`.`deleted` = ? )  ORDER BY modResource.menuindex ASC LIMIT...' at line 1
0.0047648: Total time
4 194 304: Memory usage  

What did I miss ?

Hi,

Well, I believe that you can remove these details from your call as these represent the default values when omitted:

  	&includeContent=`0`
  	&prepareTVs=`1`

Did you try to test it without including this?

&processTVs

I faced some issues when using this parameter. Do you really need to pre-process the TVs according to their output options?
If you need just the stored value, that’s fine. You can remove the call.
If you need to have them pre-processed so the output setting of each TV is respected, then you need to use this option (You may not need it as per your pdoResources call).

Cheers

I have followed your advises but I have the same error…

The problem could be the two hyphens -- in some of your TV-names, because two hyphens mark the beginning of a comment in SQL.

2 Likes

It was this ! Thanks @halftrainedharry
My query speed with GetResources was 18s
Now, with pdoTools, it’s 1,2s