I can’t for the life of me figure out what is going on here, it seems that the PDO where clause is rounding down numbers!!???
I have this:
$gasql = 'SELECT * FROM modx_inventory_gage_long WHERE id = '.$ga.' ;';
$gas = $this->modx->query($gasql);
while($ga = $gas->fetch(PDO::FETCH_ASSOC)){
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga = ' . print_r($ga, TRUE));
$min = $ga['min'];
$max = $ga['max'];
}
$arrayWhere[] = array('ga:>=' => $min);
$arrayWhere[] = array('ga:<=' => $max);
it logs this:
(
[id] => 73
[ga] => 0
[min] => 2.250
[max] => 2.490
[display] => 2.250
)
Which is correct… a little later on:
$criteria->where($arrayWhere);
$criteria->limit($limit, $offset);
$criteria->leftJoin('InventoryReserve','InventoryReserve');
$criteria->prepare();
$this->modx->log(modX::LOG_LEVEL_ERROR, 'search SQL = ' . $criteria->toSQL());
$tags = $this->modx->getCollection('InventoryData', $criteria);
Pretty normal, right? Well this is what I get in the logs:
SELECT <<a bunch of stuff>> FROM `modx_gssi_inventory_data` AS `InventoryData`
LEFT JOIN `modx_gssi_inventory_reserve` `InventoryReserve` ON `InventoryData`.`id` = `InventoryReserve`.`tag_id`
WHERE
( `InventoryData`.`id` >= 1
AND ( `InventoryData`.`product_category` IN ('STAINLESS') AND `InventoryData`.`material_type` IN ('BAR','PIPE','TUBE','SEAMLESS-TUBE','WIRE') )
AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2
)
ORDER BY gage ASC, width ASC LIMIT 33
!!! notice the inventorydata.ga both have the same value?
am I just staring right at this or is something done wrong?
Seems to have been an issue before:
https://forums.modx.com/thread/85069/xpdo-doesn-t-take-floats
modxcms:master
← Fi1osof:patch-1
opened 02:21AM - 28 Nov 13 UTC
Maybe a wrong dbtype
/ phptype
combination in your schema.
I tried some combinations but couldn’t reproduce your problem.
I know bizarre right?? The schema looks fine and is nearly identical to another table that does not do this:
<object class="InventoryGageLong" table="inventory_gage_long" extends="xPDOSimpleObject">
<field key="ga" dbtype="int" precision="4" phptype="integer" null="false" />
<field key="min" dbtype="decimal" precision="4,3" phptype="float" null="true" />
<field key="max" dbtype="decimal" precision="4,3" phptype="float" null="true" />
<field key="display" dbtype="decimal" precision="4,3" phptype="float" null="true" />
</object>
As for the database - it looks fine as well, I believe I actually just copied the other (working) table and added the one extra field.
CREATE TABLE `modx_gssi_inventory_gage_long` (
`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`ga` INT(4) NOT NULL,
`min` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`max` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`display` DECIMAL(4,3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=90
;
Quite mystified at this point!
If I dump my criteria array just before I prepare it and getCollection the ga values are correct.
arrayWhere = Array
(
[0] => Array
(
[id:>=] => 1
)
[1] => Array
(
[product_category:IN] => Array
(
[0] => STAINLESS
)
)
[2] => Array
(
[ga:>=] => 0.017
)
[3] => Array
(
[ga:<=] => 0.021
)
)
try to use phptype string
It was worth a try - but no dice
$gage = $this->modx->getObject('InventoryGageLong',$ga);
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));
settype($gage->get('min'),'double');
settype($gage->get('max'),'double');
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));
$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));
$arrayWhere[] = array('ga:>=' => $gage->get('min'));
$arrayWhere[] = array('ga:<=' => $gage->get('max'));
/model/liveinventory.class.php : 299) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 301) ga/type/max-val = 54 double 2.49
/model/liveinventory.class.php : 307) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 309) ga/type/max-val = 54 double 2.49
`AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2 )
I could reproduce the behavior on my webserver.
It seems the binding
in the xPDOQueryCondition
gets assigned the wrong type. PDO::PARAM_INT
instead of PDO::PARAM_STR
.
The query works if I add 'float'
to this array .
protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time', 'json', 'array', 'float');
But it is probably not a good idea to change the MODX core code.
This post solved my problem, though as I understand it is a PDO issue and not modx, it will get fixed when PDO gets updated. from the sounds of it PDO will be a dependency for modx 3 and installed via composer…
opened 12:42AM - 24 Oct 17 UTC
closed 10:47AM - 24 Oct 17 UTC
Summary
Using floats in a 'andCondition' call would transform them as integer
Step to reproduce
create an XPDO query calling andCondtion as this following...
halftrainedharry:
I could reproduce the behavior on my webserver.
It seems the binding
in the xPDOQueryCondition
gets assigned the wrong type. PDO::PARAM_INT
instead of PDO::PARAM_STR
.
The query works if I add 'float'
to this array .
Copy to clipboard
protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time', 'json', 'array', 'float');
But it is probably not a good idea to change the MODX core code.
This IS a change that needs to be made to the xPDO core code. See recent discussion at Round decimal by Fi1osof · Pull Request #25 · modxcms/xpdo · GitHub for more information. It seems this issue has sat unresolved and unintentionally ignored for some time.
1 Like
By the way, setting the phptype to string in the xpdo schema can temporarily work around the problem, but may prevent the use of other float-specific features in xpdo for that field.
1 Like