Where queries on number TVs

12 Posts

robotjoosen Reply #1, 7 years, 9 months ago

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.

12 Posts

robotjoosen Reply #2, 7 years, 9 months ago

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?

194 Posts

DonQuicky Reply #3, 7 years, 9 months ago

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

12 Posts

robotjoosen Reply #4, 7 years, 9 months ago

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

5,888 Posts

Bruno17 Reply #5, 7 years, 9 months ago

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!

12 Posts

robotjoosen Reply #6, 7 years, 9 months ago

It is..

194 Posts

DonQuicky Reply #7, 7 years, 9 months ago

How many products does actually meet the filter >= 250000 ? Should it be more than 3 results?

Codeplaza Webdesign: for professional websites at low cost

194 Posts

DonQuicky Reply #8, 7 years, 9 months ago

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

12 Posts

robotjoosen Reply #9, 7 years, 9 months ago

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
)

5,888 Posts

Bruno17 Reply #10, 7 years, 9 months ago

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!