Problem with query, tv and resource

1

I am creating a plugin in which I call two snippets to fetch the TV types of the resource.
They are practically identical but one works and the other doesn’t and I can’t figure out where I’m going wrong.
The first one takes all the text TVs and puts them in a select, it works fine, if there are none it puts none and away it goes.
And it is as follows, all the TVs in the template (of the resource) that are either typo text, areatext type or richtext type.

$qTV = $modx->newQuery('MODX\\Revolution\\modTemplateVarTemplate');
$qTV ->select($modx->getSelectColumns('MODX\\Revolution\\modTemplateVarTemplate','modTemplateVarTemplate',''));
$qTV ->select($modx->getSelectColumns('MODX\\Revolution\\modTemplateVar','modTemplateVar','', array('id','type','caption')));
$qTV ->leftJoin('MODX\\Revolution\\modTemplateVar','modTemplateVar', 'modTemplateVar.id = modTemplateVarTemplate.tmplvarid');
$qTV ->where(array(
				'modTemplateVar.type' => 'text'
			));
$qTV ->where(array(
				'OR:modTemplateVar.type:=' => 'textarea'
			));
$qTV ->where(array(
				'OR:modTemplateVar.type:=' => 'richtext'
			));
$qTV ->where(array(
				'modTemplateVarTemplate.templateid' => $resource->get('template')
			));
$iTotTV = $modx->getCount('MODX\\Revolution\\modTemplateVarTemplate',$qTV);
if($iTotTV) {
	$oTV = $modx->getIterator('MODX\\Revolution\\modTemplateVarTemplate',$qTV);
	foreach($oTV as $TV) {
		$selCampo .= '<option value="tv'.$TV->get('id').','.$TV->get('type').'">TV: '.$TV->get('caption').'</option>';
	}
}

The second should take all TVs in the template whether they are either image or imageplus or galleryitem and is as follows

$qTV = $modx->newQuery('MODX\\Revolution\\modTemplateVarTemplate');
$qTV ->select($modx->getSelectColumns('MODX\\Revolution\\modTemplateVarTemplate','modTemplateVarTemplate',''));
$qTV ->select($modx->getSelectColumns('MODX\\Revolution\\modTemplateVar','modTemplateVar','', array('id','type','caption')));
$qTV ->leftJoin('MODX\\Revolution\\modTemplateVar','modTemplateVar', 'modTemplateVar.id = modTemplateVarTemplate.tmplvarid');
$qTV ->where(array(
				'modTemplateVar.type' => 'image'
			));
$qTV ->where(array(
				'OR:modTemplateVar.type:=' => 'imageplus'
			));
$qTV ->where(array(
				'OR:modTemplateVar.type:=' => 'galleryitem'
			));
$qTV ->where(array(
				'modTemplateVarTemplate.templateid' => $resource->get('template')
			));
$iTotTV = $modx->getCount('MODX\\Revolution\\modTemplateVarTemplate',$qTV);
if($iTotTV) {
	$divContSetTV = '<div class="divNasc contSetTV">';
	$oTV = $modx->getIterator('MODX\\Revolution\\modTemplateVarTemplate',$qTV);
	foreach($oTV as $TV) {
		$divContSetTV .= '<div class="singSetTV">'.$TV->get('caption').' <button type="button" onclick="Mabol.setImgTv('.$TV->get('id').',\''.$TV->get('type').'\')"><i class="icon fa-long-arrow-alt-right"></i></button></div>';
	}	
	$divContSetTV .= '</div>';
}

this does not work, it reports duplicate or present TVs when there are none linked to the template.
I basically copy-pasted and changed the types and the only other difference is that one fills the options of a select the second of a hidden div.
Did I get lucky and the first one works by chance or is there something in the second one that I am missing?
The two queries also seem right and identical to me.
$qTV ->prepare(); //Debug della query
echo $qTV ->toSQL(); //Debug della query

For tv “text”

SELECT `modTemplateVarTemplate`.`tmplvarid`, `modTemplateVarTemplate`.`templateid`, `modTemplateVarTemplate`.`rank`, `modTemplateVar`.`id`, `modTemplateVar`.`type`, `modTemplateVar`.`caption` FROM `test301_site_tmplvar_templates` AS `modTemplateVarTemplate` LEFT JOIN `test301_site_tmplvars` `modTemplateVar` ON modTemplateVar.id = modTemplateVarTemplate.tmplvarid WHERE  ( `modTemplateVar`.`type` = 'text' OR `modTemplateVar`.`type` = 'textarea' OR `modTemplateVar`.`type` = 'richtext' AND `modTemplateVarTemplate`.`templateid` = 5

fot tv “image”

SELECT `modTemplateVarTemplate`.`tmplvarid`, `modTemplateVarTemplate`.`templateid`, `modTemplateVarTemplate`.`rank`, `modTemplateVar`.`id`, `modTemplateVar`.`type`, `modTemplateVar`.`caption` FROM `test301_site_tmplvar_templates` AS `modTemplateVarTemplate` LEFT JOIN `test301_site_tmplvars` `modTemplateVar` ON modTemplateVar.id = modTemplateVarTemplate.tmplvarid WHERE  ( `modTemplateVar`.`type` = 'image' OR `modTemplateVar`.`type` = 'imageplus' OR `modTemplateVar`.`type` = 'galleryitem' AND `modTemplateVarTemplate`.`templateid` = 5
2

One problem is the mixture of AND and OR in the WHERE clause without the correct parenthesis:

The generated SQL query

WHERE  (
  type = 'text' 
  OR type = 'textarea' 
  OR type = 'richtext' 
  AND templateid = 5
)

is the same as

WHERE  (
  type = 'text'
  OR type = 'textarea'
  OR (type = 'richtext' AND templateid = 5)
)

But what you want is

WHERE  (
  (type = 'text' OR type = 'textarea' OR type = 'richtext')
  AND templateid = 5
)

You can achieve this by either using IN

$qTV->where(['modTemplateVar.type:IN' => ['text', 'textarea', 'richtext'] ]);
$qTV->where(['modTemplateVarTemplate.templateid' => $resource->get('template') ]);

or like this:

$qTV->where([
    ['modTemplateVar.type' => 'text'], ['OR:modTemplateVar.type:=' => 'textarea'], ['OR:modTemplateVar.type:=' => 'richtext']
]);
$qTV->where(['modTemplateVarTemplate.templateid' => $resource->get('template') ]);
3

Also, logically it makes more sense if you use modTemplateVar as the base for the query and then join it to modTemplateVarTemplate (and not the other way around):

use MODX\Revolution\modTemplateVar;
use MODX\Revolution\modTemplateVarTemplate;

$qTV = $modx->newQuery(modTemplateVar::class);
$qTV->leftJoin(modTemplateVarTemplate::class, 'TemplateVarTemplates');
$qTV->select($modx->getSelectColumns(modTemplateVar::class, 'modTemplateVar', '', ['id','type','caption']));
$qTV->where([
    ['modTemplateVar.type' => 'text'], ['OR:modTemplateVar.type:=' => 'textarea'], ['OR:modTemplateVar.type:=' => 'richtext']
]);
$qTV->where(['TemplateVarTemplates.templateid' => $resource->get('template') ]);

$iTotTV = $modx->getCount(modTemplateVar::class, $qTV);
if($iTotTV) {
	$oTV = $modx->getIterator(modTemplateVar::class, $qTV);
	...
}
4

Always the best, of course.
On the second answer, however, I disagree. :grinning:
In my mind I look for the TVs in the template and then do leftjoin to get more data.
Or is your advice an ‘imperative’ to make the query work better?

As always, thank you very much

5

All the data (“id”, “type”, “caption”) you want in the loop is from the modTemplateVar class. Therefore it doesn’t make much sense to create objects of type modTemplateVarTemplate in the loop.

modTemplateVarTemplate is only used for the filtering.

modTemplateVarTemplate is just a junction table to connect modTemplateVar to modTemplate with a many-to-many relationship.

6

So my mind became more open :grinning: