Please help to convert SQL to xPDO query

75 Posts

sladdals Reply #1, 13 years ago

Hello, just starting to learn modx queries via xPDO .

I have the following query which works fine in sql:

select filter,id
from `table1` 
where id = 475
    or id = ( select max(id) from `table1` where id < 475)
    or id = ( select min(id) from `table1` where id > 475)

I need to convert it to modx, I realize that i should use something like this:

$Q = $modx->newQuery('table1');
        $Q->select(array('id','resource_id','alias'));
        $Q->where(array(
            
            ));
        $Q->prepare();
        $Q->stmt->execute();
        $out=$Q->stmt->fetchAll(PDO::FETCH_ASSOC);

But just can't get how to write where conditions. Can someone please help me with this?

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 13 years ago

$Q->where(array('field'=>'value'));

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

75 Posts

sladdals Reply #3, 13 years ago

Quote from: sottwell at Sep 25, 2013, 12:30 PM

$Q->where(array('field'=>'value'));

sounds great))

but the problem is to convert string containing OR and select clauses to the query you mentioned))

where id = 475 or id = ( select max(id) from `table1` where id < 475) or id = ( select min(id) from `table1` where id > 475)

5,888 Posts

Bruno17 Reply #4, 13 years ago

to get the same result, you can try something like that:

<?php

$classname = 'table1';
$id = 475;
$out = array();

//prev object
$c= $modx->newQuery($classname);
$c->where(array('id:<'=>$id));
$c->select(array('id','resource_id','alias'));
$c->sortby('id',DESC);
$c->limit('1');
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);    
}

//current object
$c= $modx->newQuery($classname);
$c->where(array('id'=>$id));
$c->select(array('id','resource_id','alias'));
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);
}

//next object
$c= $modx->newQuery($classname);
$c->where(array('id:>'=>$id));
$c->select(array('id','resource_id','alias'));
$c->sortby('id',ASC);
$c->limit('1');
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);    
}

echo '<pre>' . print_r($out,1) . '</pre>';

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

75 Posts

sladdals Reply #5, 13 years ago

Quote from: Bruno17 at Sep 25, 2013, 02:19 PM

to get the same result, you can try something like that:

<!--?php

$classname = 'table1';
$id = 475;
$out = array();

//prev object
$c= $modx--->newQuery($classname);
$c->where(array('id:<'=>$id));
$c->select(array('id','resource_id','alias'));
$c->sortby('id',DESC);
$c->limit('1');
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);    
}

//current object
$c= $modx->newQuery($classname);
$c->where(array('id'=>$id));
$c->select(array('id','resource_id','alias'));
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);
}

//next object
$c= $modx->newQuery($classname);
$c->where(array('id:>'=>$id));
$c->select(array('id','resource_id','alias'));
$c->sortby('id',ASC);
$c->limit('1');
if ($object = $modx->getObject($classname,$c)){
    $out[] = $object->toArray('',false,true);    
}

echo '<pre>' . print_r($out,1) . '</pre>';

Thanks, Bruno17!

I also found the following code works fine:

$id = 475;
$out = array();
$sql= "SELECT alias, id, resource_id
FROM 'table1'
WHERE id = ".$id."
OR id = ( SELECT MAX(id) FROM 'table1' WHERE id < ".$id." )
OR id = ( SELECT MIN(id) FROM 'table1' WHERE id > ".$id." )";
$stmt = $modx->query($sql);
$out = $stmt->fetchAll(PDO::FETCH_ASSOC)

So what is the most convenient and effective way to this?

☆ A M B ☆
298 Posts

ojchris Reply #6, 13 years ago

Pretty interesting seeing this. Learning to use xPDO

75 Posts

sladdals Reply #7, 13 years ago

Finally I ended up with the snippet making next/prev urls for the documents stored in migxdb (Thanks to the Bruno17 for the great component).

<?php
/*[[!navigation?&className=`ShopContent`&packageName=`shop`&prevClass=`nextLeft`&nextClass=`nextRight`&delimiter=`|`&urlSuffix=`.html`]]
 * prevClass /nextClass - css class for next/prev links 
 * delimiter - if need to put something between next/prev links 
 * urlSuffix - if need to generate url output with special suffix.html * 
 */
  
$config =  array(
           'urlSuffix'=>'.html',
            'packageName' => 'shop',
            'className' => 'ShopContent',
            'delimiter' => '|',
            'prevClass' => 'nextLeft',
            'nextClass' => 'nextRight'
        );
$config=array_merge($config,$scriptProperties);
$config ['parent_field'] = empty($config['className']) || $config['className']=='modResource' ? "parent" : "resource_id";

$out='';
$resource_id = $modx->resource->get($config['parent_field']);
$id = $modx->resource->get('id');

if($config['className'] != 'modResource'){
    $modelpath = $modx->getOption('core_path') . 'components/' . $config['packageName'] . '/model/';
    $added = $modx->addPackage($config['packageName'], $modelpath);
    }

$query = $modx->newQuery($config['className']);
$query->select(array('id',$config['parent_field'],'alias','pagetitle'));
$query->where(array('resource_id' =>$resource_id));
$query->where(array(
    array(
    'id= ( SELECT MAX(id) FROM '.$modx->getTableName($config['className']).' WHERE id < '.$id.')',
    'id= ( SELECT MIN(id) FROM '.$modx->getTableName($config['className']).' WHERE id > '.$id.')',
    'id= ( SELECT MAX(id) FROM '.$modx->getTableName($config['className']).' WHERE '.$config['parent_field'].' = '.$resource_id.')',
    'id= ( SELECT MIN(id) FROM '.$modx->getTableName($config['className']).' WHERE '.$config['parent_field'].' = '.$resource_id.')'
     )
 ),xPDOQuery::SQL_OR);
$query->prepare();
//$modx->log(modX::LOG_LEVEL_ERROR, $query->toSql());
$query->stmt->execute();
$dbout=$query->stmt->fetchAll(PDO::FETCH_ASSOC);

if($dbout){
    $prevId=0;
    $nextId=0;
    $maxId=0;
    $minId=0;
	$links=array();
    foreach($dbout as $r){
         if($r['id']> $id){
              if($nextId==0 || $nextId>$r['id']){$nextId =$r['id'];} 
              if($maxId==0 || $maxId<$r['id']){$maxId =$r['id'];} 
          }
          elseif ($r['id']< $id){
              if($prevId==0 || $prevId<$r['id']) {$prevId=$r['id'];}
              if($minId==0 || $minId>$r['id']) {$minId=$r['id'];}
          }
          if (!isset($resurces[$r['id']])) $resurces[$r['id']]=array();
            $resurces[$r['id']]['resource_id'] =  $r['resource_id'];
            $resurces[$r['id']]['alias'] =  $r['alias'];
            $resurces[$r['id']]['pagetitle'] =  $r['pagetitle'];           
       }        
    if($prevId == 0 && $maxId!=$nextId) $prevId=$maxId;   
    if($nextId == 0 && $minId!=$prevId) $nextId=$minId;
    if($prevId!=0)  {
        $links[0]='<a href="'.$modx->makeUrl($resurces[$prevId]['resource_id']).$resurces[$prevId]['alias'].$config['urlSuffix'].'"
            class="'.$config['prevClass'].'" title="'.$resurces[$prevId]['pagetitle'].'">назад</a>';
    }   
   if($nextId!=0)  {
        $links[1]='<a href="'.$modx->makeUrl($resurces[$nextId]['resource_id']).$resurces[$nextId]['alias'].$config['urlSuffix'].'"
            class="'.$config['nextClass'].'" title="'.$resurces[$nextId]['pagetitle'].'">вперед</a>';
    }              
       $out=implode($config['delimiter'],$links);
  }

return $out;

Have not mach testing, but if it was written by newbie like me hope it would be pretty simple for everyone)) [ed. note: sladdals last edited this post 13 years ago.]