We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 28743
    • 10 Posts
    Here goes for another newbie xPDO question. I’m trying to replicate this sql statement:

    SELECT `id`, `contentid`, value FROM `wood_site_tmplvar_contentvalues` WHERE (`tmplvarid` = 18 AND CAST(value AS  DECIMAL(5,2)) >= 7.5 )


    So that I can filter resources based on a decimal value stored in a TV. How would I go about implementing this with xPDO?
      • 33968
      • 863 Posts
      How about this (not tested):
      <?php
      $c = $modx->newQuery('modTemplateVarResource');
      $c->select('id, contentid, value, CAST(value AS DECIMAL(5,2)) AS cvalue');
      $c->where(array(
          'tmplvarid' => 18,
          'cvalue:>=' => 7.5,
          ));
      $collection = $modx->getIterator('modTemplateVarResource',$c);
      foreach ($collection as $object) {
          $output .= 'ContentId: '.$object->get('contentid').' Value: '.$object->get('value') . '<br>';
      }
      return $output;
      


      Can you let me know if this works too:
      <?php
      $c = $modx->newQuery('modTemplateVarResource');
      $c->select('id, contentid, value');
      $c->where(array(
          'tmplvarid' => 18,
          'CAST(value AS DECIMAL(5,2)):>=' => 7.5,
          ));
      $collection = $modx->getIterator('modTemplateVarResource',$c);
      foreach ($collection as $object) {
          $output .= 'ContentId: '.$object->get('contentid').' Value: '.$object->get('value') . '<br>';
      }
      return $output;
      
        • 28743
        • 10 Posts
        Thanks for the suggestions Lucas. Unfortunately, neither work as they end up producing invalid sql.

        The first creates:

        SELECT `id`, `contentid`, `value`, CAST(value AS DECIMAL(5, 2)) AS cvalue FROM `wood_site_tmplvar_contentvalues` AS `modTemplateVarResource` WHERE ( `modTemplateVarResource`.`tmplvarid` = 18 AND `modTemplateVarResource`.`cvalue` >= '7.5' )


        and the second:

        SELECT `id`, `contentid`, `value` FROM `wood_site_tmplvar_contentvalues` AS `modTemplateVarResource` WHERE ( `modTemplateVarResource`.`tmplvarid` = 18 AND `modTemplateVarResource`.`CAST(value AS DECIMAL(5,2))` >= '7.5' )
          • 4172
          • 5,888 Posts
          you can try something like this:

          $c->where("CAST(value AS  DECIMAL(5,2)) >= 7.5", xPDOQuery::SQL_AND);		
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
            • 33968
            • 863 Posts
            Bruno, you’re always miles ahead tongue

            Just wondering - could you perhaps convert the 7.5 to a string and filter against the raw TV value, avoiding CAST altogether?
              • 28743
              • 10 Posts
              Thanks Bruno - that did the trick!

              Lucas - converting to a string wouldn’t cut it - a string comparison of 2 and 100 would see 2 as the greater value.