xPDO compiles integer as string

Question

$query = $this->modx->newQuery('modResource');
$query->leftJoin('modTemplateVarResource', "price", array("modResource.id = price.contentid", "price.tmplvarid = 2"));
$query->where(array("price:>=" => 6000));
$query->prepare();
echo $query->toSql();

RETURNS:

`modResource`.`context_key` AS `modResource_context_key`, `modResource`.`content_type` AS `modResource_content_type`, `modResource`.`uri` AS `modResource_uri`, `modResource`.`uri_override` AS `modResource_uri_override`, `modResource`.`hide_children_in_tree` AS `modResource_hide_children_in_tree`, `modResource`.`show_in_tree` AS `modResource_show_in_tree`, `modResource`.`properties` AS `modResource_properties`
FROM `modx_site_content` AS `modResource`
LEFT JOIN `modx_site_tmplvar_contentvalues` `price` 
    ON ( modResource.id = price.contentid 
        AND price.tmplvarid = 2 ) 
WHERE `modResource`.`price` >= '6000'

Why is integer quoted? It should be treated as an int.

if you executing a query where you trying to find greater or lesser values by integer you wolud have wrong results if your value would been quoted — YanAlex, Nov 25 '13 at 08:56
Really? What particular wrong results did you get? (I hope you store `price` as int. Otherwise *this* is your problem) — Your Common Sense, Nov 25 '13 at 08:56
I cant store price as int because this is the TVField of MODx that stores as MEDIUMTEXT — YanAlex, Nov 25 '13 at 09:51
`TVField` in Modx has support for numbers? Try using that. Or you could try casting, although I am not sure if that makes any difference. Something like: `$query->where(array("price:>=" => (int) 6000));` — OptimusCrime, Nov 25 '13 at 22:48
ive tried (int) 6000 and tried intval(6000), same. The original getResources uses CASTING, but i cant understood how to use it because it works real correct only if im ommiting quotes, anotherway it includes trash data over 6000 — YanAlex, Nov 26 '13 at 05:38
my bad was because ive tried to use CAST on fields and not on params in where clause — YanAlex, Nov 26 '13 at 05:55

score 2 · Answer 1 · answered Nov 26 '13 at 03:00

This is probably not working because "price" is the table alias used on the join; you need to run your where condition against an actual field - price.value:

$query = $this->modx->newQuery('modResource');
$query->leftJoin('modTemplateVarResource', "price", array("modResource.id = price.contentid", "price.tmplvarid = 2"));
$query->where(array("price.value:>=" => 6000));
$query->prepare();
echo $query->toSql();

I'm almost 100% certain the where condition will work in this case, even as a string. However if you're still running into problems you might try casting "price.value" as an integer (not tested):

$query = $this->modx->newQuery('modResource');
$query->leftJoin('modTemplateVarResource', "price", array("modResource.id = price.contentid", "price.tmplvarid = 2"));
$query->where(array("CAST(price.value AS UNSIGNED INTEGER):>=" => 6000));
$query->prepare();
echo $query->toSql();

PS. float/decimal would usually be a more appropriate type for price, but that's up to you ;)

dam if i use function in where then XPDO enclose it in quotes xD. do you know the problem ? — YanAlex, Nov 26 '13 at 05:59
I think in that case (as you've suggested below), it should be added to the where condition as a string not an array. Then it will be added to the query exactly as written. Thanks for the beer :P — okyanet, Nov 27 '13 at 03:29

YanAlex · Accepted Answer · 2013-11-26T06:58:08.517

0

It seems i found another solution - the easiest way is

$query->where("price.value >= 6000");

and best way to combine with okyanet solution

$query->where("CAST(price.value AS UNSIGNED INTEGER) >= 6000");

All of them works correct in xPDO

edited Nov 26 '13 at 06:58

answered Nov 26 '13 at 06:14

YanAlex

99
1
10

score 0 · Answer 3 · answered Dec 27 '13 at 08:59

0

the right way for me is to use

$whereArray = array('CAST(:price.value:as unsigned)>=' => 6000);
//then put it to
$query->where($whereArray);

answered Dec 27 '13 at 08:59

Vitaly Bjorn

1

xPDO compiles integer as string

3 Answers3