We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 33968
    • 863 Posts
    I’m trying to port the following simple query over to xPDO, but just cannot work out how to do it using the methods available.
    <?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
    ");
    

    The query is supposed to return the cheapest ’item’ and ’box’ combination, with the criteria that the item must fit inside the box.

    This is what I have so far (inspired by this forum topic):
    <?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;
    

    This always returns ’query failed’. Tried many different methods and combinations but so far to no avail.

    I think that $modx->db->query has been deprecated with Revo 2.1 which means I now really need to figure this out.

    Any help would really be appreciated!
      • 21257 MODX Staff
      • 730 Posts
      If this is for an Extra you’re developing, you would also want to make sure that the query is going to work in Microsoft SQL server.
      Could you share a sql dump of those two tables, with at least some test data? I’m not 100% sure that the MIN function is going to work, or work the same, on both.
      This would be a good candidate for putting in an "example queries" section in the docs, once it’s solved.
        Mike Schell
        Lead Developer, MODX Cloud
        Email: [email protected]
        GitHub: https://github.com/netProphET/
        Twitter: @mkschell
        • 33968
        • 863 Posts
        It’s not for an extra - although it could be at some stage wink - 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.

        Unfortunately the data I have belongs to a client and I can’t share it, which is why I’ve changed the table names above in line with the ’Box’ examples from the docs. The tables would be fairly simple to set up though if you wanted to test on MS SQL. If I get a chance later I will try to send you a couple of sample tables though!
          • 4172
          • 5,888 Posts
          didn’t you create schema and classes for that table?
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
            • 33968
            • 863 Posts
            Yes, created those and all working fine for getObject, getCollection, newQuery, etc.
            No problem there, but I can’t get this particular query working in xPDO. The tables are not related.
              • 4172
              • 5,888 Posts
              may be something like that can do it:

              <?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());
              
                -------------------------------

                you can buy me a beer, if you like MIGX

                http://webcmsolutions.de/migx.html

                Thanks!
                • 33968
                • 863 Posts
                Hi Bruno, that seems to have worked laugh

                This one returns a ’Box’ object, onto which is tacked all Item fields and also the ’sumPrice’ field. I can do a $object->get(’sumPrice’) to get the value I needed.

                But it doesn’t ’feel’ the same as the original query, which only spat out one field + value: sumPrice. I don’t know if it’s more or less efficient.

                I think I could use this query without any issues, but is there any way to return just the result I needed instead of the whole object?
                Also, it seems to work fine without the two getSelectColumns lines. Are they necessary?

                Reduced to this which returns the correct value (not thoroughly tested though):
                <?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');
                
                  • 4172
                  • 5,888 Posts
                  Hi Lucas,
                  These selects aren’t necessary. You can use it without them, of course.
                  It was only to show, how you can get the other fields, if you need them.

                  You can also remove the line
                  $c->prepare();
                  This line is only needed, if you want to see the resulting sql for debugging with
                  echo $c->toSql();
                    -------------------------------

                    you can buy me a beer, if you like MIGX

                    http://webcmsolutions.de/migx.html

                    Thanks!
                    • 33968
                    • 863 Posts
                    Great, it seems quite simple actually! I actually started with something not too far from that but when I started reading up things started to get complicated.
                    Thanks a lot for your help smiley