$query = "SELECT stv.name,stc.tmplvarid,stc.contentid,stv.type,stv.display,stv.display_params,stc.value"; $query .= " FROM ".$tb1." stc LEFT JOIN ".$tb2." stv ON stv.id=stc.tmplvarid "; $query .= " LEFT JOIN ".$tb_content." tb_content ON stc.contentid=tb_content.id "; $query .= " WHERE stv.name='".$tvTags."' AND stc.contentid IN (".implode($cIDs,",").") "; $query .= " AND tb_content.pub_date >= '".$pub_date."' "; $query .= " AND tb_content.published = 1 "; $query .= " ORDER BY stc.contentid ASC;";
$t = $this->modx->newQuery('modTemplateVar'); $t->leftJoin('modTemplateVarResource','tvValues'); $t->leftJoin('modResource','content'); $t->where(array( 'modTemplateVar.name' => $tvTags, 'tvValues.contentid:IN' => '('.implode($cIDs,",").')', 'content.pub_date:>=' => $pub_date, 'content.published' => '1', )); $t->sortby('tvValues.contentid','ASC'); $tags = $this->modx->getCollection('modTemplateVar',$t);
"tvValues.contentid IN (" . implode($cIDs,",") . ")"
The IN expression is tricky because of quoting in PDO prepared statements. The easiest way to work around this for now is to make it a literal expression rather than a prepared statement expression:
"tvValues.contentid IN (" . implode($cIDs,",") . ")"
I’ll work on a solution to make IN work as expected, but it may take some time and thought...
<?php $t = $this->modx->newQuery('modTemplateVar'); $t->leftJoin('modTemplateVarResource','tvValues'); $t->leftJoin('modResource','content'); $t->where(array( 'modTemplateVar.name' => $tvTags, "tvValues.contentid IN (" . implode($cIDs,",") . ")", 'content.pub_date:>=' => $pub_date, 'content.published' => '1', )); $t->sortby('tvValues.contentid','ASC'); $tags = $this->modx->getCollection('modTemplateVar',$t); ?>
$query->prepare(); $sql = $query->toSQL();
<?php $t = $this->modx->newQuery('modTemplateVar'); $t->select('modTemplateVar.name AS name, tvValues.tmplvarid AS varid'); $t->leftJoin('modTemplateVarResource','tvValues','modTemplateVar.id = tvValues.tmplvarid'); $t->leftJoin('modResource','content','tvValues.contentid = content.id'); $t->where(array( 'modTemplateVar.name' => $tvTags, "tvValues.contentid IN (" . implode($cIDs,",") . ")", 'content.pub_date:>=' => $pub_date, 'content.published' => '1', )); $t->sortby('tvValues.contentid','ASC'); $t->prepare(); $sql = $t->toSQL(); ?>
$t->select(array('name' => 'modTemplateVar.name', 'varid' => 'tvValues.tmplvarid'));