⚠️ Urgent! Active Attacks on MODX Revolution Sites Below Revolution 2.6.5
Subscribe: RSS
  • I've been struggeling with this for a while and used dirty "fixes" to get it working.

    I'm using pdoPage to show products with pricing, but when i'm adding a where query i can't get products between a certain price range. Below a simple version of the code:

    [[!pdoPage?
    &parents=`7`
    &depth=`0`
    &limit=`4`
    &where=`{"TVproductprice.value:>=":250000,"TVproductprice.value:<=":500000}`
    &includeTVs=productprice`
    &tpl=`product.overview.row`
    ]]

    When filtering on text values it works perfect. When using interget values it does some filtering but it seems like it handels it more like Text than a Interger... does anybody know what i'm doing wrong?

    Thnx.

    Roald.
    • Tried the code from donquicky but it doesn't work.

      &where=`{"TVproductprice.value>=":"250000","TVproductprice.value<=":"500000"}`

      i've noticed that ranges from 0 to 999.999 work but when passing 1.000.000 it breaks... any idea?

      • Does pdoPage accepts following setting as pdoResources does?
        &showLog=`1`


        Maybe the log shows some additional information.
          Codeplaza Webdesign: for professional websites at low cost


        • min=750000
          max=1300000

          0.0001719: pdoTools loaded
          0.0000350: xPDO query object created
          0.0015562: Included list of tvs: header.image, propertytown, propertyprice, propertytype, propertylivingsurface, propertylocation
          0.0005310: leftJoined modTemplateVarResource as TVheader.image
          0.0004840: leftJoined modTemplateVarResource as TVpropertytown
          0.0004778: leftJoined modTemplateVarResource as TVpropertyprice
          0.0004749: leftJoined modTemplateVarResource as TVpropertytype
          0.0004771: leftJoined modTemplateVarResource as TVpropertylivingsurface
          0.0004809: leftJoined modTemplateVarResource as TVpropertylocation
          0.0006392: 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.0000160: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.header.image`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertytown`
          0.0000119: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertyprice`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertytype`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertylivingsurface`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertylocation`
          0.0001619: Replaced TV conditions
          0.0000641: Processed additional conditions
          0.0007021: Added where condition: TVpropertyprice.value:>=750000, TVpropertyprice.value:<=1300000, modResource.parent:IN(7), modResource.published=1, modResource.deleted=0, modResource.isfolder=0
          0.0000510: Replaced TV conditions
          0.0002031: Sorted by modResource.publishedon, DESC
          0.0000041: Limited to 4, offset 0
          0.0006931: 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(`TVheader.image`.`value`, '') AS `tv.header.image`, IFNULL(`TVpropertytown`.`value`, '') AS `tv.propertytown`, IFNULL(`TVpropertyprice`.`value`, '') AS `tv.propertyprice`, IFNULL(`TVpropertytype`.`value`, '') AS `tv.propertytype`, IFNULL(`TVpropertylivingsurface`.`value`, '') AS `tv.propertylivingsurface`, IFNULL(`TVpropertylocation`.`value`, '') AS `tv.propertylocation` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVheader.image` ON `TVheader.image`.`contentid` = `modResource`.`id` AND `TVheader.image`.`tmplvarid` = 10 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertytown` ON `TVpropertytown`.`contentid` = `modResource`.`id` AND `TVpropertytown`.`tmplvarid` = 28 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertyprice` ON `TVpropertyprice`.`contentid` = `modResource`.`id` AND `TVpropertyprice`.`tmplvarid` = 30 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertytype` ON `TVpropertytype`.`contentid` = `modResource`.`id` AND `TVpropertytype`.`tmplvarid` = 31 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertylivingsurface` ON `TVpropertylivingsurface`.`contentid` = `modResource`.`id` AND `TVpropertylivingsurface`.`tmplvarid` = 32 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertylocation` ON `TVpropertylocation`.`contentid` = `modResource`.`id` AND `TVpropertylocation`.`tmplvarid` = 38 WHERE  ( `TVpropertyprice`.`value` > '750000' AND `TVpropertyprice`.`value` < '1300000' AND `modResource`.`parent` IN (7) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 AND `modResource`.`isfolder` = 0 )  ORDER BY modResource.publishedon DESC LIMIT 4 "
          0.0014839: SQL executed
          0.0001719: Total rows: 0
          0.0000222: Rows fetched
          0.0000379: Prepared and processed TVs
          0.0000012: Returning processed chunks
          0.0090458: Total time
          6 029 312: Memory usage
          



          min=250000
          0.0002100: pdoTools loaded
          0.0000448: xPDO query object created
          0.0009251: Included list of tvs: header.image, propertytown, propertyprice, propertytype, propertylivingsurface, propertylocation
          0.0003650: leftJoined modTemplateVarResource as TVheader.image
          0.0003200: leftJoined modTemplateVarResource as TVpropertytown
          0.0002968: leftJoined modTemplateVarResource as TVpropertyprice
          0.0002940: leftJoined modTemplateVarResource as TVpropertytype
          0.0002999: leftJoined modTemplateVarResource as TVpropertylivingsurface
          0.0002940: leftJoined modTemplateVarResource as TVpropertylocation
          0.0005760: 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.0000231: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.header.image`
          0.0000112: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertytown`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertyprice`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertytype`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertylivingsurface`
          0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.propertylocation`
          0.0000679: Replaced TV conditions
          0.0000591: Processed additional conditions
          0.0005498: Added where condition: TVpropertyprice.value:>=250000, modResource.parent:IN(7), modResource.published=1, modResource.deleted=0, modResource.isfolder=0
          0.0000510: Replaced TV conditions
          0.0002081: Sorted by modResource.publishedon, DESC
          0.0000050: Limited to 4, offset 0
          0.0006049: 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(`TVheader.image`.`value`, '') AS `tv.header.image`, IFNULL(`TVpropertytown`.`value`, '') AS `tv.propertytown`, IFNULL(`TVpropertyprice`.`value`, '') AS `tv.propertyprice`, IFNULL(`TVpropertytype`.`value`, '') AS `tv.propertytype`, IFNULL(`TVpropertylivingsurface`.`value`, '') AS `tv.propertylivingsurface`, IFNULL(`TVpropertylocation`.`value`, '') AS `tv.propertylocation` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVheader.image` ON `TVheader.image`.`contentid` = `modResource`.`id` AND `TVheader.image`.`tmplvarid` = 10 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertytown` ON `TVpropertytown`.`contentid` = `modResource`.`id` AND `TVpropertytown`.`tmplvarid` = 28 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertyprice` ON `TVpropertyprice`.`contentid` = `modResource`.`id` AND `TVpropertyprice`.`tmplvarid` = 30 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertytype` ON `TVpropertytype`.`contentid` = `modResource`.`id` AND `TVpropertytype`.`tmplvarid` = 31 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertylivingsurface` ON `TVpropertylivingsurface`.`contentid` = `modResource`.`id` AND `TVpropertylivingsurface`.`tmplvarid` = 32 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVpropertylocation` ON `TVpropertylocation`.`contentid` = `modResource`.`id` AND `TVpropertylocation`.`tmplvarid` = 38 WHERE  ( `TVpropertyprice`.`value` > '250000' AND `modResource`.`parent` IN (7) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 AND `modResource`.`isfolder` = 0 )  ORDER BY modResource.publishedon DESC LIMIT 4 "
          0.0017970: SQL executed
          0.0001659: Total rows: 3
          0.0000591: Rows fetched
          0.0093570: Prepared and processed TVs
          0.0019071: Loaded "modChunk" with name "property.overview.row"
          0.0155611: Returning processed chunks
          0.0323360: Total time
          6 815 744: Memory usage
          
          • your productprice - TV isn't a number - TV-type, is it?
              -------------------------------

              you can buy me a beer, if you like MIGX

              http://webcmsolutions.de/migx.html

              Thanks!
            • It is..
              • How many products does actually meet the filter >= 250000 ? Should it be more than 3 results?
                  Codeplaza Webdesign: for professional websites at low cost
                • It looks like the query does not respect both WHERE conditions.

                  Try this one
                  &where=`{"TVproductprice.value>=":"250000","AND:TVproductprice.value<=":"500000"}`
                    Codeplaza Webdesign: for professional websites at low cost
                  • Currently i have 4 products/properties.

                    Prices:

                    • 999999
                    • 670000
                    • 510000
                    • 1000000

                    What i think happens, it compares it as string not as integers. I have the same results when using this snippet.

                    $qry = $modx->newQuery('modTemplateVarResource');
                        $qry->where(array('tmplvarid' => 30, 'AND:value:>'=>250000));
                        $result = $modx->getCollection('modTemplateVarResource', $qry);
                    


                    Result:
                    Array
                    (
                        [id] => 67
                        [tmplvarid] => 30
                        [contentid] => 8
                        [value] => 999999
                    )
                    Array
                    (
                        [id] => 78
                        [tmplvarid] => 30
                        [contentid] => 26
                        [value] => 670000
                    )
                    Array
                    (
                        [id] => 89
                        [tmplvarid] => 30
                        [contentid] => 38
                        [value] => 510000
                    )
                    
                    • ah, pdoTools seems only to cast to number for sorting, not for filtering TVs
                        -------------------------------

                        you can buy me a beer, if you like MIGX

                        http://webcmsolutions.de/migx.html

                        Thanks!