[SOLVED] Query Join table to sort by menuindex

Query Join table to sort by menuindex

Here is my query:

$results = $modx->query("SELECT contentid FROM modx_site_tmplvar_contentvalues WHERE tmplvarid=107");

I need to sort it by menuindex so i tried this:

$results = $modx->query("SELECT contentid FROM modx_site_tmplvar_contentvalues JOIN modResource WHERE tmplvarid=107 ORDER BY menuindex DESC");

I'm not familiar at all whith this. Of course it doesn't work. Someone told me to use XPDO but i know nothing about it. Do i miss something? What should i do to make it work ?

Here is the full code :

<?php
$results = $modx->query("SELECT contentid FROM modx_site_tmplvar_contentvalues WHERE tmplvarid=107");

// description tv: 108
// vignette tv: 121

if (!is_object($results)) {
  return;
}
$breves = array();


while($r = $results->fetch(PDO::FETCH_COLUMN, 0)){
  $breves[] = $r;
}

$queryIn = implode(',' , $breves);

$results_2 = $modx->query("SELECT contentid, tmplvarid, value FROM modx_site_tmplvar_contentvalues WHERE contentid IN ({$queryIn}) AND tmplvarid IN (108, 121)");

if (!is_object($results_2)) {
  return;
}
$tvValues = array();

while($v = $results_2->fetch(PDO::FETCH_ASSOC)){
  if($v['tmplvarid'] == 108){
    $tvValues[$v['contentid']]['desc'] = $v['value'];
  } else {
    $tvValues[$v['contentid']]['vignette'] = $v['value'];
  }
}

$output = "";

foreach ($breves as $res_id) {
  $page = $modx->getObject('modResource', $res_id);
  $alias = $page->get('alias');
  $id = $page->get('id');

  $page_title = $page->get('pagetitle');
  $description = $tvValues[$res_id]['desc'];
  if(!$description || $description == ""){
    $description = $page->get('content');
  }
  $description = strip_tags($description);
  $description = mb_strimwidth($description, 0, 150, "...");
  $vignette = $tvValues[$res_id]['vignette'];
  if($vignette){
    $vignette = "/assets/upload/pln/" . $vignette;
  } else {
    $vignette = "https://www.commune-ploudaniel.fr/assets/templates/pln/default-b7824fcd998f51baf0f0af359a72e760.png";
  }

$output .= <<<HTML
  <div class="xpro-slider-item">
    <div class="xp-news-classic-block">
        <div class="xp-hover-image">
          <a href="{$alias}.html" ><div class="xp-overlay"></div></a>
            <a class="xp-view-lightbox" href="{$alias}.html" ><i class="fa fa-plus"></i></a>
            <img src="{$vignette}" alt="{$alias}" style="width:375px; height:270px; object-fit: cover;"/>
    </div>
        <div class="xp-news-detail">
          <h4><a href="{$alias}.html" data-display="lightbox">{$id}{$page_title}</a></h4>
          <p>{$description}</p>
          <div class='xp-news-footer'></div>
        </div>
    </div>
  </div>
HTML;

$output .= PHP_EOL;
}
return $output;

Solution

Try this for MODX:

$q = $modx->newQuery('modTemplateVarResource');
$q->leftJoin('modResource', 'mr', 'mr.id = modTemplateVarResource.contentid');
$q->where(array(
    'modTemplateVarResource.tmplvarid' => 107
));
$q->select(array(
   'modTemplateVarResource.contentid'
));
$q->sortby('modResource.menuindex', 'DESC');
$q->prepare();
$q->stmt->execute();
$result = $q->stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);