On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • I wonder if you can use an alias in a "where" statement.

    For instance:
    $query = $modx->newQuery('request');
    $query->select(array(
        'id',
        'lastupdate' ,
        'DATEDIFF(NOW(),lastupdate) AS Age'
    ));
    $query->where(array(
        'Age:>=' => 7
    ));
    


    I tried this as well:
    $query->where(array(
        'DATEDIFF(NOW(),lastupdate):>=' => 7
    ));
    


    In both causes I get "an unknown column" error.

    I hope someone can help me. Thanks in advance.

    This question has been answered by Bruno17. See the first response.

    • How about something like this:

      $cutoff = strtotime(-1 week); /* 7 days ago */
      
      $query->where(array(
           (string) $cutoff . ':>=' => 'lastupdate'
      ));
      
        Did I help you? Buy me a beer
        Get my Book: MODX:The Official Guide
        MODX info for everyone: http://bobsguides.com/modx.html
        My MODX Extras
        Bob's Guides is now hosted at A2 MODX Hosting
      • Quote from: BobRay at Jan 05, 2016, 08:21 PM
        How about something like this:

        $cutoff = strtotime(-1 week); /* 7 days ago */
        
        $query->where(array(
             (string) $cutoff . ':>=' => 'lastupdate'
        ));
        

        The problem is that de resulting query looks like this:
        SELECT `id`, `lastupdate`, `state`, `file`, DATEDIFF(NOW(),lastupdate) AS Age, OCTET_LENGTH(file) AS FileSize FROM `modx_my_requests` AS `myRequest` WHERE ( `myRequest`.`state` = 6 AND `myRequest`.`1451466840` >= 'lastupdate' ) ORDER BY lastupdate ASC

        The part "`myRequest`.`1451466840`" results in an error.
        • Try removing this line:

          'DATEDIFF(NOW(),lastupdate) AS Age'
            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting
          • Quote from: BobRay at Jan 06, 2016, 06:29 PM
            Try removing this line:

            'DATEDIFF(NOW(),lastupdate) AS Age'

            I think that will work but then I loose the column Age in the query output.

            Is this what you mean?

            $query = $modx->newQuery('request');
            $query->select(array(
                'id',
                'lastupdate'
            ));
            $query->where(array(
                'DATEDIFF(NOW(),lastupdate):>=' => 7
            ));
            


            I need the column "Age" to process the output. I have written a snippet:

            $requests = $modx->getCollection('request',$query);
            foreach ($requests as $r)
            {
              	$request=$r->toArray();
            
              	if ($request[Age]>7)
                    ......
            


            This is Ok for the moment. The collection contains too much elements, but that's all.
            I was wondering if there was a more efficient way to get the records, but it seems to be a problem using an alias.
            • discuss.answer
              try

              $query = $modx->newQuery('request');
              $query->select(array(
                  'id',
                  'lastupdate' ,
                  'DATEDIFF(NOW(),lastupdate) AS Age'
              ));
              
              $query->having('Age >= 7');



              or

              $query = $modx->newQuery('request');
              $query->select(array(
                  'id',
                  'lastupdate' ,
                  'DATEDIFF(NOW(),lastupdate) AS Age'
              ));
              
              $query->where('DATEDIFF(NOW(),lastupdate)  >= 7');
              

                -------------------------------

                you can buy me a beer, if you like MIGX

                http://webcmsolutions.de/migx.html

                Thanks!