<?php
$minPrice = $modx->db->query( "
SELECT
MIN(item.price + box.price) AS sumPrice
FROM `item_table_name` item, `box_table_name` box
WHERE box.width > item.width
AND box.length > item.length
");
<?php
$item = $modx->getTableName('Item');
$box = $modx->getTableName('Box');
$iCols = $modx->getSelectColumns('Item', 'item');
$bCols = $modx->getSelectColumns('Box', 'box');
if ($stmt = $modx->prepare("
SELECT MIN({$iCols} + {$bCols}) AS sumPrice
FROM {$item} item, {$box} box
WHERE box.width > item.width
AND box.length > item.length" )) {
if ($stmt->execute()) {
$output = $stmt->fetch(PDO::FETCH_COLUMN);
$stmt->closeCursor();
}
else $output = 'query failed';
}
return $output;
- so I’ve only tested this on mySql. It works fine there and I’ve been using the same query on an Evo site, but it would be a great to have a solution that works for all.
<?php
$prefix = 'modx_box_';
$packageName = 'box';
$packagepath = $modx->getOption('core_path') . 'components/'.$packageName.'/';
$modelpath = $packagepath.'model/';
$modx->addPackage($packageName,$modelpath,$prefix);
$c=$modx->newQuery('Box');
$c->select($modx->getSelectColumns('Box', 'Box'));
$c->innerJoin('Item','Item',array('Box.width > Item.width','Box.length > Item.length'));
$c->select('MIN(Item.price + Box.price) AS sumPrice');
$c->select($modx->getSelectColumns('Item', 'Item','Item_'));
$c->prepare();echo $c->toSql();
$object=$modx->getObject('Box',$c);
print_r($object->toArray());

<?php
$c = $modx->newQuery('Box');
$c->innerJoin('Item','Item',array('Box.width > Item.width','Box.length > Item.length'));
$c->select('MIN(Item.price + Box.price) AS sumPrice');
$c->prepare();
$object = $modx->getObject('Box',$c);
$sumPrice = $object->get('sumPrice');
$c->prepare();
echo $c->toSql();