We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 6059
    • 45 Posts
    Hy, i try to make a mysql query like

    SELECT * FROM modx_site_tmplvar_contentvalues where tmplvarid = '8' and (value = '6' or value = '12')


    but after run this in xpdo

    $c->where(array(
        'tmplvarid' => '8'
    ));
          
    $c->andCondition(array(
        'value' => '6'
    ), null, 1);
          
    $c->orCondition(array(
        'value' => '9'
    ), null, 1);


    i get wrong result. Think i make the query wrong but i am new to xpdo and need your help. How to make a xpdo query like the mysql query above and how should it look for a query like this

    SELECT * FROM mosega.modx_site_tmplvar_contentvalues where tmplvarid = '8' and (value = '18' or value = '12') or tmplvarid = '9' and (value like '%Action%' or value like '%komödie%') or tmplvarid = '58' and (value = 'P') order by contentid asc


    I have the values in a array and need to run a loop to insert it into the query. Tried it with this code

    if($_GET['u'] != ''){
        //USK Conditions
        
        $c = $modx->newQuery('modTemplateVarResource');
        $c->select('*'); 
        
        if(count($uskValues) == 1){
        
          $c->where(array(
              'tmplvarid' => '8',
              'value' => $uskValues[0]
            ));
          
        }else{
          
          $c->where(array(
              'tmplvarid' => '8'
            ));
          
          $c->andCondition(array(
              'value' => $uskValues[0]
            ), null, 1);
          
          unset($uskValues[0]);
          
          foreach($uskValues as $usk){
            $c->orCondition(array(
              'value' => $usk
            ), null, 1);
          }
        }
        $elements = $modx->getCollection('modTemplateVarResource',$c);
        $uskids = array();  
        foreach($elements as $element){    
          $uskids[] = $element->get('contentid');
        }
      }
      • 21257 MODX Staff
      • 730 Posts
      The where condition can be specified as a nested array to easily achieve grouping.
      Try the following:
      $c = $modx->newQuery('modTemplateVarResource');
      $c->select('*');
      $c->where(array(
        'tmplvarid' => '8',
        array(
          'value' => '6',
          'OR:value' => '12
        )
      ));
      
        Mike Schell
        Lead Developer, MODX Cloud
        Email: [email protected]
        GitHub: https://github.com/netProphET/
        Twitter: @mkschell
        • 28215
        • 4,149 Posts
        Worth it to note that:

        $c->select('*');
        


        should be:

        $c->select($modx->getSelectColumns('modTemplateVarResource','modTemplateVarResource'));
        


        (It’s better for sql abstraction for multiple drivers.)
          shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
          • 22303 MODX Staff
          • 10,725 Posts
          Much easier to use the nested array format, though in this case you need to use IN, not OR, especially since you can’t have two conditions in the same "group" or array-level with the same key:
          <?php
          $c->where(array(
              'tmplvarid' => '8',
              'value:IN' => array('6','12')
          ));


          If you had a valid OR clause that is not better replaced with IN, just use nested array format:
          <?php
          $c->where(array(
              'tmplvarid' => '8',
              array(
                  'value' => '6',
                  'OR:value:=' => '12'
              )
          ));

          However, you would not be able to use another instance of ’OR:value:=’ in that specific array level, since that key of the associative array is already used.
            • 6059
            • 45 Posts
            Ok this are good tips but i can have the where values in a array like array(6,12) or much more values thats because i need no make a loop. The value field of the tv can have multiple values of number like 6, 12, 18 or 12, 0 or 6, 18 so where with :IN will not work.

            I get the values in a array now and need to make the loop and insert it in the query any idea?
              • 21257 MODX Staff
              • 730 Posts
              You can pass your array right into the query.

              <?php
              $c->where(array(
                  'tmplvarid' => '8',
                  'value:IN' => $uskValues
              ));
              


              Note: If you expect the array to have a lot of values (100, 500, 1000, 5000, it’s hard to know) then an IN clause will not be the most efficient query. http://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause
                Mike Schell
                Lead Developer, MODX Cloud
                Email: [email protected]
                GitHub: https://github.com/netProphET/
                Twitter: @mkschell
                • 6059
                • 45 Posts
                That twill not run i think because i have in my tv something like

                tmplvarid value
                8 6,12,18
                8 12,6
                8 6,18

                and if i make it with value:IN it would be something like where 6,12,18:IN => 12 and that will not give me any result
                  • 21257 MODX Staff
                  • 730 Posts
                  Based on your earlier code I thought that $uskValues was an array, sorry for the confusion.
                  If $uskValues is a comma-separated list of the values that you want to check, then try:
                  <?php
                  $c->where(array(
                      'tmplvarid' => '8',
                      'value:IN' => explode($uskValues)
                  ));
                  

                    Mike Schell
                    Lead Developer, MODX Cloud
                    Email: [email protected]
                    GitHub: https://github.com/netProphET/
                    Twitter: @mkschell
                    • 6059
                    • 45 Posts
                    Sorry i think i explained it wrong. I have an array uskValues but the text in the values is a comma seperatet list

                    in values field i have something like 12,6 or 6 or 12,18 or 6,18,12 or something other numbers and an array like array(’6’, ’12’) thats because values in will not run i think because i would check is ’12,6’ in 12 answer NO is 12,6 in 6 answer NO.

                    Hope you know now what i mean smiley